declare

Excel VBA Arrays: Practical Example of a 2 dimensional array to create a New Workbook



Sharing buttons:

in this tutorial I'm going to show you

how you can create a VBA routine that's

gonna create a customized workbook the

customization that we're gonna make is

this we want to create a new workbook

that contains a worksheet for each

company so we have the list of companies

here each of the worksheets in this new

workbook should get a name from here

then inside each of these worksheets we

want to populate cell a1 to equal the

company name and cell a2 should be the

manager's name here we can make good use

of arrays we can keep this information

in a two dimensional array because we're

dealing with rows and columns so rows

will be one dimension and columns will

be the second dimension so once we store

this in the array it's really easy to

reuse that information okay so let's see

how we can set that up let's bring up

the visual basic editor also 11 I've

already created an empty module called

project activity inside this module

let's create a new sub called manager

report okay let me collapse this first

off lets them our array I'm gonna dim

comp info ok we'll call it comp info

it's gonna have two dimensions right so

one is gonna be four rows and the other

one four columns so in this case let's

assume that our companies are fixed I'm

going to use a two-dimensional fixed

array and say one to eight and the

columns are one to two my lower bound is

one that's an easier way of working with

rows and columns right so if we leave

out the one and just put in the upper

bound the lower bound is automatically

going to be zero okay so that's one

thing we need second we're going to be

looping through the rows here and the

columns here

so let's dim them as well so I'm going

to dim our for the rose as well since we

don't have that many companies and we're

not gonna have over like 200 companies

I'm gonna do this as buy it and I'm also

going to dim the column as buy it since

it's only two columns here now we can

start filling up our array let's create

our loop we say for first is the Rose so

4 are equal 1 to 8

and it's still next arm okay so we're

going to go through each row but the

moment we're in the first row we also

need to loop through the column so we

need to do column 1 column 2 then it's

for C equals 1 to 2 and let's do next

see here now we want to fill up our

array so this one with the values of

this table right because right now our

array our box is empty right we need to

fill that up so we're gonna say comp

info now we need to decide on the row

and column since we're looping through

them I'm going to do R and C this equals

cells now is this also R and C dot value

well it would have been Orange C dot

value if my data range here started from

a 1 right then I could leave it like

this but I can't leave our like this

because our actually starts from 7 so I

need to make an adjustment but see I can

leave like this right because it's I

mean column 1 and 2 just like my array 1

& 2

for our I need to move them by six I

could do plus six and to make this

easier to read

I could declare a constant for this on

top so let's do constant start row as

now this would also be byte in this case

but if our entire data set is like

somewhere on the bottom of our sheets I

just want to be sure we don't run into

problems so let me do as long here and

it equals six since it's a constant

we're going to define it on top this one

I can remove and replace with start room

okay so that's it right this way I'm

gonna fill up the array let's just bring

up the locals window that's a good one

to look at your array after it gets

filled let's run this but let me stop it

before it leaves the loop and empties

the array so let me press play here and

see if everything gets populated

properly so this is each of my

dimensions in my array let's open that

so calm painful one one that's jellyfish

one two so Row one two in the table

that's this one and let's look at the

last one as well each one is pairs and H

two is that okay so my array here has

been filled

now with the contents of this table okay

so let's stop this and get rid of the

breakpoints there

so we fill up the array now we want to

create a new workbook and then inside

that work will create new sheets for

each of these companies we can also

declare variables for them I'm going to

say dim new book as a workbook and dim

let's call it sheet new as H nu as a

worksheet let's move down

and after this loop we're gonna create a

new workbook so let's set our new book

equals workbooks dot add now we want to

loop through this workbook and add new

sheets to it so I'm actually going to

reuse our four worksheets here as well

right because that R is my rows here is

eight so I'm gonna have eight worksheets

as well I can create a new loop here and

say for R equals one to eight let's do

next arm now here I'm gonna create a new

sheet every time the loop runs a new

sheet is created so I'm gonna set s H nu

equals my new book the worksheets dot

add

yes I'm creating a new sheet for each of

these here then we need to assign the

name of the sheets to be equal to the

company name we're gonna do SH new dot

name equals here we can refer to our

filled box our filled array so that's

going to become info now what do I have

to select here what's the row

it's the are right so if it's in number

one it's gonna be jellyfish if it's

number two is gonna be - ring what about

the column that's going to be fixed

right because my company name is in

column one then I can also do SH new dot

now I want to write in cell a1 so I'm

going to do that range a one dot value

equals it come in fo the same thing

right it's going to be r1 and now the

next one well here I could have actually

used a width and end with statement

I can do range a 2 dot value equals what

should this one be now r2 right because

I want to put the manager name in cell

a2 that's it we close the loop there and

if everything goes well we should get a

new workbook with this information

filled let's test that let's add also a

button for this manage your report and

let's call this create reports okay

let's test

we got a new workbook okay it's called

book one we have let me just bring this

up so that you can see the different

tabs so we have one tab per company we

have the company name and we have the

manager name in front of it right here

okay so that's how you can use a raise

in your code you can fill up these

arrays and reuse them to create such

reports this video was brought to you by

unlock Excel VBA and macros course where

you're gonna learn how to simplify your

work and have to save time by automating

complex and routine tasks we're gonna go

from beginner all the way to advanced

for more information check out the

description of this video or go to

XEL plus.com slash courses