563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Using Excel Add-Ins to Share VBA Code
Part two of a four-part series of blogs
If you've written a killer function, you'll want to be able to share it between workbooks. The best way to do this is using an add-in.
Here - fairly randomly picked - are 3 examples of functions that it might be useful to share between workbooks.
A separate blog shows how to write functions.
In the worksheet below, we've used the FullName function to create a name suitable for mail merges, given a person's first and last names as inputs:
The function joins the first and last names together with a space in between, capitalising each part of the name.
Here is the code for a basic version of this function:
Function FullName(FirstName As String, LastName As String) As String
'capitalise first letter of names
Dim ProperFirst As String
Dim ProperLast As String
ProperFirst = UCase(Left(FirstName, 1)) & LCase(Mid(FirstName, 2))
ProperLast = UCase(Left(LastName, 1)) & LCase(Mid(LastName, 2))
FullName = ProperFirst & " " & ProperLast
This function will struggle with names like Samuel L. Jackson and Kate O'Riordan, but this blog is about sharing code, not writing it!
A much simpler function could be used to return the path to all of your files:
Public Function WiseOwlFilePath() As String
'return path to where all files stored
WiseOwlFilePath = "C:\Wise Owl Files\"
Using this function could ensure that if you ever move your files, you'll only need to change the file path in code in one place.
To avoid reinventing wheels, you could share a function to find out if a given workbook is open or not:
Function DoesWorksheetExist(SheetName As String)
'checks whether a worksheet exists
Dim w As Worksheet
'loop over all of worksheets in current workbook
For Each w In ActiveWorkbook.Worksheets
If UCase(w.Name) = UCase(SheetName) Then
'if found a match (whatever case), say so
DoesWorksheetExist = True
'if get here, no match found
DoesWorksheetExist = False
This function loops over all of the worksheets in the active workbook, trying to find the one we're looking for. If it can't find a worksheet with the name specified, it returns False; otherwise, it returns True.
|Parts of this blog|
25 Aytoun Street