Search code examples
arraysexcelglobal-variablesscopevba

VBA: Arrays and Global Variable Declarations

I need to declare an array in VBA that will be used by every function. However, I cannot declare it as a global as I would do in C++.

My code is as follows:

Option Explicit
 Dim test(0 to 10) as String

 test(0) = "avds"
 test(1) = "fdsafs"
 ....

The following conceptualizes what I am trying to do. 

 public function store() as boolean
  Worksheets("test").cells(1,1) = test(0)
 End Function

How can I achieve this functionality?

Solution

  • For global declaration, change Dim to Public like so:

    Public test(0 to 10) as String

    You can call this like (assuming it is in Module1, else change Module1 to whatever you've named it):

    Module1.test(0) = "something"

    Or simply:

    test(0) = "something"