CODEWITHSUNDEEP
arraysvbaexcelrange
working on it...
working on it...

Reputation: 28

Range and arrays in VBA converting whenever they want. What's going on?

The Range object in VBA is very strange. Here's my practice code:

a = ActiveSheet.Range("A1:A3").Columns.Count
b = ActiveSheet.Range("A1:A3")
c = b.Columns.Count

How is it possible for a to call the range object and b to be an array? I need a range object to pass into my Functions! *I've tried declaring b as a range and range(), this just gives me an error.

Upvotes: 0

Views: 74

Answers (1)

user2858650
user2858650

Reputation:

You need to explicitly define your variables if you want the compiler to translate appropriately. Right now it is just guessing at what you are trying to do because you haven't spelled it out.

Dim a as Integer
Dim b as Range
Dim c as Integer

a = ...
Set b = ...
c = ...

I'm not positive but I think your variables are coming across as Variants which you really want to avoid as this will cause you major headaches down the road.

I would highly recommend setting Option Explicit on any code you plan to write going forward so the compiler will complain if you try and reference a variable that has not been explicitly defined. You should be able to set your IDE to default to this on any new module by playing around with the Options menu.

--Edit--

Please note that only objects require the Set syntax while primitive datatypes do not. It's a good rule of thumb to explicitly define both in your dimensions though.

Upvotes: 4

Related Questions