Here is my intro to VBA for you- I had this all typed up, saved on my comp, ready to post in a blog, once I get one...
The programming language used in creating Excel macros is known as VBA (Visual Basic for Applications).
To open the macro editor / VBA editor, from Excel, press Alt-F11.
The building blocks of a macro are known as functions and procedures. Functions can do all the same things + more than what procedures can do, so I always use functions, unless a procedure is explicity required.
To get to where you can type in or paste in a general function or procedure, click Insert --> Module. To start a function, type the following line:
function myFunction
Then press Enter. It should automatically put () after your function name for you, and create an "End Function" line for you.
If you want or need to use a procedure instead of a function for any reason, instead of typing "function", type "sub"- like this-
sub myProcedure
and press Enter. Now you're ready to start coding in VBA. Now if you ever find any code in an internet search and try it out, you can copy and paste that code into your function or procedure (if it already has a "function ...()" or a "sub ...()" starting line then you don't need to type your own).
To execute a procedure or function, just click anywhere inside that procedure or function and then press F5.
The basics:
VBA is what is known as "object-oriented". That means there are several types of "objects" that you can use in your code, like a Workbook, a Worksheet, a Range, Cells, etc. An object can be of a certain object type, or "class", and can have certain "properties", which consist of "methods" or functions. You can store an instance of an object in a "variable" and then refer to it and it's properties (methods and functions) by that variable.
For example, Worksheets is a property of the ActiveWorkbook object, so the following code will return an "array" (a list) of all the worksheets in the currently active workbook, into a variable called "sheets":
set sheets = ActiveWorkbook.Worksheets
Before going on, make sure you understand- a lot of people will refer to what's inside an Excel file as a worksheet or Excel sheet, but what an Excel file really holds is actually called a workbook- and that workbook can hold many worksheets within it. A new workbook starts out with 3 worksheets. (Keep in mind that we developers sometimes speak a different language- a more precise language than many casual Excel users.)
There are actually specific ways you can refer to the default 3 sheets that a new workbook comes with- the following code returns an array of all cells in sheet 1-
set allCells = Sheet1.cells
Once you have a variable of an array, you can refer to one of the items in the array like this-
set myCell = allCells(1,2)
This will return the cell in the first row and 2nd column- the 1st # refers to row #, 2nd # refers to column #.
You can also refer to a certain cell by using the "range" function of a worksheet object. (a "cell" object is actually a specific type of a "range" object.) If you don't specify the worksheet object, the code will just use the currently active worksheet, like this :
set myCell = range("A2")
Once you have a reference to certain cell, you can use the "value" property to see or set the value inside it- this example is also introducing the debug.print method, which simply displays what you tell it to, into a certain place in your code editor, known as the "immediate" window :
debug.print range("A2").value
Notice you didn't have to set a variable to that cell first- you can use the value property right after the range function. We do this a lot to make our code more compact.
It was mentioned you can set the value of a cell with the value property as well- like this-
range("A2").value = "hello world"
It would be good for you to know that "value" is the default property of a range object when you're using it in context of doing something with the value. So you can actually do this-
range("A2") = "hello world"
Hopefully you're learning that writing computer code is a delicate thing- you have to be very particular. A computer is even worse than a human at reading your mind. The developers of VBA tried to make things easy for you in some things, but don't make any assumptions about how something works- look it up, try it out, and prove it. Never make unwarranted assumptions.
Here's a good time to tell you some of the most important things you need to know- Important Thing #1. you can use the "intellisense" of an object to see it's properties/methods/functions - for example, in your code editor, type "range." and you should see a drop-down appear of all the properties of a range object.
Important Thing #2. After you have typed or selected any object / method / function, you can (and should) press F1 to get help on how to use it.
Important Thing #3: Please don't be afraid to research and use trial and error to find out if you're understanding correctly what you learn about how things work. You usually won't be able to break something beyond repair- but do make sure that you make backups of your workbooks or data before experimenting.
From here you really should be able to teach yourself just about everything else you need to know by using the intellisense and the help topics. But here are a few more hints of important things that you should search for in the help topics :
loops (There are different types of loops you can learn about, with advantages & disadvantages to each)
"if" statement (conditional statement)
range.offset
range.end
range.find, range.findnext
mid, left, val (functions)
For your added convenience (aren't I a nice guy), here are a few more important things so you don't have to dig around in the help topics so much:
Here is the syntax of 4 different ways of using the "if" statement (syntax just means the specific format and way that you have to use it):
method #1:
if (condition) then (statement)
method #2:
if (condition) then (statement) else (statement)
method #3:
if (condition) then
(statement)
end if
way #4:
if (condition) then
(statement)
else
(statement)
end if
Here is my favorite way of doing a loop:
do
(statements)
if (break loop condition) then exit do
(statements)
loop
EVENTS
Events are another very important thing to understand- Excel calls certain procedures that are named a certain way whenever certain events are called. Example- if you want certain code to run whenever someone changes something on sheet 1- put that code inside a procedure named Worksheet_Change- but your "sub" line starting the procedure has to look like this-
Private Sub Worksheet_Change(ByVal Target As Range)
(Actually it doesn't necessarily have to be private, but you can learn more about that later)
The part inside the parentheses is a declaration of "parameters" that the procedure can use. There's just one parameter here- named Target. The "ByVal" means the parameter is passed "by value"- again, you can learn more about that later- not real important right now. the "As Range" is telling you the data type of the parameter variable- Range. For this procedure, this parameter tells you which cell, or range or cells, was changed. So your code can check what cell it was and/or what value it changed to, and take action based on that.
