Can I declare global arrays with VBA

[MrTeeny]

Can I declare a global array that's available to all modules within the workbook, I tried declaring a variant array as Public but it gave me an error

 

[Fluff]

Yes you can. Have a look here for details
http://www.cpearson.com/excel/scope.aspx

 

[Norie]

Where did you try and declare the array and how did you try and populate it?

 

[MrTeeny]

Where did you try and declare the array and how did you try and populate it?

I declared it as a Public Variant on the sheet 1 object before running a Worksheet_Change event

Option ExplicitPrivate Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
Public priceArray() As Variant
 




Private Sub Worksheet_Change(ByVal Target As Range)........etc

I usually declare it within the Worksheet_Change event but want the data to be available to all modules

Private Sub Worksheet_Change(ByVal Target As Range)

Dim priceArray() As Variant....

works fine but obviously the array isn't avaliable all over

 

[Norie]

It should be declared in a standard module not a class module like a sheet module.

 

[Rick Rothstein]

works fine but obviously the array isn't avaliable all over

Try declaring it in a general module (where you install macros) rather than in a sheet module (where you install event code) and do not declare it a second time in the event procedure (as your code above shows you doing)... then you should be able to ReDim it in the Change event (or any other event, macro, function, etc.) procedure to give it size and then fill it with values or, alternately, assign an exist or newly created array to it.

 

[MrTeeny]

It should be declared in a standard module not a class module like a sheet module.

Thanks , seems to all be working now .



I had a look at the link Fluff posted and could you tell me how I'd declare the variables as a project scope variable, I'm assuming the project scope means any variables are only available to that workbook and wouldn't get picked up or interfered with if I say had two versions of the workbook running?

At the moment I simply have

Option Explicit


Public priceArray() As Variant

But guessing I need to incorporate [FONT=&quot]Option Private Module[/FONT] within there somewhere