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.

Answer 1

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