WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 525 reviews for our classroom and online training
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