How to create a library in Excel VBA
Microsoft has installed the VBA programming language into Excel to enable a user to write bespoke programs for his own specific purpose. While procedures can be written to run specific routines in a worksheet, functions can be written too.
Procedures are blocks of code that perform actions and do not return values. Functions, on the other hand, are blocks of code designed to perform a specific task and return a value.
A function works because it is called from a procedure.
I had a bit of difficulty initially writing functions because I was not sure how to write them in Excel VBA. A function can be written beneath a procedure in the code window, but the user might have difficulty finding where the function is stored if he wants to reuse it in another procedure.
Get Crystal X’s stories in your inbox
Join Medium for free to get updates from this writer.
A better way to write and store functions in Excel VBA, in my opinion, is to create a library. The user only needs to insert a module from the Insert tab in the VBA window. The user then names the procedure, and in this instance I have named the module math_functions:-
Once the math_functions module has been created and the code window is open for view, the functions can be coded. In this example I have coded three functions, being:-
- Square_number
- Calc_cube
- calc_square _root
All three of these functions accept numbers as input, and these numbers come from outside of the functions:-
A module needs to be written to actually call a function. In the example below, I have written the procedure, fund_square_of_num, which:-
- Prompts the user to enter a number.
- Calls the square_num function, which takes the number the user has entered as input and calculates the square of the number.
- Generates a message box that displays the calculation.
I have inserted buttons that the user can press, which will automatically run a procedure. In this example, the user only needs to click onto the button, Find square of number (click here), as seen below:-
When the user clicks onto the appropriate button,a message box will pop up and prompt the user to enter a number he wants to calculate the square of:-
Once the user has entered the number he wants to calculate the square of and clicked onto the OK button, a window will appear, displaying the calculation:-
The above example was a very simple module that calls a function from a library to illustrate the mechanics of this process. Certainly, more complex procedures and functions can and should be made to obtain Excel’s maximum potential.
I have created a video on this blog post and it can be viewed here:- https://youtu.be/JovZYP8dHkQ
