Getting started with VBA

Although I’m not planning to go through a VBA tutorial (there are plenty of them out there far superior than anything I would do), it is clear that a many people do not actually know how to create a VBA procedure. That’s a real shame since the best way to learn excel is to copy from someone else on the many forums that are out there. So lets go through creating a VBA procedure. If you know about all this already, then try Get Started Snippets 
 

User defined Functions, Macros, Subs, Procedures, Classes, Modules,Public,Private ….

 
When you use the ‘macro recorder’ in Excel, the idea is that it will keep a record of what you did, and allow you to repeat it multiple times. What in fact it does is to create a VBA sub, which it stores in a module in a VBAproject associated with your Workbook. The structure of an excel workbook is like this
 
We are going to focus on the circled section – this is where you will put your procedures that you want to be able to access from any sheet. This is the same place that the macro recorder constructs a VBA representation of the series of actions you have asked it to perform.
 
Here’s a summary of some of the terms.
  • Macro – The result of recording a macro is the generation of a VBA sub in whichever public module you have selected when you recorded it.
  • Procedure – a generic term that covers both sub and function.
  • Sub – a series of VBA commands that do not return a result but perform some action
  • Function – like sub, but returns a result, or in the case of an array function, a list of results. 
  • Built in Function –  is something like =SUM(). These are functions already created and available within Excel. You use these every day.
  • User defined functions – This is what you will usually be creating. Sometimes referred to as UDF. These are functions that you create, that you can use in your worksheet the same way as built in functions or access from other procedures. You will create these in the blue circled area.
  • Module – A collection of procedures. You decide how many modules to have, and where to put the procedures.
  • Form – An excel form that is used to interact with the user
  • Form procedures – Procedures that are executed as a result of actions using the form, for example changing the value of a field or clicking a button. Normally these procedures would be private and not visible outside of the specific Form Module
  • Sheet procedures – Procedures that are executed only on the sheet they are associated with, for example some special processing when a particular cell value changes or when the sheet is opened. Normally these procedures would be private and not visible outside of the specific Sheet  Module
  • Classes – VBA is object orientated (for example a sheet is an object), which means that you can encapsulate properties (values associated with an object)  and methods (procedures you can execute on or using an object. Classes are descriptions of objects so that you can create your own. For more details on classes see Getting Started with Classes
  • This Workbook procedures – These are procedures that are associated with the workbook as a whole , are are usually associated with events – for example something special you want to do when the workbook opens.
  • Private – the procedure will be visible only from other procedures within its own module
  • Public – the procedure is visible across modules, including (if a function) from the cells of your worksheet , and accessible by the syntax =myFunction(arguments…), just like an excel built in function. For more details on scope and visibility see Understanding Scope

 

What that looks like in Excel

Here is a project, containing a class (cShapeContainer), 4 modules (Initiate, Roadmapping, Sorting, Utilities), a form (rmManageForm), a thisworkbook and a bunch of sheets.We are going to add a new module and create a couple of functions. 
 
Ah.. but wait – if you are reading this you may not even know how to get here. 

 

Preparing Excel to be able to create and execute macros

If you can already access Visual basic, you don’t need to do any of this section
 
In excel 2007, they introduced some extra security to stop malicious procedures running, so we have to do a few extra things first. It also introduced a different file extension (.xlsm) for spreadsheets containing macros. If you create macros in an (.xlsx) file, it will strip them out on saving so first lets make sure you can actually create a .xlsm
 
  • Get into Excel options

Get to the trust centre settings

Now we are good to go

Adding a module

We will create a module in which we will create all our public user defined functions
  • Get into the Visual basic editor (VBE)

Note: if you are missing the developer tab, add it: Go back to options as described above, select customize ribbon and add the developer option to the ribbon.

  • Create a new module (right click on your vbaproject)

Rename it

Add your user defined functions

  • Write your own or copy them from some of the excel forums
  • here is a few (not very inspiring ones), or if you prefer read Get Started Snippets – there are plenty of examples and a workbook to download there. 

Function bracket(stin As String, Optional brk1 As String = "(", Optional brk2 As String = ")") As String
    bracket = brk1 & stin & brk2
End Function
Function quote(Optional stin As Variant = "") As String
    quote = bracket(CStr(stin), """", """")
End Function
Function afConc(arr() As Variant) As String
    Dim i As Long, s As String
    s = ""
    For i = LBound(arr) To UBound(arr)
        If Len((arr(i, 1))) > 0 Then
            s = s & arr(i, 1) & "|"
        End If
    Next i

    afConc = s
End Function


Function howmanyInList(inThisList As Range) As Long
    Dim r As Range
    Dim Count As Long
    Count = 0
    For Each r In inThisList
        If IsEmpty(r.Value) Then
            Exit For
        End If
        Count = Count + 1
    Next r
    howmanyInList = Count
    Set r = Nothing
End Function

Use them

I hope this can get you started in VBA. You’ll find it an interesting world. Why not head over to Get Started Snippets and join our forum for questions, feedback and comments.