EXCEL: How to Initialize a Global Array ??? [Resolved]

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
EXCEL VBA

How do I initialize a global array?

I can Dim an array before any of my Subs/Functions, but if I do that, there doesn't seem to be any way to initialize the array in the Dim statement.

I can set: VarName = Array(7, 3, 8, 4)
but I have to do that in a Sub/Function, and I need to get to the array from several different Subs/Functions. I don't really want to initialize the array every time I call a Sub/Function that uses it. And no, I don't want write a test to see if it is initialized. I just want to create and initialize the array one time.

Thank you for any and all comments, suggestions, and assistance.

[salvelinus]

Well, if it's a global array I'd declare it in a module using Public, not Dim. If you mean a module level variable, why not initialize it in the form open or resize event? If you've declared the array in the General Declarations section, it wil be available to all subs & functions in that module, or if truly global, to all subs/functions in any module you may have (although that can lead to problems if a sub changes the array, and then another sub uses the array expecting the previous values). I usually only use module or global variables for values that don't change once set, or can be reused (i.e., you don't necessarily need to keep declaring new recordset variables in every sub as long as you close them and set them to nothing every time a sub is done with it. Declare it at module level and keep reusing it).
The best way would be to pass it as a parameter between functions, but maybe that doesn't work for you.

[asmdev]

this sample xls file should help you
please set ur macro security to medium otherwise u wouldn't be able to run any macro on excel

[Webtest]

Thanks for the help ...

(I am building a VBA Macro in Excel.)

I finally figured out that I can Dim the variable at the top of the Edit window (before any Subs or Functions):
Dim G_myArray As Variant

Then, in the first Sub that uses the array, I call the Function that initializes all of the arrays. The initialization is just:
G_myArray = Array( 7, 4, 8, 2) (or whatever)

After that, I can reference the array in any Sub or Function.

[salvelinus]

Thanks for the help ...

(I am building a VBA Macro in Excel.)

I finally figured out that I can Dim the variable at the top of the Edit window (before any Subs or Functions):
Dim G_myArray As Variant

That's known as the General Declarations area

Then, in the first Sub that uses the array, I call the Function that initializes all of the arrays. The initialization is just:
G_myArray = Array( 7, 4, 8, 2) (or whatever)

After that, I can reference the array in any Sub or Function.

Yep, or in the form (spreadsheet) open event.

[Webtest]

Thanks, Savelinus ...

I appreciate the specific comments ... I'll try to remember "General Declarations". I've been around programming for many years, but I'm more of a technician/hacker than a 'real' programmer.

I've never used events before in Excel VBA programs ... could you perhaps post a very brief template to get me started with the workbook open event? I'll do a little research on my own.

Thanks again ...

[salvelinus]

The easiest way I know is to right click the worksheet title bar and click View Code from the pop up menu. This takes you to the code sheet for the worksheet in the VBA editor. This looks similar to the module code sheets for macros. Notice the two drop down boxes at the top of the sheet. The first should say General, the second Declarations.
Click the first box, and select Workbook. Then click the second and select Open. The code sheet will now have an empty sub for Workbook_Open(). Put whatever code you want to run when the workbook opens in there. You probably won't be able to use the data in the spreadsheet at this point, because it's not done opening yet. But you can initialize variables, etc, which is what you want here.
Events are things that happen to objects. Open is a common one, Click (as in a button) another. The first drop down box lists the objects in your app, the second lists the events that can happen to the object.