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:
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.
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.
Now we can create the module "library" and import the contents of the file.
It will depend on your own situation as to how best to set up the code library. Here are some ideas:
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.
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.
Andy L Gibson is a an Excel developer and writer keen to bring the magic of VBA to non-programmers. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download.
Article Source
0 comments:
Post a Comment