“If you think you are too small to make a difference, try sleeping with a mosquito.” [Dalai Lama]

Abstract

Excel VBA is a programming language. You can write programs to enhance Excel standard worksheet functionality or to repeat specified actions, for example.

As with learning in general, you might want to look around for all possible material you can get hold of, sort/order/structure it to your needs and filter/focus on parts you profit from most. Some nice points to start at:

  • (External link!) VBTutor (detailed)

  • (External link!) TechBookReport (simple, short)

  • Enter VBA Editor by pressing ALT + F11

  • VBA Project Browser: Change module name with F4 but do not choose a name of a function or a sub

  • Record a macro. You will get ‘spaghetti code’ but it’s an easy way to look for a function or for a command you don’t know or you can’t recall. Do not forget to clean this up!

My Second Program

Copy and steal good code wherever you can! Your first program should be just a copy, I suggest.

Good sites to look for code:

(External link!) Pearson Software Consulting

(External link!) Erlandsen Data Consulting - A good one for import/export of data

Logging with VBA (example for object orientation)

Example: =CONCATENATE(A1:A9) does not work. Create this functionality with your own code: TEXTJOIN

Use OPTION EXPLICIT at the start of each of your modules:

Option_Explicit

This forces you to declare of all variables explicitely. You will get warned about variables you misspelt, for example.

Comment!

'This function takes the input parameters x, y, and z, performs a
'blabla calculation and returns value "oops".
'Version 0.1
'Date        Programmer Change
'22-Nov-2008 Bernd      Create

I indicate the quality of my macros with my version numbers: 0.1 is the first version, module tested, 1.0 would be good for general usage, 0.01 would be a prototype with some obvious drawbacks or similar. You can increase these version to 0.2, 1.1, etc. each time you apply a change. So everybody can see at one glance whether he has the most recent version and to which extent he might want to rely on it.

Sub, Function, Parameters

Sub

A sub(routine) normally performs well defined actions. It is good for repetitive tasks.

Function

A function encapsulates a reasonable portion of functionality, has zero or more input parameters, returns a return value (output parameter), and cannot change worksheet contents (ok, it can, but it shouldn’t!).

Parameters

Parameters are arguments for a sub or a function. They enable you to program in a more general way, because you can use them to represent the variable (flexible) inputs to your code. When your code is called with explicit values as parameters, these parameters in your code get replaced by the call values.

Parameter types or forms are: ByRef, ByVal, Optional (Preset), Paramarray.

Variables

Variables are very useful to store temporary data during execution of your code. You can store different types of data:

Type Name Examples Value Examples Comment
Boolean bProcessed True, False Can only have value False (0, Zero) or True (-1)
Byte btChar &H0F, 253 Value range 0 - 255
Currency ccyEUR 123.01 Value range -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date dtBirthday #12/31/1980# Value range 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59.
Decimal decDist 123789.12 With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001
Double dPi 3.14159265 -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Integer intAge 123 Value range -32,768 to 32,767
Long lIndex 17167 Value range -2,147,483,648 to 2,147,483,647
LongLong llBigNum 123152367765 Only available for 64 bit systems: value range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
LongPtr lptr 131313123 Signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647 on 32-bit systems; and signed 64-bit (8-byte) numbers ranging in value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems
Object obj A Range Use the Set keyword to set the object reference to the variable
Type Name Examples Value Examples Comment
Single sngNum 1.2345 Value range -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values
String sName “Bernd” Codes for String characters range from 0–255
User defined m_SystemState Any data type that you define by using the Type statement. User-defined data types can contain one or more elements of a data type, an array, or a previously defined user-defined type
Type Name Examples Value Examples Comment
Variant vA True, 123.01, #12/31/1980#, “Bernd” Value range -1.797693134862315E308 to -4.94066E-324 for negative values and from 4.94066E-324 to 1.797693134862315E308 for positive values. Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer. However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double. An error occurs when Variant variables containing Currency, Decimal, and Double values exceed their respective ranges