I am trying to do it as below with runtime error 91 - Object variable not set
Dim columns(6) As Characters
Dim cColCount As Integer
Dim sColCount As Integer
columns(0) = A
columns(1) = B
columns(2) = C
columns(3) = D
columns(4) = E
columns(5) = F
I'm new to VB and all online resources only show examples of Integer and some string arrays. Unfortunately, like New Integer()
, there seems to be nothing for a character array.
What should be my approach for a character array?
CodePudding user response:
VBA thinks that Characters
is the Characters object for a range. See Characters object (Excel) Microsoft Docs
Dim columns(6) As Characters
defines an array but not the objects in each element. If you had created your own class called Characters then you could do something like this for each element
Set columns(0) = New Characters
But at the moment that will also produce an error ("Invalid use of New keyword")
The real problem is understanding that a set of "characters" is called a String in VBA (and many other languages).
So as @braX has pointed out, you need to use Dim columns(6) As String
. Because String is built in to VBA you don't need to "initialise" each instance in the array.
Your next problem is understanding how strings are represented in VBA and many many other languages. You can't just stick an A
out there and hope that Excel understands you mean the letter "A". In VBA we surround strings with double-quotation marks so columns(0) = "A"
would be ok.
Alternatively you would have to define the Variable A e.g. Dim A as String
and assign it a value e.g. A = "A"
Here are a couple of other links to help you on your journey