VBA How to declare an array with elements with different datatype

Question

I have an array that I have created to store error records and it has the following elements: Serial No,File name, error type, error cell, error cell value

As of now I have declared my array like this and then I populate the values later on.

Dim errorArray() As String

But ideally, I want Serial Number to be a proper integer, but it is getting converted into string. I don't know how to declare this correctly so that I can have Long datatype for the first element and string for the next 3 and variant for the last one.

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-10-25 13:01:07Z

26

Create a private type in your code, like this:

Private Type ErrRecord
    SerialNo As Long
    FileName As String
    ErrorType As String
    ErrorCell As String
    ErrorCellValue As Variant
End Type

And then in your routine, call it like this:

Dim errorArray(0) As ErrRecord
With errorArray(0)
    .SerialNo = 12345
    .FileName = "Test.xls"
    .ErrorType = "Bad error"
    .ErrorCell = "1234"
    .ErrorCellValue = "Test"
End With

answered Oct 25, 2012 at 13:01

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nasrul Arif Over a year ago

What if I want a dynamic array. Like I am storing data retrieved from DB, so columns may differ in datatype.

GeertVc Over a year ago

Should be the accepted answer. Neat and clean solution.

ORION · Accepted Answer · 2012-10-25 13:03:05Z

8

You'll want to create an array of type Variant. An array of type Variant can store any data type in any of its elements.

Dim astrItems(0 To 9)    As String
Dim varItems             As Variant

varItems = astrItems

answered Oct 25, 2012 at 13:03

ORION

2,4313 gold badges24 silver badges34 bronze badges

1 Comment

Siraj Samsudeen Over a year ago

So obvious, but I missed it. In fact, I was thinking that I declared an array of type Variant, so I was a bit confused when I saw your response. Thanks.

Collectives™ on Stack Overflow

VBA How to declare an array with elements with different datatype

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related