Excel Text value not recognized as numeric key unless calculated in the same function Topic is solved

[fatodubs]

Code: Select all

For k,v in StrSplit(arrIO[i,zcCol["VP Role IDs",1]-iOff],"`n")
	If IsNum(v) {
		strChk1:=zcShared.VPSERXWalk[v]
		v*=1
		strChk2:=zcShared.VPSERXWalk[v]
		strChk3:=zcShared.VPSERXWalk[7175]
		MsgBox %strChk1% - %strChk2% - %strChk3%
	}

When v=7175, only the last 2 work to find a value; strChk1 is always empty.

arrIO is a range pulled from Excel via Formula2R1C1 and this cell in question is formatted as Text, but the help text seems to suggest that using a variable as a key to get data from an array would be seen as an integer: "Additionally, if a quoted literal string is concatenated with another value (as in "0x" x), the result is treated as purely non-numeric. However, this does not apply to variables, so x[1] and x[y:="1"] are equivalent."

I'm not sure if it matters, but zcShared.VPSERXWalk is shared from another script via ObjRegisterActive. In SciTE, the key is seen as an integer in the originating script - there are no quotes around it.

[fatodubs]

The version for the original report was 1.1.33.02. I'm upgrading now to 1.1.33.10 (1.1.33.11 is being blocked by Windows Defender for being unsigned, and I'm on a work computer so easier to check back than to try to get it installed by someone else.)

I will report back on the results.

[fatodubs]

It's still an issue with 1.1.33.10.

Also, it doesn't work if the value is calculated in another function. For example:

Code: Select all

FixExcelID(strID) {
	strID:=Trim(strID)
	If IsNum(strID) {
		strID:=Format("{:i}",strID)
		strID*=1
		return strID
	} else if IsAlphaNum(strID)
		return Format("{:U}",strID)
	else
		return strID
}

IsNum(str) {
	If str is number
		return 1
	return 0
}

IsAlphaNum(str) {
	strChk:=StrReplace(str,".")
	If strChk is alnum
		return 1
	return 0
}

