VBA Programming

VBA Programming

VBA (Visual Basic for Application) Programming can add a great deal of power to the Microsoft Office applications.  People often use the term macros to refer to this type of programming. To me, the difference between VBA programming and a macro is one of scale.  A macro may also be “recorded” automatically in some of the office applications but don’t go looking for a recorder in Access.  😉

What follows are some highlights of the VBA programming I have done in the past.

In Excel;
Created an ongoing suite of tools for microarray analysis work. One member of this tool set “ChromaBlast” is the subject of a DRDC Technical Memo.
Automated a database for Environment Canada.
For a Liquor Agent, created an Excel Addin* to analyse the data downloaded from the LCBO (Liquor Control Board of Ontario).
For a scientific paper, created an Excel Addin* to automate the normalization of data sets for micro array analysis of genes.
In Word;
I have created numerous interactive templates, in which dialogue boxes collect information from the user and place that information into the document.
In PowerPoint;
I have created numerous useful tools for working with PowerPoint, such as a tool to find the length of an automated presentation without having to rerecord the presentation.

Free Macros

The following macros are a handy tools to add to your Excel installation.

The macro makes up for the lack of a Paste Link button in Excel.  Just attach the macro to a toolbar and away you go!*

Public Sub PasteLink()
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
End Sub 

Excel can format telephone numbers and American zip codes, however you will look in vain for a Canadian Postal Code format.  This macro allows you to select the postal codes you’ve entered and make sure they are in uppercase and have a space in the middle. This means you can enter your postal codes in lower case without any spacing and not worry. Contact me for a more elaborate macro, which will flag incorrectly entered postal codes.

Sub GoPostal()
'formats Canadian postal codes
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbUpperCase)
Rng.Value = Left(Rng.Value, 3) & " " & Right(Rng.Value, 3)
End If
Next Rng
End Sub

* Don’t know what I’m talking about?  That would be the reason you should hire me to help!

Leave a Reply

Your email address will not be published. Required fields are marked *