[VBA] Does longs get loaded into memory before doubles when passing large range into array?

[AiRiFiEd]

Hi all,

My boss encountered a weird problem today but I am unable to post snippets of the code as it is part of a corporate .xlam library.

Basically, a function was written to take in a range using a variant parameter. Range that was passed in was an entire column, meaning 1048576 rows.
Each cell contains doubles but some are whole numbers (essentially longs).

Basically, the function loops through the cells in vba and pass the non-empty cells into another array.

The weird part:
- when assigning the range to a variant array, cells with decimals are empty (Dim vTemp as variant: vTemp = Range("A:A") - for example)
- this prompted investigation by breaking at that line of code
- accessing the range object direction via rng.Cells(i,j) returns the value with decimals
- further running of the code reveals that the function is called twice (or multiple times) and on the second pass, the decimal values appear in the variant array

Can anyone think of any potential cause for this phenomenon?
Is it documented anywhere that, due to the sheer size of the range object, the longs are loaded first, followed by the doubles?

Thanks in advance for your help!

 

[JoeMo]

Is the "phenomenon" you describe seen when running this simple code?

Sub KISS()
Dim vTemp As Variant
vTemp = Range("A:A").Value
MsgBox UBound(vTemp, 1)
MsgBox vTemp(1048576, 1)       'Change row number to test cell with a Long in it
MsgBox vTemp(1048573, 1)       'Change row number to test cell with a Double in it
End Sub

If not, then the "function" you can't post may be the culprit.

 

[AiRiFiEd]

Is the "phenomenon" you describe seen when running this simple code?

Sub KISS()
Dim vTemp As Variant
vTemp = Range("A:A").Value
MsgBox UBound(vTemp, 1)
MsgBox vTemp(1048576, 1)       'Change row number to test cell with a Long in it
MsgBox vTemp(1048573, 1)       'Change row number to test cell with a Double in it
End Sub

If not, then the "function" you can't post may be the culprit.

Hi Joe, many thanks for your response! Today is a public holiday in Singapore - will test the code tomorrow when i am back in office.

May i check with you what your suspicion is at the moment?
Few points that was noted:
- the error occurred on row 520 (and not at the very large indexes - e.g. the last row @ 1048576)
- ?rng.Cells(i,j) in the immediate window returns the correct value (where rng is the parameter that was passed into the function)
- the function parameter was declared as a variant type instead of a range type (although i do know i am able to do vTemp = rng and get all the values, I am not sure if there is any information leakage)
- as these functions are part of a larger library, the rng object was passed on around 3-4 times, alternating between para as range and para as variant, depending on the original design of each function

Hope this clarifies and thanks alot for your help!

 

[JoeMo]

The more you describe the function the more I suspect it does more than just assign the values of a range object to a variant array. You can change the row numbers in the message boxes to rows with cells that contain decimal numbers to see if those numbers are stored in the vTemp array.

 

[AiRiFiEd]

Hey Joe,

If i were to assign the range to a variant array in a separate standalone sub, it actually works. Any chance that a complex function will cause excel to load the large range object into memory by parts?

 

[JoeMo]

Hey Joe,

If i were to assign the range to a variant array in a separate standalone sub, it actually works. Any chance that a complex function will cause excel to load the large range object into memory by parts?

Don't know for sure, but I really doubt it. Even if the array elements were not filled sequentially, the final result, inaccessible until the entire array is filled, would not have missing elements.

 

[AiRiFiEd]

Hi Joe, thanks for your prompt reply. Can i confirm that the entire array is indeed inaccessible until the entire array is filled? Thanks again for your help!

 

[JoeMo]

Confirmed.

 

[AiRiFiEd]

Thank you! i guess we will have to look at the implementation of the functions.
Thanks again!

 

[JoeMo]

Thank you! i guess we will have to look at the implementation of the functions.
Thanks again!

You are welcome - thanks for the reply.