Once you’ve got start creating VBA purposes in Excel it is worthwhile creating your personal code library. Reusing procedures and features is sensible when just a little high quality tuning is all it is advisable to make the code work for a present challenge.
There are a number of methods to save lots of and manage your personal code, and this text will clarify how one can save and import code right into a VBA module when wanted.
Developing The Code Library
Sorting a column utilizing Excel’s type perform is perhaps a typical code snippet you need to save lots of. Here’s the code:
Sub type()
Dim rng As Range
Set rng = Range("a1").CurrentRegion
rng.Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlYes
finish sub
The query is the place do you have to save the code so you’ll be able to readily entry it? One choice is to save lots of the code right into a textual content file after which use VBA to learn the contents of the file right into a code module.
For this instance, we have saved the code in a file referred to as “type.txt” in a folder referred to as “library” underneath the present workbook folder.
First, we’ll outline the file and path the place the code is saved.
path = ActiveWorkbook.path & "library"
myFile = path & "type.txt"
We’re going to import the file contents right into a module referred to as “Library”. This is just a module to carry any code you import earlier than deciding tips on how to use it.
First, we’ll delete any earlier use of the “Library” module. We’ve turned off the show alerts choice to save lots of time as a result of we undoubtedly need 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 will create the module “library” and import the contents of the file.
Set m = Application.Modules.Add
m.Name = "Library"
m.InsertFile myFile
It will rely by yourself state of affairs as to how greatest to arrange the code library. Here are some concepts:
- Have an index file which lets you simply seek for key phrases
- Add code to the library module relatively than begin from scratch every time
- Have some normal procedures in a separate file which you need to use with out modification.
Summary
In just some strains of code, this text has proven how you need to use beforehand written code for future reference when required. It is sensible to save lots of your earlier work and VBA makes it straightforward to retrieve and seek for your personal code snippets.
