Converting all of my Excel VBA library standard modules to class modules?

[Chaoxite]

Hello everyone, this is my first post

So, for some years I have had a macro-enabled workbook that I also use as a library file of common procedures I can use. In my last job, I created several Excel templates that were used for reporting. Each template was a unique report and contained some VBA for its own needs but each used my library file for general/common code, such as connecting to a SQL server database and using ADO to download data etc. I have a general rule that VBA that is very generic goes in the library file and VBA that is only relevant within the file itself, goes in that file.

Anyway, I've been using standard modules for the most part with a couple of class modules that extend the functionality of certain MS Access controls but that's another story. I've been thinking of late to switch everything to class modules and wanted to get an idea if people thought this would do more harm than good. I've seen things on other posts that class modules are slower than standard but after running the same test several times on both a standard module and class module, they both performed at 15-16 seconds. I know class modules have to be instantiated but even standard modules have to be loaded into memory to at some point. I'm also aware of the VB_PredeclaredId = True means a class is automatically instantiated without the need to create it explicitly.

There are three main reasons for wanting to switch to class modules. First, any VBA inside a given class will represent a specific theme (such as Excel functions, Access, File System, ADO, DAO, etc.). This is currently how they are setup in their standard modules. If they are in classes though, this would prevent unintentional dependencies from forming i.e. the VBA in each class will not have to reference VBA in another module except for the common error handling module.

The second reason is more along the lines of being organised (this is a refactoring exercise too), since I have quite a lot of functions beginning with "Get" or "Create" so instead of calling CompareExcelRangeRows it would be Excel.Range.CompareRows or something like that. With Excel and Range being classes if there are enough functions in those categories to warrant two classes and if not, something like Excel.CompareRangeRows.

And thirdly, if I decide to switch a class library DLL via VB.net, then everything needs to be in classes anyway.

I would appreciate any thoughts on this matter although it is more of a "preference" or "what works best" type of question.

Thanks all.

 

[RoryA]

Putting your library code in classes won't affect anything other than how you use it (won't be slower/faster) - eg you need calling code and you can't run it from menus directly.
Trying to call your classes Excel/Word etc will cause you issues because those are of course the built in library names.

 

[Chaoxite]

Putting your library code in classes won't affect anything other than how you use it (won't be slower/faster) - eg you need calling code and you can't run it from menus directly.
Trying to call your classes Excel/Word etc will cause you issues because those are of course the built in library names.

Hi RoryA,

I came across this issue when I first created the standard modules a while back and as such, all of my modules (standard/class), userforms, constants and enums have a 2-letter prefix (similar to ac = Access, xl = Excel, wd = Word, etc.) to avoid name conflicts but I can use a standard module with properties to return a specific name such as:

Public Property Get Excel() As psExcel
Set Excel = New psExcel
End Property

This works without any issues and by using VB_PredeclaredId = True, internally, I can just call the class directly but these properties would be needed for external VB projects to call the code from their classes.

I did realise the use of these properties add an extra step when calling the classes but the way around that is that the external VBA could have a variable set to the class(es) needed. But I wonder if this is just creating more problems.

 

[RoryA]

all of my modules (standard/class), userforms, constants and enums have a 2-letter prefix (similar to ac = Access, xl = Excel, wd = Word, etc.) to avoid name conflicts

Given that all the built-in constants/enums use the same prefixes, that sounds more likely to cause a conflict to me.

I think this boils down to a judgement call as you'll probably have a lot of work to rewrite existing code, so it's a question of whether you think you will actually get any benefit out of it. If you write and distribute a lot of code, it may be worthwhile in the long run.

 

[Chaoxite]

Sorry, just wanted to clarify I only use ps as a prefix on my modules, constants and enums, just how all Access enums use ac, etc. I will be rewriting code anyway, to tidy up some of the mess that has evolved over time and I think there is still some benefit to having code in a particular class that has no dependencies in other classes and knowing that I could transfer a class to another VB project knowing it will work fine or, for code no longer needed (I'm thinking about my IE automation code), that I could delete that class knowing it won't break anything else. But as you say, it's a judgement call.

Thanks Rory.

 

[RoryA]

just wanted to clarify I only use ps as a prefix on my modules, constants and enums, just how all Access enums use ac, etc

Ah, I misunderstood (good!).