write Macro or DLL to copy data from worksheet to memory

[Desmond Leung]

Hi, I would like to write some functions
in Macro or DLL so that I can copy some data from
Excel sheet into memory so that I can call back from
other Excel sheets or macros.

It works as a global variable array, something like that.
Thanks

 

[Tushar Mehta]

If you must use global variables...

Option Explicit

Dim myArr As Variant

Sub loadMyArr()
myArr = Range(Range("A1"), Range("A1").End(xlDown))
End Sub

The above code puts the contents of the current region of A1 in the
active sheet into the variable myArr.

myArr will be a 2-D array that you can loop through with code such as:

Sub whatsInMyArr()
Dim i As Long
For i = LBound(myArr) To UBound(myArr)
Debug.Print myArr(i, 1)
Next i
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions