I'm trying to accomplish this obviously simple thing, but somehow VBA keeps serving me weird errors. I would like to have a global array named styles containing the following strings: Settings, Titles, Comment and Direct Copy. in Code:
Public styles(4) As String
Directly assigning the array was not working for me so i did this via a sub:
sub Populate()
styles(0) = "Settings"
styles(1) = "Titles"
styles(2) = "Comment"
styles(3) = "Direct Copy"
Debug.Print styles
End Sub
However this does not work as it gives a compile error: Type mismatch on the debug.print line... The expected result was something like: ("Settings", "Titles", ..) etc like any programming language would return.
So how do I get a public array containing strings in VBA Excel such that I can use them in the same module across functions and subs?
Try this form of public declaration and array element assignment.
Option Explicit
Public styles As Variant
Sub printStyles()
styles = Array("Settings", "Titles", "Comment", "Direct Copy")
Debug.Print LBound(styles) & "to" & UBound(styles)
Dim i As Long
For i = LBound(styles) To UBound(styles)
Debug.Print styles(i)
Next i
Debug.Print Join(styles, ", ")
End Sub
BTW, there a reserved Styles Object which you may have difficulty using if you continue to use reserved words as the names of your public and private variables.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With