Typecasting Variant Array into String or Boolean Array

Question

I have several named ranges inside my Excel sheet where I store data for VBA Macros between sessions. When loading I need to get that data into multiple arrays (String and Booleans).

Dim arr() As Variant
Dim rg As Range

Set rg = Sheets("Calc").Range("myRange")
arr = rg.Value2

How can I typecast arr() into a String or Boolean array for use in my macro?

If I try and use Variant arrays instead I get ByRef argument mismatch errors on function calls.

Or Is there maybe another way to get the contents of the ranges into an array of other type than Variant?

Searching did not yield a result on the latter question.

For a Boolean, you'd have to populate the array in a loop. For strings, as long as it's a one row or column range, you can do it with a little messing about, but looping is still simpler. — Rory
– Rory, Commented Sep 30, 2015 at 14:03
Can't you cast it to a string etc when passing to your function? Something like CStr(arr(1, 1)) - or do you pass the entire array into your function? — SierraOscar
– SierraOscar, Commented Sep 30, 2015 at 14:15
I'm not aware of any way of doing that other than manually casting in a loop. Wouldn't it be easier to test for type within the function and just pass the Variant: If VarType(v) = vbBoolean Then? — Ambie
– Ambie, Commented Sep 30, 2015 at 14:23

R3uK · Accepted Answer · 2015-09-30 14:21:44Z

1

Easiest way is to declare your array as an array of Strings or Booleans, simply like this :

Dim arr() as String
Dim arr() as Boolean

It works also for functions btw.

And if you can't load the whole range because of a type mismatch, load this in an temporary Variant Array and then ReDim your String or Boolean array to match and loop on everything and use conversion functions : CBool, CStr, CInt, CDbl, ...

answered Sep 30, 2015 at 14:21

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

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

5 Comments

Christian Over a year ago

I was trying to avoid looping but it seems to be the easiest way. Wouldn't it be easier to loop over the range right of the bat instead of loading into a variant array first?

R3uK Over a year ago

You may test it, but loading an array from a range and working on the array is pretty fast, although reading info in a specific cell isn't that long but not really efficient. You can check the time spent by code by setting up a variable StartTime = Timer and at the end : MsgBox "Done in " & Timer - StartTime & " seconds"

R3uK Over a year ago

@ChrisUnbroken Glad I could help, if I can just ask that you upvote my answer, it would be nice (I'm working on my "Refiner" badge). Enjoy SO ;)

Christian Over a year ago

I would have done so before but I only just passed the 15 Reputation required to do so :) Thanks again

R3uK Over a year ago

I know I upvoted your 2 questions so that you can! ;) And that's why I poke you afterwards^^ Have a nice journey here! Thx for the vote! ;)

Collectives™ on Stack Overflow

Typecasting Variant Array into String or Boolean Array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related