In this screencast I am going to cover a couple of different ways in which excel and VBA interface
and interact with one another. There are a couple of different ways to reference excel
cells in VBA. You can use range which basically refers to whatever values is in cell "A5".
The value property here is actually optional. If you leave it off and just do range of "A5"
it just means that it is the value. We could do a range of "A1" to "C3" which just corresponds
to the 3 by 3 array. You can also reference entire columns and entire rows with the range
shown. Another way to refer to cells in excel is you can use Cells with the numbers. "Activecell.value"
is another way that you can refer to cells and that just refers to whatever value is
in the cell that is currently the active cell. In combination with all of these methods above
you can use the offset method. For example we can name a range temp in excel and I can
refer to range Temp and then offset it (1,1) which means one row down and one column over.
As far as importing data from excel to VBA there is a coupe of ways to do this. First
we can assign a value "a" which will be a local variable in VBA. We are assigning the
value in cells (2,2), that would be cell "B2", to the local variable "a". We can do something
similar where we say the active cell value is going to be placed into "x". Finally we
can say something like the range of a single cell or a range of cells is equal to some
variable called something like "b". Let me show you how we might do this. I have some
values here. I can press Alt and "F11" to open up the editor and I have a couple of
different subs here. There are a couple of things that I want to point out. At the beginning
of any module you always want to start with "Option Explicit". It is optional but it is
a good thing to do because that requires you to define all of the variables that you use
which makes trouble shooting and debugging a lot easier. Then I am "dimming" which is
basically declaring "a", "b" and "c". This first sub is called "GetValue" and it is taking
the value in cell A5 and setting that equal to the local variable of "a". If I did not
have this next line then we would not be doing much because VBA would not be using that value
of "a" and nothing exciting would be happening so I have added in this message box which
says "Value in A5 is " and then it will display the value in "a". One of the best ways to
run a macro and to really see what is going on is to run it step wise and you can step
through a function using "F8". When I go through and press "F8" on this line now I can go down
to something called the locals window down here (it is a good idea to have that opened
up and enabled) and I can click on the module and you can see that we have assigned a value
of 17 to the variable "a". Then I can press "F8" again. That message box is displayed
and we see that the value in "A5" is 17. That is exactly what we wanted to do. Then I will
press "F8" just to finish that sub. That is one way that we can get a value. Usually you
will not have a message box displaying it but that is how you can import something into
VBA. Some other ways using arrays is I have this sub where I am saying that "a" is equal
to the range of "A1" to "C3". I can step through this and it is going to set that array equal
to "a" which is going to be a 3 by 3 array. In this case I am going to step through it
and initiate that line of code. I go down here and I can open up that locals window
and you can see that we have "A1", "A2" and "A3 which correspond to the different rows
of "A". Another way we can do this is to set a selection to the value "b". I step through
this with "F8". We can see that it picked up "b" which is now going to be a 2 by 2 array
defined by my selection. The final example here for actually getting something from excel
is I have this sub called "GetArrayPutArray" and "c" is equal to the range of "A1" to "C3".
It is going to take in the 3 by 3 array and then I want range "D5" to "F7" to be the value
of "c". I am going to just take "c" from "A1" to "C3" and place it back into "D5" to "F7".
This is just a copy and paste. Instead of step wise going through this sub you can run
the sub here using play and if I go back to excel you can see that it took the cells and
moved them. Now let's talk about how we can use VBA to modify the content of cells. There
is a couple of different ways to do this. It is the flip of what we went over in order
to import something you just flip the order. Instead of saying that "c = Cells(2,2).Value"
you say "Cells(2,2).Value = c". Somewhere earlier you would have to define what "c"
is and now you are saying cells (2,2) which is "B2" is equal to that value. Similarly
we can say that the active cell value is equal to "y". You can say range of "B3" to "D6"
is equal to 23 which will place 23 in that whole range. Similarly selection which is
the selection that is highlighted in excel and set all of those cells to the value of
10. I have an example here. I have a "PutValue" sub which says that "b" is equal to 25 to
define "b" locally. Now I am saying that the range of "A6" is equal to the value of "b".
When I run this I can run it using play. The final sub is more complicated. We have "c"
defined as an input box. An input box will open and ask the user to "Enter a number to
be placed in cell A7: ". There is a statement put into VBA called "ForEach" which will actually
iterate through a bunch of values in an array. I have it set up here that for each "c" in
the worksheet of range "A1" to "C3" we are going to take the value in the cell and add
5 to it. What we are going is taking the old values, adding 5 and assigning the new value.
I will show a variation on this theme by putting 5 in a couple of these boxes. Instead of using
a range I can actually refer to a highlighted region. Now this second sub here has this
"if" statement. I will iterate through all of the cells in the selection. If that value
in the cells is equal to 5 then the new value is going to be 9999. I am going to go in here
and insert a button and assign this button to the "ForEachIf" sub. When I click this
I have to select my selection and when I click the button you will see that we will replace
any 5 and only the 5 with 9999. I hope this screencast has been helpful. We have shown
how you can go back and forth between excel and VBA and some nice ways to take data from
excel and maybe do something with it.