A_Index fails in Excel COM, manual increment works

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Exaskryz
Posts: 2876
Joined: 17 Oct 2015, 20:28

A_Index fails in Excel COM, manual increment works

30 Nov 2018, 22:58

The code below works

Code: Select all

ex:=ComObjActive("Excel.Application")
Loop % 26*10+12 ; this is columns
	{
	c++
	Loop 1506 ; this is rows
		{
		r++
		MsgBox % r "," c
		formula:=ex.Cells(r, c).formula
		If StrLen(formula)>80
			ex.Cells(r, c).formula:="=IF(A5=""CP""," SubStr(formula,2) ",IF(A5=""Stats Sum"",(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+7 & ""C"" & COLUMN(),FALSE)+$B$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE)+(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+8 & ""C"" & COLUMN(),FALSE)+$C$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE)+(INDIRECT(""R"" & ROW() & ""C"" & COLUMN()-MOD(COLUMN(),17)+1,FALSE)+$D$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE),""N/A""))"
		}
	r:=0
	}
return
This code fails

Code: Select all

ex:=ComObjActive("Excel.Application")
Loop % 26*10+12 ; this is columns
	{
	c:=A_Index
	Loop 1506
		{
		r:=A_Index
		MsgBox % r "," c
		formula:=ex.Cells(r,c).formula
		If StrLen(formula)>80
			ex.Cells(r, c).formula:="=IF(A5=""CP""," SubStr(formula,2) ",IF(A5=""Stats Sum"",(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+7 & ""C"" & COLUMN(),FALSE)+$B$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE)+(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+8 & ""C"" & COLUMN(),FALSE)+$C$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE)+(INDIRECT(""R"" & ROW() & ""C"" & COLUMN()-MOD(COLUMN(),17)+1,FALSE)+$D$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE),""N/A""))"
		}
	}
return
On the first time through, after it pops up 1,1 it throws this error:

Code: Select all

---------------------------
Script.ahk
---------------------------
Error:  0x800A03EC - 
Source:		(null)
Description:	(null)
HelpFile:		(null)
HelpContext:	0

Specifically: Cells

	Line#
	002: Loop,26*10+12
	003: {
	004: c := A_Index
	005: Loop,1506
	006: {
	007: r := A_Index
	008: MsgBox,r "," c
--->	009: formula := ex.Cells(r, c).formula
	010: if StrLen(formula)>80  
	011: ex.Cells(r, c).formula:="=IF(A5=""CP""," SubStr(formula,2) ",IF(A5=""Stats Sum"",(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+7 & ""C"" & COLUMN(),FALSE)+$B$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE)+(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+8 & ""C"" & COLUMN(),FALSE)+$C$2)*VLOOKUP(INDIRECT(""R"" & ROW()-MOD(ROW(),19)+6 & ""C1"",FALSE),CPMultiplier!$A:$B,2,FALSE)+(INDIRECT(""R"" & ROW() & ""C"" & COLUMN()-MOD(COLUMN(),17)+1,FALSE)+$D$2)*VLOOKUP(IND
	012: }
	013: }
	014: Return
	015: Exit
	016: Exit

Continue running the script?
---------------------------
Yes   No   
---------------------------
Clearly, I'm missing something. But, what?
Last edited by Exaskryz on 01 Dec 2018, 02:25, edited 2 times in total.
User avatar
Xtra
Posts: 1446
Joined: 02 Oct 2015, 12:15

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 01:58

r is always = 1 in the 1st one because you reset it every iteration with r:=0
edit: the indenting is horrible to read, But anyways its a range error issue.
Helgef
Posts: 3984
Joined: 17 Jul 2016, 01:02
Contact:

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 02:14

Looks like r is reset in the outer loop though... But its hard to read on the phone, especially without indentation.
User avatar
Exaskryz
Posts: 2876
Joined: 17 Oct 2015, 20:28

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 02:26

Formatting fixed. It was never an issue of r = 1. It does r++. 1506 times. Then goes back to 0. And repeats that 272 times.

To be clear, in both instances, the values of r and c are 1 to produce the 1, 1 pair. The first code continues on to 2, 1 then 3, 1. The second code, which uses A_Index, instantly fails at 1, 1. So we're not even really getting into the loops. Set the loops to loop 1 if you wish.
just me
Posts: 6512
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 04:54

