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.

  1. Sharing Code across Workbooks using Add-Ins
  2. Useful Examples of Code to Share (this blog)
  3. Creating an Add-In
  4. Using Add-In Functions

This blog is part of our Excel VBA online tutorial.  Wise Owl also run excellent classroom-based VBA courses!

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 FullName function

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

End Function

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\"

End Function

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

Exit Function

End If

Next w

'if get here, no match found

DoesWorksheetExist = False

End Function

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.

 

This blog has 0 threads Add post