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
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.
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
We will create a module in which we will create all our public user defined functions
- Get into the Visual basic editor (VBE)
- Create a new module (right click on your vbaproject)
- Rename it
- Write your own or copy them from some of the excel forums
- heres 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
Function quote(Optional stin As Variant = "") As String
quote = bracket(CStr(stin), """", """")
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) & "|"
afConc = s
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
Count = Count + 1
howmanyInList = Count
Set r = Nothing