Home > Net >  VB - How to initialise a character array?
VB - How to initialise a character array?

Time:05-23

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

Language Reference for VBA

Excel VBA Programming for complete beginners

  • Related