Menu
What is the best way to avoid duplicating code when working in VBA?
I'm used to languages where I can just add an import statement and get access to all a class's public properties and functions, so I can just create a utility class with some common functions and have access to that in any project I choose to import it to. Any time I want to update one of those functions, one edit is all it takes to get it working across all projects.
Is there any good way to replicate this functionality in VBA?
780
How to recover unsaved Excel files Go to FILE -> Open. Choose Recent Workbooks. Scroll down and click on the Recover Unsaved Workbooks button at the bottom of the list. Note. ...
Sub procedures DO NOT Return a value while functions may or may not return a value. Sub procedures CAN be called without a call keyword. Sub procedures are always enclosed within Sub and End Sub statements.
Just type the word Call then space, then type the name of the macro to be called (run). The example below shows how to call Macro2 from Macro1. It's important to note that the two macros DO NOT run at the same time. Once the Call line is hit, Macro2 will be run completely to the end.
What follows focuses on Excel but I am pretty sure the same would apply to any Office products.
The easy way is to save your reusable code as an addin (*.xla for Excel 2003, *.xlam for Excel 2007+). You then add the addin to Excel and all the spreadsheets you open will have access to the custom functions you have in your addin. If you add specific VBA code to a spreadsheet, you can add a reference to your addin and your VBA code will have access to all the public sub, function and classes of your addin.
In my organisation, we use 3 home made addins - they are stored in C:\Program Files\OrganisationName. And everybody has access to them. When an update is made, we only need to copy the new version to everybody's hard drive and restart Excel and they have the new version.
The addins contain utilities functions such as functions to:
A few drawbacks:
An alternative is to develop xlls or COM libraries in VB or C# for example, but this is something I have not tried.
There are plenty of tutorials online if you need a more detailed procedure.
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
