How To Create Your Own VBA Code Library In Excel

How To Create Your Own VBA Code Library In Excel

As soon as you’ve got start creating VBA functions in Excel it is worthwhile creating your personal code library. Reusing procedures and features is sensible when just a little fantastic tuning is all it’s essential to make the code work for a present mission.

There are a number of methods to save lots of and arrange your personal code, and this text will clarify how one can save and import code right into a VBA module when wanted.

Growing The Code Library

Sorting a column utilizing Excel’s type operate could be a typical code snippet you would like to save lots of. Here is the code:

Sub type()

Dim rng As Vary
Set rng = Vary("a1").CurrentRegion
rng.Type Key1:=Vary("a1"), Order1:=xlAscending, Header:=xlYes
finish sub

The query is the place must you save the code so you possibly can readily entry it? One possibility 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 known as “type.txt” in a folder known as “library” beneath 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’ll import the file contents right into a module known as “Library”. That is merely a module to carry any code you import earlier than deciding the best way to use it.

First, we’ll delete any earlier use of the “Library” module. We have turned off the show alerts possibility to save lots of time as a result of we undoubtedly need to delete the module.

Software.DisplayAlerts = False

For Every a In Modules
If a.Identify = "Library" Then
a.Delete
Exit For
Finish If Subsequent

Now we are able to create the module “library” and import the contents of the file.

Set m = Software.Modules.Add

m.Identify = "Library"
m.InsertFile myFile

It’ll rely by yourself scenario as to how finest to arrange the code library. Listed here are some concepts:

  • Have an index file which lets you simply seek for key phrases
  • Add code to the library module moderately than begin from scratch every time
  • Have some customary procedures in a separate file which you need to use with out modification.

Abstract

In only a few traces 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.

No Comments

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.