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?
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"
test array outside of a Sub or Function.Why wouldn't you create everything in a class? That's the reason why classes where invented after all.
Consider the Class1 definition
Option Explicit
Private m_data() As String
Private Sub Class_Initialize()
ReDim m_data(0 To 10)
End Sub
Private Sub Class_Terminate()
Erase m_data
End Sub
Public Property Get Count() As Integer
Count = UBound(m_data) - LBound(m_data) + 1
End Property
Public Property Get Data(index As Integer) As String
Data = m_data(index)
End Property
Public Property Let Data(index As Integer, value As String)
m_data(index) = value
End Property
Public Function Store(rng As Range) As Boolean
Store = (rng.value = m_data(0))
End Function
You can add all the functions you want that can access your array just like Store().
with the test code in a worksheet of
Public Sub Test()
Dim c As New Class1
c.Data(0) = "January"
Debug.Print c.Store(Cells(1, 1))
End Sub
You can also cache the location of the cell where it is referencing, or used an assumed named argument and only supply a reference to the worksheet once after class initialization.
You can use the Public keyword to declare a variable that you need to access in any module.
Remember that in vba you cannot declare variables or code outside of procedures.
See here for more information
I have a recommendation that is a bit lighter than a class (although class is a great recommendation)
Define your desired constant array as a delimited string constant:
Public Const cstrTest = "String 1;String 2; String 3; String 4; String 5; String 6"
Next, whenever you need it just use Split to create an array with minimal code:
Dim arrStrings
arrStrings = Split (cstrTest, ";")
You might replace (or combine with Option 1) a simple public function
Public Function constStringArray() As String()
constStringArray = Split (cstrTest, ";")
End Function
So then, in use...
Dim arrStrings
'Option 1 example
arrStrings = Split (cstrTest, ";")
'Option 2 example
arrStrings = constStringArray()
one can do it (with global initialization) via Static Property quite straight-forward without creating a class or string parsing - as described in detail and with examples here
