Summary: Using Excel and Excel/VBA for Data analysis and Lists

Following is a brief list of notes from class:

  1. Use Tools / Macros / VBE editor or Alt + F11 to get to the VBE editor
  2. Use F8 to step through your code and each line is highlighted in yellow
  3. Use F5 to run the macro or click the "run" button in the toolbar..
  4. Use F9 or click in the gray area to the left to put in a break point (which is a brown circle) and then use F5 to run from the top to the breakpoint.
  5. Use Ctrol/Break or the “reset” button on the toolbar (small square) to reset or stop in the middle.
  6. To put more routines in the same module, just type in Sub again following by the procedure name and VBE will put in a solid line between them.
  7. The commands end at the end of a line; no need for semi-colons, etc.!
  8. Use F1 for help; VBA help is not part of the standard installation but must be added on.
  9. comments: any text that follows an apostrophe
  10. line continuation: [space]_[space]

To run a macro:
1. Tools / Macro / Macros / Run
2. The macro runs automatically when the worksheet opens
3. The user clicks on a button on the worksheet.
4. The user clicks a button on a new or existing toolbar.
5. The user selects an item on a new or existing menu.

To distribute VBA code (as an Excel application, for example):
1. within the application (the XLS, MDB, PPT file, etc)
2. set up a template for the users which contains the macros
3. store the code in a hidden workbook in the startup directory
4. store the code in an Excel add-in

Data Types:

Data Types Declaration Suffix Data Type Values
Boolean   True/False
Integer % integers -32,768 - +32,767
long & long integers
single ! single-precision real numbers
double # double-precision real numbers
currency @  
date   1/1/100 - 12/31/9999
object   any object reference
string $ note: variable and fixed length strings are allowed
user-defined   varies

Variable Declaration Example:
Dim Name$
Name$ = “Deena”

Dim Name as String
Dim Holiday as Date, etc.

Examples of Excel object datatypes: Filter, Font, PageSetup, Range, Sheets, Window, Workbook, Worksheet, etc.

String operators

  1. concatenation: &
  2. Logical "and": AND
  3. Logical "or": OR
  4. Logical "not": NOT

Comparison Relations

equal =
less than <
greater than >
less than or equal to <=
greater than or equal to >=
not equal to <>