User defined Functions, Macros, Subs, Procedures, Classes, Modules,Public,Private ….
- 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
Preparing Excel to be able to create and execute macros
- Get into Excel options
Get to the trust centre settings
Adding a module
- 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)
Add your user defined functions
- Write your own or copy them from some of the excel forums
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