Contact Us

Contact SLIM Enterprises today to get the most out of your business.

captcha

How To Create Your Own VBA Code Library In Excel

November 10, 2017

Once you’ve begin developing VBA applications in Excel it’s worthwhile creating your own code library. Reusing procedures and functions makes sense when a little fine tuning is all you need to make the code work for a current project.

There are several ways to save and organize your own code, and this article will explain how you can save and import code into a VBA module when needed.

Developing The Code Library

Sorting a column using Excel’s sort function might be a typical code snippet you’d like to save. Here’s the code:

Sub sort()

Dim rng As Range
Set rng = Range("a1").CurrentRegion
rng.Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlYes
end sub

The question is where should you save the code so you can readily access it? One option is to save the code into a text file and then use VBA to read the contents of the file into a code module.

For this example, we’ve saved the code in a file called “sort.txt” in a folder called “library” under the current workbook folder.

First, we’ll define the file and path where the code is stored.

path = ActiveWorkbook.path & "library"

myFile = path & "sort.txt"

We’re going to import the file contents into a module called “Library”. This is simply a module to hold any code you import before deciding how to use it.

First, we’ll delete any previous use of the “Library” module. We’ve turned off the display alerts option to save time because we definitely want to delete the module.

Application.DisplayAlerts = False

For Each a In Modules
If a.Name = "Library" Then
a.Delete
Exit For
End If Next

Now we can create the module “library” and import the contents of the file.

Set m = Application.Modules.Add

m.Name = "Library"
m.InsertFile myFile

It will depend on your own situation as to how best to set up the code library. Here are some ideas:

  • Have an index file which allows you to easily search for key words
  • Add code to the library module rather than start from scratch each time
  • Have some standard procedures in a separate file which you can use without modification.

Summary

In just a few lines of code, this article has shown how you can use previously written code for future reference when required. It makes sense to save your previous work and VBA makes it easy to retrieve and search for your own code snippets.

Leave a Reply

Your email address will not be published. Required fields are marked *

Archive

Categories

Recent News

Recent Comments