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.