CODEWITHSUNDEEP
variablesexcelexcel-2013vba
Tarik
Tarik

Reputation: 77

defining lots of different variables

Is there a faster way to define the following variables. notAct is the name, after the name comes the cells positions. The "b" is for the column and the number refers to the row. The value is 0. Very single cell has to be defined so that I can use the variables.

Dim notAct_b1, notAct_c1, notAct_d1, ..., notAct_d20 As Integer

    notAct_b1 = 0
    notAct_c1 = 0
    notAct_d1 = 0
    notAct_b2 = 0
    notAct_c2 = 0
    notAct_d2 = 0
    notAct_b3 = 0
    notAct_c3 = 0
    notAct_d3 = 0
    notAct_b4 = 0
    notAct_c4 = 0
    notAct_d4 = 0
    notAct_b5 = 0
    notAct_c5 = 0
    notAct_d5 = 0
    notAct_b6 = 0
    notAct_c6 = 0
    notAct_d6 = 0
    notAct_b7 = 0
    notAct_c7 = 0
    notAct_d7 = 0
    notAct_b8 = 0
    notAct_c8 = 0
    notAct_d8 = 0
    notAct_b9 = 0
    notAct_c9 = 0
    notAct_d9 = 0
    notAct_b10 = 0
    notAct_c10 = 0
    notAct_d10 = 0
    notAct_b11 = 0
    notAct_c11 = 0
    notAct_d11 = 0
    notAct_b12 = 0
    notAct_c12 = 0
    notAct_d12 = 0
    notAct_b13 = 0
    notAct_c13 = 0
    notAct_d13 = 0
    notAct_b14 = 0
    notAct_c14 = 0
    notAct_d14 = 0
    notAct_b15 = 0
    notAct_c15 = 0
    notAct_d15 = 0
    notAct_b16 = 0
    notAct_c16 = 0
    notAct_d16 = 0
    notAct_b17 = 0
    notAct_c17 = 0
    notAct_d17 = 0
    notAct_b18 = 0
    notAct_c18 = 0
    notAct_d18 = 0
    notAct_b19 = 0
    notAct_c19 = 0
    notAct_d19 = 0
    notAct_b20 = 0
    notAct_c20 = 0
    notAct_d20 = 0

Upvotes: 0

Views: 55

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

First of all, you don't need to initialize an Integer variable in VBA - it will be 0 after it is Dimmed.

However, instead of defining 4x20 variables, better use an array:

Dim notAct(1 to 20,1 to 43) As Integer

If you need to initialize or reset all values, you can use a small loop:

Dim col As Integer, row As Long

'Initialize/reset
For col = 1 To 4
    For row = 1 To 20
        notAct(row, col) = 0 'setting 0 not required after Dim
    Next row
Next col

If you need to assign the value of a variable to a cell, use this syntax:

'Assigns a value from the array to B3
Cells(3, 2).Value = notAct(3, 2)

And best of all, if you want to assign all 4x20 cells with their values, use this one line of code:

Range("A1:D20").Value = notAct

Upvotes: 1

Related Questions