Declaring variable types within an array

[roscoe]

I have an array with a mix of data types (strings, dates, integers, etc...). When I use the watch window for debugging, some date values show up as dates, but others as a real number (which isn't very helpful). Not sure how that happens, but that question is for another time. In the meantime...

Example, I have an array dimensioned as testarray(1 to 100, 1 to 5) [100 rows with 5 columns]. My data is entered as:
Columns 1,4: String
Columns 2,3: Date
Column 5: Single

How do I declare these data variable types in an array so they are readable as such in the watch window?

Thanks

 

[joeu2004]

If the declaration is literally

Dim testarray(1 to 100, 1 to 5)
or
Dim testarray(1 to 100, 1 to 5) As Variant

you are doing the right and necessary thing: declaring testarray as an array of variants.

I suspect that some dates appear as real numbers (type Double) because of their origin or the arithmetic you perform. Use CDate to ensure that values are stored as Date.

Aside.... IMHO, column 5 should be type Double, not type Single. "Never" use type Single. The limited precision of type Single can cause suprising results, especially when loading from or storing into Excel cells.

(Okay, I use type Single to store the results of Timer, only becuase I need to retain that limited precision/resolution for some of my explanations of time arithmetic. Most people would not care.)

 

[pgc01]

Hi

If you really want/need to have each column with a specific type you could use an array of arrays, like

Sub Test()
Dim a(1 To 100) As String, d(1 To 100) As String
Dim b(1 To 100) As Date, c(1 To 100) As Date
Dim e(1 To 100) As Double
Dim arr As Variant

arr = Array(a, b, c, d, e)
arr(0)(20) = "string1" ' first column is type string
arr(1)(10) = DateSerial(2020, 1, 1) ' second column is type date
arr(4)(15) = Exp(1) ' fifth column is type double

End Sub

As you see the syntax changes a bit.

Another option would be to define a type with the 5 elements with the respective types and then declare an array of that type. The syntax would change more.

In both options you'd get an array in which each column has its own type.

 

[roscoe]

I suspect that some dates appear as real numbers (type Double) because of their origin or the arithmetic you perform. Use CDate to ensure that values are stored as Date.

I would agree with you but in both cases I'm just reading in "numbers" (that represent date/time stamps) from two separate cells; I'm not doing any arithmetic on them. First value will always show in the array watch window as variant/date and the second always shows variant/double. I could try "Dim Datetemp as Date" and do an intermediate step of reading the cell into DateTemp then loading DateTemp into the array (then eliminate the double step once debugging is complete).

I've used Cdate a lot but in my experience it was designed to turn a date/time string into a date valuem..Mine is already a proper date value.

Aside.... IMHO, column 5 should be type Double, not type Single. "Never" use type Single. The limited precision of type Single can cause suprising results, especially when loading from or storing into Excel cells.

I'm only reading in a value from a cell to preserve it (with no interest or need to go to double precision accuracy) and to perform a greater than/less than compare later. I would think Single is enough...but I'll keep my eyes open for odd behavior, thanks!

Hi

If you really want/need to have each column with a specific type you could use an array of arrays, like

Sub Test()
Dim a(1 To 100) As String, d(1 To 100) As String
Dim b(1 To 100) As Date, c(1 To 100) As Date
Dim e(1 To 100) As Double
Dim arr As Variant

arr = Array(a, b, c, d, e)
arr(0)(20) = "string1" ' first column is type string
arr(1)(10) = DateSerial(2020, 1, 1) ' second column is type date
arr(4)(15) = Exp(1) ' fifth column is type double

End Sub

Very interesting, I'll have to take alook at that. Thanks

 

[joeu2004]

I'm only reading in a value from a cell to preserve it (with no interest or need to go to double precision accuracy) and to perform a greater than/less than compare later. I would think Single is enough

Enter 2.34 into A1.

Execute the following VBA code.

Sub doit()
Dim x As Single
x = Range("a1")
Range("a2") = x
End Sub

Enter to the formula =A1=A2.

The result is FALSE. QED.

 

[roscoe]

Fair enough, thank