Array Definition in vba-excel

Question

I got in trouble in a part of my code. I have an variable that changes by the the number of filled rows.I have defined an array from 0 to that number but I receive "Constant Expression Required" Error. I would be thankful if anyone could help me.

 Sub test()
   Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
   Dim f(0 To Lastrow) As Double
 End Sub

grominet · Accepted Answer · 2015-05-31 09:48:36Z

1

You can't dim an array with variable, just constant

You need dim the array before, and redim it to your variable. With redim:

Sub test()
   Dim f() As Double
   Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
   Redim f(0 To Lastrow)
End Sub

With high constant (a bad way)

 Sub test()
       Dim f(35000) As Double
       Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    End Sub

answered May 31, 2015 at 9:48

grominet

2721 gold badge3 silver badges13 bronze badges

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

Comments

Lefty · Accepted Answer · 2015-05-31 09:18:07Z

0

I could be wrong but I don't think you can dim using a variable. You might be able to redim..?

answered May 31, 2015 at 9:18

Lefty

4013 silver badges15 bronze badges

1 Comment

Neyram Over a year ago

Would you please tell me how I can do that?

snb · Accepted Answer · 2015-05-31 14:30:46Z

0

If you use

sn=cells(1).currentregion

You don't need a 'last row' variable.

answered May 31, 2015 at 14:30

snb

3431 silver badge4 bronze badges

Collectives™ on Stack Overflow

Array Definition in vba-excel

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related