In this video we're going to cover how to pull data or information from your Excel file into your VBA
code and how to write data from your VBA code back into your Excel file.
And there are a few different methods we can use to accomplish this reading and writing of data and
they all operate based on a specific worksheet.
What that means is that if we follow through our object hierarchy here we should start with our application
which is Excel moved to our workbook which in this case is example and then select our sheet.
In this case will mainly be working with data sheet down here and then we can start to interact with
the specific data that we want to touch.
And as we start to identify information for our code or write information back into the excel file it
becomes really important that we've specified each step of the object hierarchy.
It's important to ensure that VBA knows the specific cell on the specific sheet in the specific workbook
file that we're dealing with.
Otherwise VBA will default to assuming you're looking at the cell on the active worksheet which may
or may not be the correct worksheet especially if your B VBA code is bouncing across several different
workbooks or several different worksheets in order to work properly.
So the first thing we're going to look at is this cell's method of writing data the cells method is
something we've actually seen a couple of times now when we created example macros before.
So now we can get a bit more detailed on the cells method of data.
Cells are of course the individual cells on worksheets so we identify them by giving their row number
and then their column number kind of like coordinate system.
So if you come back to our data sheet here if I'm interested in referencing let's say Cell C to this
would be wrote to in column 3 1 2 3 so I would reference the cell by saying cells 2 comma 3 and that
would represent this highlighted cell we see on the screen here.
So now we're gonna go ahead and open up Visual Basic.
I go into my developer tab and then going over to Visual Basic and we can see I have some previous modules
and previous code that I've written in here.
So I'm going to go ahead and close out of this open module and insert a new one
and we'll create a new public sub.
We'll call this data testing
and now I'm going to go ahead and type cells to three dot value.
So again this dot here this little period means we've identified what we're working with and we're ready
to move further into our object hierarchy.
In this case I'm interested in what the value is of cell to 3 what's written in that cell.
What data lives there right now.
And we're actually going to overwrite that value with our own value by setting it equal to something
like this is Cell C to like so now if I go ahead and shrink these windows like so and I come over here
click into my sub and I click Run we can see myself see two was overwritten with exactly what I told
the VBA to write in there.
This is cell C to now we can actually reverse this logic and pull data out of our excel file and into
our VBA code using the same cells method.
So if we create a variable here and we'll say I just I'll just create this little e x variable for example
and I'll say that's a string I can set E X equal to cells to three value and hit enter.
And now when I run this the variable E X should actually pick up what value is in C to at the time that
we run this.
So I'm going to go ahead and output that in a little message box and we'll just message box directly
what that variable is.
So I'll go ahead and run this code and we see we get a little message box that pops up and says This
is cell C 2 which is exactly what we would expect seeing that we assigned the value of the cells to
three to our variable X. We'll go ahead and give us a little bit more space here.
Now the cells method of interaction is really helpful for identifying specific cells.
And we use a lot in loops and if statements.
So this is probably the most common interaction type you'll use for specific data in VBA.
And there are other things you can actually do with cells like select to select a cell or we can do
things like clearing contents or changing the font around.
So for example let's add a few lines and we'll keep working with cells 2 3 which is Cell C.
This time we'll put dot font and now we know we're looking at the font properties of Cell C too and
specifically we want to look at something like the size of that font and you can find out what all of
these properties are through the object library or through Microsoft's help site but they're generally
everything that you would expect to be able to change with the font given cell.
So we'll go ahead and set this equal to something like 14 so that we can see a change now let's also
go ahead and we will clear the contents of that cell and then last.
We will go ahead and change its value to be something different.
We'll say new cell value something like this and we'll go ahead and click Run we can see our X variables
still reading this is Cell C too because that's what's typed into the cell right now or click OK and
now we can see our font size has gotten a lot bigger.
It's changed to this new cell value here that we had we didn't need to clear the contents if we didn't
want to we could have actually just skipped this line entirely and just rewritten the value to be what
we wanted it to be the clear contents can be helpful if you're trying to clear big ranges of data or
anything like that on your file so we can change the font size we can change the font style we can make
the color of a cell different we can resize the cell and all of those are really straightforward properties
but when we use cells specifically we're referencing a single cell on that sheet.
And this kind of begs the question what do we do if we went to mass reformat the cells we don't want
to have to go cell by cell.
So is there a way that we can mass change the properties of a range on a sheet and that is exactly what
the range method of interaction is for the range method.
Well you provide a start cell and an end cell and then let you interact with that entire range that
you had selected.
You can also use range to interact with a singular cell but most of the time you use range for interaction
on a large scale and the cells method for interaction of a singular cell range you can also use named
ranges in your Excel file so on my datasheet here you can see I have a list of fruit in column J apples
through melons and I can convert this into a named range by going to the formula tab clicking on name
manager here and clicking on new.
Now if you haven't used name ranges before they're sort of like our variables in VBA.
You can see here we have a chance to name them.
So I'll name this something like fruit and then we can point to the name fruit in a certain cell or
range of cells on my file.
And in this case it's my my range of fruit here from J to to J 8 which we can see has already been selected.
So we'll go ahead and click.
Okay and now we can see it appears in our name manager as the fruit name applied to this particular
data range that we have selected now anytime I referenced the name fruit if it's in a formula or my
Excel file if it's in a range in my VBA code Excel we'll be able to tie it back to this range of cells
here.
So as an example if we come back over into our code give ourselves some room we can type range and now
in quotes I'm going to type J to colon which means through J 8 and quotes close parentheses.
Now this range we're giving it the start so we're giving it the end cell and we're saying we want you
to select everything in between these two cells.
That's what the colon stays stands for.
So j 2 through J 8 and we've put that in quotes.
Now we're going to type that font.
We'll get into the font properties of this particular range and we'll go ahead and mess with the bold
properties and we'll set that equal to true which means it will show as bold.
So now instead of having to go step by step cell by cell through this list we're able to do it all at
once by saying range J to to J.
So this line references the range by its cells.
So alternatively we could use our named range.
So in this case we'll type range and instead of typing J to J 8 we'll type fruit and this time let's
change the color so we'll say interior dot color index and we'll pick a number like 37 which should
be a light blue and you can look up the color index list on line.
Alternatively if you want to reference a more specific color if you want to use for example our G.B.
inputs you can do that as well.
So we'll type range fruit dot interior dot color and instead of saying color index this time we're just
going to say color equals we'll type RG beat to make it clear that we're doing a red green blue input
open parentheses and we'll type one hundred two hundred and two hundred and fifty there should be kind
of a light bright colored blue but if you ever want to know exactly what your input is going to look
like if you click back into Excel go to the Home tab under the back color dropdown you can choose more
colors at the bottom here go to the custom tab instead of the standard tab and you can pick things in
the standard tab if you want to we could pick kind of a lighter green or something but you can go into
the custom tab and you can see this lighter green.
This is the RG B code for that particular light green color we could also overwrite these directly with
our inputs.
In this case we said one hundred two hundred two hundred fifty five.
So we can see this is that blue color that we're gonna get out of this particular the input so we'll
go and close out of this and I'm going to step through this line by line so we can see what each of
these does.
So I will go ahead and you can either use debug step into or you can just hit F eight on your keyboard
which is what I'll do so the first thing we should see is our cell to three value which is Cell C two
should turn into this is Cell C to something to keep hitting f eight
and now we should be pulling our x variable which should be equal to what is currently in Cell C 2 F
eight one more time we should see that pop up in our message box and this looks right.
So we'll click OK now cells C 2 should change its font size be fourteen it's already fourteen because
we've already run this so we shouldn't see any change after this time we should see the contents of
cell C to disappear and we'll rewrite them to say new cell value now we should be on this line changing
the range of J to to J eight to be bold and that looks like it's worked properly now on this line we're
referencing named range of fruit which is our range J to to J 8 and we should be changing the interior
color of that to be index number thirty seven which is a light blue color so we'll hit eight and we
can see that's behaved as expected and if we had a fake one more time we can just see that the RG B
method works just as well so we'll hit F eight and we can see our blue changed colors just a little
bit so there's two different methods of referencing those colors we'll head effort one more time to
end our subroutine so these are gonna be our main methods of interacting with data through VBA this
range and cell method and we talked a little bit about the difference between the two and how we can
use them moving forward.