Note: The following terms are often used (correctly or incorrectly) interchangeably to describe what is entered in the editor (VBE):-
proceedure, macro, routine, sub-routine, program, sub-program, function, code, script, VBA, Basic
Summary Of Commands
To get into the VBE use Tools->Macro->Visual Basic Editor (Alt+F11).
To get back to the spreadsheet use View->Microsoft Excel (Alt+F11).
To select any proceedure/macro in the Workbook from the spreadsheet use Tools->Macro->Macros (Alt+F8) then (typically) Run, Step or Edit.
To run a proceedure/macro in the VBE use Run->Run Macro (F5) or to step through one line at a time use Debug-Step (F8).
Write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it doesn't, check your spelling.
Always activate the 'Option Explicit'. Although, you are forced to declare variables, there are many advantages. If you have the wrong spelling for your variable, VBE will tell you. You are always sure that your variables are considered by VBE. You can use Shift/Space to call your variables in a contextual menu and double click them rather then key them in.
Declare all your variables (Dim) at the beginning of the procedure, it will simplify the testing of your code.
Remember to:-
Use readable and logical variable names to make your code 'self documenting'
Provide adequate on-sceen information to the user (also contributes to 'self commenting' code).
Add comments as you code to aid the reader what is being done and why.
Use indenting and white space to aid readability.
Use modularity. Besides being the only way you can perform any large task, keeping things in small chunks aids readability, fault finding and code reuse.
When I work with the Visual Basic Editor (VBE) I use three windows, the Project window, the Properties window and the Code window.
You can test a procedure line by line by clicking within the procedure in the Code Window of the VBE and using the F8 key. After a line of code containing a variable has been executed, you can check the value of this variable by moving the cursor on top of the name of the variable.
You can see your VBA code at work by opening both Excel and the Visual Basic Editor and then by right clicking on the task bar and splitting the screen.
To test part of a VBA procedure, start the execution with the F8 key. Then, click on the yellow arrow in the margin of the code window and drag it down to the line you want to test. Use the F8 key from there.
To test part of a procedure, you can also press F8 then right click in the margin of the code window in front of the line that you want to test and select 'Set next statement'. From then on, you use F8.
To create code within an event related to the sheet, right click on the sheet's tab and select 'View Code'. Then select the event from the event window and write your code.
You can carry the value of a variable from one procedure to the other by stocking this value in any cell in the workbook. eg:
In the first procedure: Range("A3456").Value=Variable1
In the second procedure: Variable2=Range("A3456").Value
Note: Your best VBA coach is the Macro Recorder (Tools/Macro/New Macro). Start it, do what you want your code to do and then go to the Visual Basic Editor (VBE) and cut and paste these segments of code. Personally, I use the Register a lot to avoid writing and spelling errors. I adapt the code that is offered by VBE.
Note: Quotes within quotes must be doubled. Hence,
Selection.Formula= "=Range ("A1").Value & "normal""must be written
Selection.Formula = "=Range(""A1"").Value & ""normal"""
Named Fields
Adopt the habit of naming the fields and cells that you use within your VBA procedures because if you point at a cell within your procedure and the location of that cell changes, your code is obsolete. If you name cells and ranges, even if they move up, down or sideways, VBA will always find them.
Name ALL the cells and ranges that you use in your code so that if their addresses change (adding or deleting columns or rows, changing the name of sheets, etc..), your code is not invalidated.
Note: If you want to name the fields that you use in SUMPRODUCT formulas, INDEX/MATCH formulas, and VBA procedures in Excel you select the pertinent cell or field and then you go to the Name Zone and type a name. Then, CLICK ENTER. Use prefixes (nf, general named fields; prf, fields used in procedures; etc..)
Procedure: A set of instruction that you want the computer to execute. For historical reasons (and it's quicker to write and say), the term 'Macro' is often used and both occur in the Excel documentation for example.
Objects: They are the building blocks of your Excel project; workbooks, worksheets, cells, charts, drawings, controls (buttons, text boxes, list boxes, etc.), VBA modules and forms.
Properties: Think of the verb to be. Objects have properties that you can change.
Methods: Think of the verb to do. You might want an object to close, to open, etc.
Events: For a procedure to be executed an event must happen. One event that everybody knows is the click on the button. Other events include opening a workbook, activating a sheet. When you program you use events to tell the computer when to execute a procedure.
Example:-
Sub Command125_Click ()
Range("A2").Value= 2
Application.Close
End Sub
In this example 'Sub' is the Procedure and 'Command125_Click' is the Event
'Range ("A2")' is an Object and 'Value' is a Property.
'Application' is an Object and 'Close' is a Method
This procedure tells the computer that when the user clicks on the command button 125, cell A2 takes a value of 2 and Excel is closed.
from http://www.engineering.usu.edu/cee/faculty/gurro/VBA&Excel.htm#Introduction
The Excel control toolbox is useful for adding command buttons and other VB controls to your spreadsheet. Do a right click on any empty space on the Excel top tool bar and select the Control Toolbox. [Excel control toolbox].
The Control Toolbox contains a Design Mode toggle button, a Properties button, and a Code button, as well as a number of controls (check button, command button, list box, combo box, toggle button, spin button, scroll bar, label, image, and additional controls).
Command buttons can be used to start a program from a worksheet. Place a command button by clicking on the command button icon in the Excel control toolbox. Place and dimension the command button within the worksheet of interest.
To modify the properties of the control button do a right-click on the button and select the Properties option. A properties window appears where you can modify the button's properties such as the name, caption, font, etc. [Command button properties window]
To add code to the control button double-click on the button. This will send you to the code area for the current worksheet in the VBA IDE. Type your code using Visual Basic.
If you want to assign a macro to a button, add the code Call Macro_name() to the button code. To get numerical data from the worksheet into your VBA code use functions Range or Cells. For example, to get the value of cell "B2" into variable 'a' in your VBA code use:
a = Range("B2").Value
Alternatively, you could use
a = Cells(2,2).Value
to load your data value.
To place data from your VBA code to the worksheet use the same functions Range and Cells, e.g.,
Range("C2").Value = r1
Cells(3,2).Value = r1
To place string data from your VBA code to the worksheet use, for example:
Range("M2") = "Int. = "
Cells(15,2) = "Int. = "
**There is more available at the above link.