Single Cell Array?

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
Never ran into this before...guess it just never came up.

So, I have a simple VBA array:

vars = Range("G2:G" & Cells(Rows.Count, 7).End(xlUp).Row).Value

where vars is DIM'd as a variant: Dim vars() as Variant

Problem is, when the array turns out to be one cell, it craps out...type mismatch. What's a workaround?

Thanks.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Remove the parentheses after vars in your declaration. You can use the IsArray function to test if what's assigned to vars is an array.
 
Upvote 0
Never ran into this before...guess it just never came up.

So, I have a simple VBA array:

vars = Range("G2:G" & Cells(Rows.Count, 7).End(xlUp).Row).Value

where vars is DIM'd as a variant: Dim vars() as Variant

Problem is, when the array turns out to be one cell, it craps out...type mismatch. What's a workaround?
First off, remove the () from the declaration for vars...

Code:
Dim vars as Variant

A plain Variant can hold an entire array, when you declare the variable with the parentheses as you did, you set up an array of variants where each element of the array is a Variant capable of holding anything including an entire array... you are just working with a simple array so you do not need the full out array of variants... the simple variant suffices. Now, that change won't solve your problem because Excel does not consider a single cell to be an array. You could test what it in the vars variable and react accordingly. For example,

Code:
Dim vars As Variant
vars = Range("G2:G" & Cells(Rows.Count, 7).End(xlUp).Row).Value
If TypeName(vars) = "Variant()" Then
  [COLOR="#008000"][B]' vars contains a two-dimensional array, put the code for that here[/B][/COLOR]
Else
  [COLOR="#008000"][B]' vars contains a single value, put the code for that here[/B][/COLOR]
End If

Alternately, you can force vars to be a simple two-dimensional array even if the range being assigned only has one cell in it...

Code:
Dim vars As Variant
vars = Range("G2:G" & Cells(Rows.Count, 7).End(xlUp).Row).Value
If Not IsArray(vars) Then
  vars = Range("G2:H2").Value
  ReDim Preserve vars(1 To 1, 1 To 1)
End If
[COLOR="#008000"][B]' Rest of your code goes here[/B][/COLOR]

Note what I have done here... I deliberately assign two horizontal cells to vars (if it is not an array already) and then ReDim Preserve it down from the two columns I assigned to a single column.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top