excel vba - redefining a 2 dimensional array -

i'm kind of struggling of redefining array. have simplified code, see below:

sub knop1_klikken() 'all cells defined in array dim col, integer, defrow long dim notact(1 20, 1 43) integer  col = 2 4     row = 1 20         notact(row, kol) = 0 'setting 0 not required after dim         cells(row, kol).value = notact(row, kol)     next row next col 'a loop if-statement see if specifica x = 1 100     if notact(2, 2) = 0              = + 1              if = 10                 notact(2, 2) = 1              end if     end if next x  end sub 

the thing wanted redim array. tried after for-loop next x

msgbox notact(2, 2) 'it returns 0 redim notact(2, 2) msgbox notact(2, 2) 'it returns 0 

it vba doesn't appreciate redim notact(2, 2). there way redim working in 2d-array. want return value when notact(2, 2) gets 1.

you need dim array without dimensions first:

dim notact() integer 

the can redimension it:

redim notact(1 20, 1 43) integer 

note redimensioning array cause lose data stored. preserve data, need use preserve keyword:

redim preserve notact(1 20, 1 2) integer 

note when using preserve keyword can change last dimension's bounds.