VBA: Creating an array of zeros with length equal to selection length

Question

Merry Christmas all. I'm new to VBA and excel for that matter. I only know how to program in python. I'm trying to create a fairly long macro but am encountering a lot of problems. First, I want to create an array of zeros, which has length equal to the length of the selected cells. I thought this would do it (where the array is called "Split"):

Sub mymacro()

    Dim Split(Selection.Rows.Count) As Integer

End Sub

But I get the "constant value expected" error. How can I do it? Cheers

By "an array of zeros" I mean an array in which every element is a zero. — PickledNoob
– PickledNoob, Commented Dec 26, 2014 at 22:56

Doug Glancy · Accepted Answer · 2014-12-26 16:38:44Z

1

You can use a ReDim statement later in your code.

Sub mymacro()
Dim Split() As Integer
... 
'in the code body
ReDim Split(Selection.Rows.Count) As Integer
End Sub

Two general suggestions:

Don't use "Split" as a variable name. It's a VBA function name.
Don't declare variables as Integer. Use Long instead. In this case, it's actually required, since Integer can't handle the number of rows in a sheet.

answered Dec 26, 2014 at 16:38

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

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

Collectives™ on Stack Overflow

VBA: Creating an array of zeros with length equal to selection length

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related