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

dmadhup

Board Regular
Joined
Feb 21, 2018
Messages
95
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

New Member
Joined
Aug 22, 2018
Messages
42
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

Board Regular
Joined
Feb 21, 2018
Messages
95
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

New Member
Joined
Aug 22, 2018
Messages
42
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

Board Regular
Joined
Feb 21, 2018
Messages
95
I can see the difference.
I am using a large set of variables to sum the columns.

Anyway thanks....
 

RileyC

New Member
Joined
Aug 22, 2018
Messages
42
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

Board Regular
Joined
Feb 21, 2018
Messages
95
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
 
Last edited:

Forum statistics

Threads
1,082,344
Messages
5,364,803
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top