Data type confusion when trying to create VBA arrays from Excel ranges

Question

I have two sets of data in Excel, and I am trying to compare them in VBA. All the data is numeric (integers).

The "new" data is in a table, which gets copied over the "old" data as a range, and then the "new" data table gets updated.

Data format:

ID	Parameter_1	Parameter_2	Parameter_3	etc....
12345	3	7	9	etc....
67890	1	2	3	etc....
etc....	etc....	etc....	etc....	etc....

My code:

...
Sub sub1()

new_rows = ThisWorkbook.Sheets("new").Cells.SpecialCells(xlCellTypeLastCell).Row
old_rows = ThisWorkbook.Sheets("old").Cells.SpecialCells(xlCellTypeLastCell).Row
Dim new_data() As Variant
Dim old_data() As Variant
new_data = ThisWorkbook.Sheets("new").Range("A1:G" & new_rows).Value
old_data = ThisWorkbook.Sheets("old").Range("A1:G" & old_rows).Value
...
Dim update_tracker() As Variant
ReDim update_tracker(rows, columns)
...
updates_recorded = 0
For row_new = 2 To new_rows
     ID = new_data(row_new, 1)
     [code to match corresponding row in old_data that has same ID]

     new_Parameter_1 = new_data(row_new, 4)
     old_Parameter_1 = old_data(row_old, 4)
     new_Parameter_2 = etc....
     [all other data parameters]

     If new_Parameter_1 <> old_Parameter_1 Then
          update_tracker = track_updates(update_tracker, updates_recorded, ID, old_Parameter_1, new_Parameter_1, 1)
          updates_recorded = updates_recorded + 1
     End If
     [if statements for all other parameters]
Next row_new
[irrelevant code to put data into Excel]
End Sub

Function track_updates(update_tracker, updates_recorded, ID, old_Parameter, new_Parameter, parameter_number)
     If updates_recorded > 0 Then
          ReDim Preserve update_tracker(start To UBound(update_tracker, 1), [same columns])
     End If
     update_tracker(UBound(update_tracker, 1), col_1) = ID
     update_tracker(UBound(update_tracker, 1), col_2) = old_Parameter
     update_tracker(UBound(update_tracker, 1), col_3) = new_Parameter
     update_tracker(UBound(update_tracker, 1), col_4) = parameter_number

     track_updates = update_tracker
End Function

An error comes up for the 4th line: "Type mismatch". This is regardless if the arrays are Integer or Double.

VarType([range].Value) returns 8204 for both the new and the old ranges and the data is literally identical, yet the data can only be saved from the table into the first array.

If I Dim all the arrays as Variant then this part of the code works, but later when the ID number is being saved to the update_tracker the same error comes up.

Even though all 3 arrays are of type Variant, putting ?VarType(ID) into the "Immediate" window when debugging returns 5, which indicates that the number is somehow saved as a Double.

Using a different approach, from an answer I found on this site:

Dim old_data() As Variant
Dim r As Range
Set r = ThisWorkbook.Sheets("old").Range("A1:G" & old_rows)
old_data = r

results in the whole old_data array just being full of zeros.

Please help!

Pretty sure your arrays need to be type variant, when you are passing a range of values, no matter the type of data. See the late Chip Pearson's article on [Arrays and Ranges in VBA](http://www.cpearson.com/Excel/ArraysAndRanges.aspx) — Ron Rosenfeld, Nov 19 '22 at 17:02
Could you share the complete code so we could draw a conclusion? BTW, the line should be `old_data = r.Value` (`r` is a range, `r.Value` is an array (if more than one cell in `r`)) and there is a difference when using `Dim old_data As Variant` without the parentheses. But let's first see the code. — VBasic2008, Nov 19 '22 at 19:01
*ID number is being saved to the update_tracker*: You've never declared nor set `col_1` to a value. `?VarType(ID)` => the subtype of the stored value, not the type. Suggest you put `Option Explicit` at the top of your module, and declare all variables. — Ron Rosenfeld, Nov 20 '22 at 00:17

score -1 · Answer 1 · answered Nov 19 '22 at 17:10

-1

Since you're assiging a 2-dimensional array use Dim old_data As Variant instead of Dim old_data() As Variant (remove the braces)

answered Nov 19 '22 at 17:10

flow_283

1
1

1

Why? Also, this didn't help. – jamesanderson Nov 19 '22 at 17:38
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 25 '22 at 12:29

Data type confusion when trying to create VBA arrays from Excel ranges

1 Answers1