Excel VBA - Dynamic Array

Sharing buttons:

in this video we'll see dynamic erase

that is one dimensional dynamic erase

till now we have seen all these static

erase now let us see what is dynamic

arrays so I'll go in the sheet the file

name is Excel VBA miscellaneous and I'll

go to this Developer tab click on this

Visual Basic now I have created a module

named as dynamic one dimensional arrays

in this place I have written some code

here now when you're not sure about what

should be the size of your array that

time you can leave it empty now for

example you don't know how many data you

have to take how many data you have to

accept so that time what you can do is

if you know this one is the simple

static array dim a of 2's integer and

the data is same there's no change in

this I have assigned some values to the

array variables here if you see a of 0 1

2 and I've displayed the values a of 0 a

of 2 a of 1 that's it now if I'm not

sure how much data should be occupied in

that array that time I can remove this

too like this ok I can remove this too

and I can define another size of this

array here by just putting as 3

dimension a of 2 so this is nothing but

3 dimension a of 2 now the sizeof array

will be become as 3 so a of 0 a of 1 a

of 2 still it will work fine now let us

see if I execute it step by step like

this if I execute it now a of 2 it will

work fine a of 0 is 1 a of 1 should be

20 because a of 1 is 20 and a of 3

should be given as a of 2 should be 30

if I execute 30 which is working fine

and I'll just come out of the sub

procedure so when you're not sure about

the dimension of the array that time you

can just leave it empty for the first

time and then you can define as reading

a of 2 now suppose after sometime after

fifth rule I know of code again if you

think the size should be increased then

you can go here like this and you can

say reading a of four okay reading a or

for again the dimension of a will be

increased array of this one size will be

increased and I'll press ENTER but if

you observe there will be one change

what is the change I will show you if I

just exit

step-by-step like this and now what

should be the value of a of zero it

should be ten now ten twenty and thirty

this three values should be displayed

here according to rule here if I just

execute now if you see that is given as

zero and again the value will be given

as zero again the value will be given as

zero and I'll just come out of the loop

now I'll tell you the reason why it is

showing a value of zero when you

redefine a value when you redefine an

array by using this reading a of

whatever the value is given here the

values which sure earlier in that array

will be white wiped out there will be no

values in that particular array so the

values will start from the scratch

itself now what should be done if you

want the previous values so if you want

the previous values also you can just

make it as reserved the keyword is just

preserved that means whatever values you

had earlier to that it will be still

there and you can expand to next one

more or you can says next to of values

also so if I see if I just click on the

step in to now if you observe it will

preserve the earlier values also and it

will increase the limit itself again if

I say now you see the value is ten and

20 and again step into 30 so it is

working fine I'll just come out of it

right so simple method leave the

dimension you don't define the dimension

just say yes declare a variable don't

declare the size and then you can

redefine it reading a of two because it

saw for the first time there are no

values here now the next time when you

want to define and if you want to keep

the existing values you can just use the

keyword as reading preserve preserve is

the keyword and then you can use this a

or four and keep on working on it right

so I hope you understood what is re

dimension how what is dynamic arrays and

how do we use it and what is the method

what is the use of this preserve keyword

that's all for this video