Side note: I recently switched to Formula2R1C1 for pulling data from Excel instead of Value (or Value2, I don't recall). Before that, I just ran every possible ID cell through the function above and it worked. I was hoping this would be more efficient because I wouldn't need to reformat everything, but I'm starting to question that.

I still think this is a bug, as the help text says to avoid quoted literal string but that variables should be fine - but that doesn't match what I'm experiencing. I especially think it's a bug because it doesn't retain the integer when passed from a function.

[lexikos]

This is likely a consequence of v1's vague type system and number caching, changes to which cannot be made in a minor/bug-fix release, and have already been made in v2. If you want a better explanation, please provide a standalone minimal script that reproduces the issue.

zcShared.VPSERXWalk is shared from another script via ObjRegisterActive.

Then zcShared.VPSERXWalk isn't an array; it's a COM proxy object, which accepts calls via the IDispatch interface and forwards them to an external process via COM interop channels. IDispatch has its own rules different to AutoHotkey's. The documentation you quoted only applies to the interpretation of keys by AutoHotkey's objects when invoked directly. The actual object cannot receive your variable, only either a pure number or string.

1.1.33.11 is being blocked by Windows Defender for being unsigned

The installer hasn't been signed for several versions now, including 1.1.33.10. AutoHotkey itself has never been signed. If Defender doesn't like the installer, try the zip.

Likely, it's being blocked for being new, nothing more.

[fatodubs]

Thank you for the info, and the clarification on the state of the installer (and all the other stuff you do for the AHK community!). I thought that might also be a possibility, but since the next oldest one downloaded and ran without issue - I assumed the warning Windows was giving was somehow relevant. Silly me!!!

I won't bother either of us with putting together a script for a more detailed explanation (unless you want one for further investigation from your side); I understand it's a limitation with the underlying structure of v1 and the interaction with external objects. TBH, I'm actually kind of fascinated with how the data type is handled and stored, and how it varies from within a function to out of it, but it also sounds like a making of the sausage kind of thing.

For now it's just something to deal with and account for - like I had been doing with numeric Excel values and decimal points - until I move over to v2 one day. Perhaps this is the impetus I need

Thanks again!

[lexikos]

I thought about it some more, and came up with a simple way to demonstrate the issue:

Code: Select all

#requires AutoHotkey v1.1

obj := {}
obj[1] := "0"
obj["1"] := "a"
obj.sub[1] := "1"
obj.sub["1"] := "b"
disp := ComObj(9, &obj)
MsgBox %
(Join
    obj[1]
    obj["1"]
    obj[v:=1]
    obj[v:="1"]
    obj.sub[1]
    obj.sub["1"]
    obj.sub[v:=1]
    obj.sub[v:="1"]
    "`n"
    disp[1]
    disp["1"]
    disp[v:=1]
    disp[v:="1"]
    disp.sub[1]
    disp.sub["1"]
    disp.sub[v:=1]
    disp.sub[v:="1"]
)

The result is

Code: Select all

0a001b11
00001b1b

0000 shows that the first parameter of the invocation is permitted to be interpreted as numeric after passing through IDispatch. The caller converts the parameter to a string and passes it through as the property name of the invocation. The callee receives a property name (a string) and uses its value as the first parameter, and it ultimately can be converted to a number.

If you use __get, you will find that a property name like "0x02" actually comes through as 2. It is actively converted to a 32-bit integer, which could backfire in some cases (e.g. a long string of digits), but if you're not using meta-functions, it generally won't matter because such values would be converted to integers for use as keys anyway. The original purpose for the conversion was to support JScript, where it can look like you're performing array indexing (disp[1], just like AutoHotkey), but 1 is actually a property name.

1b1b shows that all strings passing through the second parameter via IDispatch are treated as non-numeric. It doesn't matter how the string originated, because passing through IDispatch "normalizes" the value. It probably would have been better to treat parameter strings as impure (potentially numeric), but v1 is stuck with the current behaviour.

obj is the actual AutoHotkey object and disp is a wrapper object which simply calls the IDispatch interface of the actual object. All of the conversion between AutoHotkey types and COM types is performed by AutoHotkey, but the two systems aren't 1:1 compatible.

For historical reasons, quoted numeric strings such as "123" are always considered non-numeric when used directly in an expression (but not when stored in a variable or returned by a function).

The documentation intentionally does not go into implementation details; I suppose that would require a level of verbosity that would confuse more than it would help, and it might (in general) restrict potential improvements. As a consequence, it may be technically inaccurate.

Implementation details

When it says "quoted numeric strings", it actually means any input value tagged as SYM_STRING. If such a value is used as a key or in a numeric comparison or math operation, it is considered non-numeric. However, this tag is only meaningful within a limited context, and is not retained (or even have a single consistent meaning) throughout the language.

By contrast, SYM_OPERAND represents a value of "undetermined type"; i.e. a string which could be numeric or non-numeric. String results from functions within an expression are generally SYM_OPERAND.

Different parts of the v1 "engine" do not make the same distinctions, or use the same methods of storing values and type information.

• Objects use the same tags, but all strings are stored as SYM_OPERAND, not SYM_STRING; i.e. once they are returned, the caller can determine whether they are numeric or not.
• Variables can hold a string and a cached number. This cache was originally designed for performance, not to permit distinctions by type, so information about the original type of the value is sometimes lost.
• User-defined functions primarily return strings as a plain null-terminated character string, and use the "SYM" convention only when returning binary numbers and objects.
• Built-in function/object string return values are required to be SYM_STRING, but they normally degrade to SYM_OPERAND for expression evaluation. So an object might have a SYM_OPERAND field which is returned as SYM_STRING and then becomes SYM_OPERAND again within the calling expression.
• Commands basically just deal with null-terminated strings or variable references. Numeric expressions produce a numeric string.
• COM interfaces use a documented set of tagged types. A string is a string (a BSTR, actually), and any COM server can choose what to do with the string.

[fatodubs]

@Lexicos, I ran into another one that was kind of interesting - in basically the same code. This is still running in V1 as I'm working on updating my custom libraries to V2. I'm not expecting any investigations, but just thought I would share.

One script assembles several different Excel and CVS files from different systems into various nested objects in a ComObject. Other scripts connect to the ComObject and use it to run different types of audits on the data.

Most of the user IDs are alphanumeric, but some are just numeric. All of the numeric IDs - except one - are working with this code to prime it to be numeric:

Code: Select all

		If IsNum(strEMPID)
			strEMPID*=1

and this code to reference it:

Code: Select all

strJunk:=zcShared.AllUsers[strEMPID,"LastLogin"]

All the IDs are stored as text in an Excel sheet from which they are loaded into the shared object and pulled from a different file by the second script to populate the variable.

I tried several different variations including quoted and unquoted literals. Here are some non-working examples:
zcShared["AllUsers",strEMPID,"LastLogin"]
zcShared.AllUsers[3041800000,"LastLogin"]
zcShared.AllUsers["3041800000","LastLogin"]

These two found the correct value, but only the latter is actually workable because I can't use the literal:
zcShared.AllUsers.3041800000.LastLogin
zcShared["AllUsers"][strEMPID]["LastLogin"]

Also, I believe the one numerical ID that isn't working is longer than the others. It's 10 digits ("3041800000").