It might be a type issue. AFAIK, all built-in AHK variables retrieve strings in v1. Try

Code: Select all

		formula:=ex.Cells(r+0,c+0).formula
Helgef
Posts: 3984
Joined: 17 Jul 2016, 01:02
Contact:

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 07:28

@just me, I've heard something like this before, but I can't recall if this is documented somewhere, do you have any reference?
Observe this,

Code: Select all

loop 1
{
	x := a_index
	y := (a_index)
}
msgbox % varsetcapacity(x) "`n" x
msgbox % varsetcapacity(y) "`n" y
I guess this is due to some backwards compatability issue or (if undocumented) it is a bug.
Caching wrote: In effect, a variable can contain both a string and a number simultaneously. Usually this just improves the script's performance with no down-sides, but if a variable contains both a number and a string, is it number, or is it a string? This ambiguity causes unexpected behavior in at least two cases:
  • COM objects. In order to pass parameters to a COM object, the program must convert the variable's content to either a number or a string. Some COM objects throw an exception if the wrong type of value is passed. If a variable has both, the number is used. Usually this gets the right result, but sometimes it doesn't
So if the issue is indeed a type issue, then x := a_index stores a string but doesn't cache a number, and y := (a_index) doesn't store a string but caches a number.

This should not be an issue in v2 :thumbup:.

Cheers.
just me
Posts: 6512
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 07:47

Hi Helgef,

are you able to test it? Is the +0 work-around actually working?

I had similar issues some time ago with some COM object and had to pass numerical values explicitely.
User avatar
Exaskryz
Posts: 2876
Joined: 17 Oct 2015, 20:28

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 10:39

Jumping on to test in just a moment. Thanks for the idea just me!

Edit: Yes, the +0 trick worked.

And I did a bit more diagnosis that it only matters that the c:=A_Index is set in the outer loop. If I set it in the inner loop (which I did to test if a 1,1 pair would work), there is no issue.

To clarify:

Code: Select all

ex:=ComObjActive("Excel.Application")
Loop 1
{
	c:=A_Index
	Loop 1
	{
	r:=A_Index
	ex.Cells(r, c+0).formula:="=C1"
	}
}
return
This code works.

But if I did

Code: Select all

ex:=ComObjActive("Excel.Application")
Loop 1
c:=A_Index
Loop 1
{
r:=A_Index
ex.Cells(r, c).formula:="=C1"
}
return
That code breaks. Note that I only did the +0 trick in the functional code on the variable c.

But, the next code will work:

Code: Select all

ex:=ComObjActive("Excel.Application")
Loop 1
{
c:=A_Index
r:=A_Index
ex.Cells(r, c+0).formula:="=C1"
}
return
User avatar
jeeswg
Posts: 6823
Joined: 19 Dec 2016, 01:58
Location: UK

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 10:55

This caused errors:

Code: Select all

c := A_Index
But not these:

Code: Select all

r := A_Index
c := A_Index, r := A_Index
r := A_Index, c := A_Index
There were no errors when using AHK v2.

Code: Select all

q:: ;test Excel and types (tests done on AHK v1 and AHK v2, errors relate to AHK v1)
MsgBox(A_AhkVersion)
oXl := ComObjActive("Excel.Application")
Loop 1
{
	c := A_Index+0
	r := A_Index
	MsgBox(oXl.Cells(r, c).Formula) ;no error (curious)

	r := A_Index+0
	c := A_Index
	;MsgBox(oXl.Cells(r, c).Formula) ;error
	c := (A_Index)
	MsgBox(oXl.Cells(r, c).Formula) ;no error
	c := A_Index+0
	MsgBox(oXl.Cells(r, c).Formula) ;no error

	c := A_Index, r := A_Index
	MsgBox(oXl.Cells(r, c).Formula) ;no error (curious)
	r := A_Index, c := A_Index
	MsgBox(oXl.Cells(r, c).Formula) ;no error (curious)
}
oXl := ""
return

;[MsgBox function]
;commands as functions (AHK v2 functions for AHK v1) - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=37&t=29689
It's worth mentioning that c (column) could commonly be a string, you can specify the column by letters instead of by numbers. So perhaps for rows: it converts a string that looks numeric to a number. And for columns: it sees a string that looks numeric, but it can only handle strings that contain letters, thus an error.

Some further tests:

Code: Select all

w:: ;test Excel and types
oXl := ComObjActive("Excel.Application")
oXl.Cells("1", "1").Formula := 1 ;error
oXl.Cells("2", "B").Formula := 2 ;no error
oXl.Cells("C", "3").Formula := 3 ;error
oXl.Cells("D", "D").Formula := 4 ;error

oXl.Cells(5, 5).Formula := 5 ;no error
oXl.Cells(6, "F").Formula := 6 ;no error
oXl.Cells("G", 7).Formula := 7 ;error
oXl.Cells("H", "H").Formula := 8 ;error
oXl := ""
return
Last edited by jeeswg on 01 Dec 2018, 12:39, edited 1 time in total.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
Helgef
Posts: 3984
Joined: 17 Jul 2016, 01:02
Contact:

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 11:40

jeeswg wrote:There were no errors when using AHK v2.

Code: Select all

r := A_Index, c := A_Index
MsgBox(oXl.Cells(r, c).Formula) ;no error (curious)
What is curious?
jeeswg wrote:

Code: Select all

r := A_Index+0
c := A_Index
;MsgBox(oXl.Cells(r, c).Formula) ;error
That would be curious, because,
built-in variables - a_index wrote:This is the number of the current loop iteration (a 64-bit integer)
a_index+0 or integer(a_index) should make no difference.
Helgef wrote:@just me, I've heard something like this before, but I can't recall if this is documented somewhere, do you have any reference?
I probably got it from v2 changes,
v2 changes wrote:The built-in "variables" true, false, A_PtrSize, A_IsUnicode, A_Index and A_EventInfo always return pure integers, not strings. They sometimes return strings in v1 due to certain optimizations which have been superseded in v2.
I can't recall seeing anything in v1 docs describing when these optimisiations occurs. Does someone know about this?

Cheers.
User avatar
jeeswg
Posts: 6823
Joined: 19 Dec 2016, 01:58
Location: UK

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 12:20

- @Helgef: Any errors occurred when testing on AHK v1.
- What's curious is assigning A_Index by itself, assigns it as a string, however, if the assignment is one of multiple comma-separated assignments, it appears to assign it as an integer.

Code: Select all

	r := A_Index
	c := A_Index
	;MsgBox(oXl.Cells(r, c).Formula) ;error

	c := A_Index, r := A_Index
	MsgBox(oXl.Cells(r, c).Formula) ;no error

	r := A_Index, c := A_Index
	MsgBox(oXl.Cells(r, c).Formula) ;no error
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
Helgef
Posts: 3984
Joined: 17 Jul 2016, 01:02
Contact:

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 12:24

I thought the test I referred to was done in v2.
User avatar
jeeswg
Posts: 6823
Joined: 19 Dec 2016, 01:58
Location: UK

Re: A_Index fails in Excel COM, manual increment works

01 Dec 2018, 12:52

Here are some useful tests. I.e. to shift the problem away from Excel.

Code: Select all

q:: ;text A_Index and types
oDict := ComObjCreate("Scripting.Dictionary")
oDict.Item(1) := "num"
oDict.Item("1") := "str"
Loop, 1
{
	MsgBox, % oDict.Item(A_Index) ;num

	vIndex := A_Index
	MsgBox, % oDict.Item(vIndex) ;str

	vIndex1 := A_Index, vIndex2 := A_Index
	MsgBox, % oDict.Item(vIndex1) ;num
	MsgBox, % oDict.Item(vIndex2) ;num

	vIndexParen := (A_Index)
	vIndexNum := A_Index+0
	vIndexStr := A_Index ""
	MsgBox, % oDict.Item(vIndexParen) ;num
	MsgBox, % oDict.Item(vIndexNum) ;num
	MsgBox, % oDict.Item(vIndexStr) ;str

	vNum := 1
	vStr := "1"
	MsgBox, % oDict.Item(vNum) ;num
	MsgBox, % oDict.Item(vStr) ;str

	MsgBox, % oDict.Item(A_Index+0) ;num
	MsgBox, % oDict.Item(A_Index "") ;str
}
return
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], Chunjee, Fefint, Google [Bot], just me, MARCAN, Odlanir and 227 guests