When you’ve get started producing VBA applications in Excel it can be worthwhile producing your very own code library. Reusing procedures and features makes perception when a little fantastic tuning is all you want to make the code work for a recent venture.

There are many strategies to help save and organize your very own code, and this short article will reveal how you can help save and import code into a VBA module when wanted.

Developing The Code Library

Sorting a column making use of Excel’s kind function could possibly be a regular code snippet you would like to help save. This is the code:

Sub kind()
Dim rng As Variety
Set rng = Variety("a1").CurrentRegion
rng.Type Key1:=Variety("a1"), Order1:=xlAscending, Header:=xlYes
stop sub

The problem is wherever should really you help save the code so you can easily access it? A single option is to help save the code into a textual content file and then use VBA to study the contents of the file into a code module.

For this illustration, we’ve saved the code in a file termed “kind.txt” in a folder termed “library” beneath the recent workbook folder.

1st, we will determine the file and path wherever the code is stored.

path = ActiveWorkbook.path & "library"
myFile = path & "kind.txt"

We are likely to import the file contents into a module termed “Library”. This is only a module to maintain any code you import just before determining how to use it.

1st, we will delete any previous use of the “Library” module. We have turned off the screen alerts option to help save time simply because we undoubtedly want to delete the module.

Application.DisplayAlerts = Wrong
For Each individual a In Modules
If a.Title = "Library" Then
a.Delete
Exit For
Conclusion If Following

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

Set m = Application.Modules.Increase
m.Title = "Library"
m.InsertFile myFile

It will rely on your very own circumstance as to how greatest to set up the code library. Right here are some ideas:

  • Have an index file which will allow you to conveniently research for crucial words and phrases
  • Increase code to the library module fairly than start out from scratch every single time
  • Have some regular procedures in a different file which you can use with no modification.

Summary

In just a couple strains of code, this short article has proven how you can use formerly written code for long term reference when demanded. It makes perception to help save your previous work and VBA makes it uncomplicated to retrieve and research for your very own code snippets.



Resource by Andy L Gibson