Life of an array 1

[Numbers1]

I have created the following procedure which loads an array using Sub LoadArray.

Dim aryFunds () as Variant
Dim intCountRow as Integer
Dim intCountCol as Integer
Dim strCellContents as String

aryFunds (ArrayName)

Private Sub LoadArray (ByRef ArrayName as Variant)

For intCountRow = 1 To 5
For intCountCol = 1 To 3
strCellContents = ActiveSheet.Cells(intCountRow, intCountCol)
ArrayName(intCountRow, intCountCol) = strCellContents
Next intCountCol
Next intCountRow

End Sub

I want to keep the contents of this loaded array for usage in other subs. How do I retain the life of this array outside of the sub that loaded it?

Thanks, Numbers

 

[TonyJollans]

Hi Numbers,

The life of a variable (array or otherwise) depends on where it is declared. If it is declared inside a procedure, it will live only as long as the particular instance of that procedure is running. If it is declared in the declarations section of a module (at the top before any procedures) its lifetime will be the same as the lifetime of the module (in practice, the lifetime of the application). Where you can use it then depends on its scope (private or public).

In your case ..

Dim PrivateArray(1 To 5, 1 To 3)
Public PublicArray(1 To 5, 1 To 3)

Sub ArraySetUp()

LoadArray PrivateArray
LoadArray PublicArray

End Sub

.. will load two arrays. PrivateArray will be available to all procedures in the module, and PublicArray will be available to all procedures in all modules.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Numbers1]

Tony:
So what I am missing is the row and column references when I first declared the array in the original declarations, right? I am using variables for the row and column references so that the code is more flexible.

Dim aryFunds (intCountRow, intCountCol) as Variant

Thanks for your help, Numbers

 

[TonyJollans]

Hi Numbers,

Well, yes. You must, one way or another, declare the size of your array. I hadn't registered your declaration to start with. Do remember that arrays are (normally) zero-based unless you specify otherwise.

I think you're overcomplicating something here but haven't time to look any more right now. Will look again later.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[TonyJollans]

Hi Numbers,

If you are just trying to create an array from a range in your worksheet, you can do it much more simply like this ..

Dim aryFunds As Variant

aryFunds = ActiveSheet.Range(Cells(1, 1), Cells(5, 3))

.. and aryFunds will get cast as an array of the right size (and obviously you can use variables instead of the numbers I've used, if you want). But, as the range is already an array, you only really need to do it at all if you want to store values before they are changed.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Numbers1]

Please note. I am trying to create a procedure that I can use to load various named arrays in my module from the same procedure. The problem I am having is getting the loaded array in the procedure to return the values to my calling array outside of the procedure.
Thanks, Numbers

 

[TonyJollans]

Hi Numbers,

Provided your arrays are Dimmed at module level you should be able to reference them in other procedures. If you can't, can you post some more code showing where it fails?

You first reply indicated that perhaps you had a problem sizing your arrays rather than just referencing them. You must declare your arrays to the right size somehow - either in the original Dim statement (at compile time) or via a Redim (at run time) or implicitly somehow. If I'm misunderstanding something, please post back, with code if relevant.

Sorry if I confused you, but my last post was not to do with visibility. There isn't anything particularly wrong with your routine; my point was just that, if the source data is an array of cells you can load the array more simply and that, unless you want to save values which will later change, you don't actually need to do it at all.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.