defining lots of different variables

Question

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

One important thing relating to your code: when your Dim variable like that: `Dim notAct_b1, notAct_c1, notAct_d1 as Integer` only the last variable will be Integer type. Properly you should write: `Dim notAct_b1 as Integer, notAct_c1 as Integer` and so on. — lowak, Aug 18 '14 at 08:13

Peter Albert · Accepted Answer · 2014-08-18T21:51:41.540

1

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

edited Aug 18 '14 at 21:51

answered Aug 18 '14 at 06:23

Peter Albert

16,917
5
64
88

1

Thanks your code works. Could you please explain how it is possible that B2 = 3,2 and not B2= 2,2. If so what is C2 and D2? – Tarik Aug 18 '14 at 17:29
Sorry, my (double) bad. Range(3, 2) is B3 - 3rd row, second column! :-) – Peter Albert Aug 18 '14 at 21:52

defining lots of different variables

1 Answers1