BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Sharing Code across Workbooks using Add-Ins
- Useful Examples of Code to Share (this blog)
- Creating an Add-In
- Using Add-In Functions
Posted by Andy Brown on 05 March 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Useful Examples of Code to Share
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.
A Function to Return Someone's Name
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 Function to Return a File Path
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.
A Function to Determine if a Workbook is Open
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.