How To Create Your Own VBA Code Library In Excel

[ad_1]

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.

[ad_2]

Source by Andy L Gibson