This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Sharing Code across Workbooks using Add-Ins Useful Examples of Code to Share (this blog) Creating an Add-In Using Add-In Functions This blog is part of our Excel VBA online tutorial. Wise Owl also run excellent classroom-based VBA courses!

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.

Sharing Code across Workbooks using Add-Ins
Useful Examples of Code to Share (this blog)
Creating an Add-In
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 07 July 2023

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

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.

Parts of this blog
Sharing Code across Workbooks using Add-Ins Useful Examples of Code to Share (this blog) Creating an Add-In Using Add-In Functions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Useful Examples of Code to Share

A Function to Return Someone's Name

A Function to Return a File Path

A Function to Determine if a Workbook is Open

Head office

London

Manchester