How to define large set of variables as Long in VBA?

[dmadhup]

Hello,

I am looking to define an efficient way to define these number of variables.

Dim sumCol31 As Long, sumCol32 As Long, sumCol33 As Long, sumCol34 As Long, sumCol35 As Long, sumCol36 As Long, sumCol37 As Long, sumCol38 As Long
Dim sumCol39 As Long, sumCol40 As Long, sumCol41 As Long, sumCol42 As Long, sumCol43 As Long, sumCol44 As Long, sumCol45 As Long, sumCol46 As Long

Is there any way to optimize this?

Your help will be highly appreciated.

Thank you

 

[RileyC]

Dim sumCol() as long
ReDim sumCol(31 to 46)
Now you can do sumCol(31) to sumCol(46) to enter in the sum to.

Look up arrays in vba if you want more info.

 

[dmadhup]

Thank you RileyC for the prompt response.
So, I can use an array to define the variable. It works and I can use sumCom31 also instead of sumCol(31).

Looks like no need to put parenthesis.

 

[RileyC]

Oh sorry you can't define the variable that way. But why would you want a huge list of variables when you can just use an array? If you wanted to do a list of variables you could just copy and paste an incrementation of them but an array is much cleaner code.

 

[dmadhup]

I can see the difference.
I am using a large set of variables to sum the columns.

Anyway thanks....

 

[RileyC]

You can use the array to sum the columns! You simply iterate through it and add each together (sum = sum + array value). It'll be much simpler than what you're doing right now.

 

[dmadhup]

Actually, I have more than one columns to sum. For example:

For jj = lastRow To 14 Step -1
If arr(jj, 1) = 1 Then
Cells(jj, 21) = sumColumn(21) + Cells(jj, 21).Value
sumColumn(21) = 0
Cells(jj, 22) = sumColumn(22) + Cells(jj, 22).Value
sumColumn(22) = 0
Cells(jj, 23) = sumColumn(23) + Cells(jj, 23).Value
sumColumn(23) = 0
Cells(jj, 24) = sumColumn(24) + Cells(jj, 24).Value
sumColumn(24) = 0
Cells(jj, 25) = sumColumn(25) + Cells(jj, 25).Value
sumColumn(25) = 0
Next jj