Solved Cleaning up a Mess (1 Viewer)

[KitaYama]

The Mess:
Over the years, I've added various classes and utilities to my Access database. Most of these use private API declarations, so I now have many duplicates scattered across different modules.

My situation:
All machines running this database, are on Microsoft 365, all 64 bit, only one 32 bit.
All Windows 11 except a few Windows 10.

My Plan:

1. Delete all #Else sections in conditional compilation blocks. ( I really hate that red blocks in vbe)
2. Move all APIs into a single new standard module called modWinAPI.
3. Use Find/Replace to change Private Declare to Public Declare.
4. Compile the project and delete redundant APIs when I hit "Ambiguous name" errors.

My questions:

• Is this a safe approach, or is it better to leave the duplicates alone?
• Will changing the physical order of these declarations affect how they function?
• Are there other risks (like 32-bit vs. 64-bit issues) I should be worried about before I start?
• Is Consolidating Duplicates a Recipe for Disaster?

Thanks for any kind of advice.

 

[The_Doc_Man]

Consolidating duplicate classes and utilities, as long as you use ByRef/ByVal correctly, should be OK. The only time it would be really icky is if you have anything declared STATIC - which retains values across invocations and therefore can make the code no longer automatically re-entrant. Code can be called from multiple places at once and the method of subroutine initialization normally keeps things separate on a per-invocation basis - i.e. re-entrant code that doesn't care how many forms you have open at once that would use it.

Changing the order of declarations would matter if and only if you had overlapping types AND were careless about using them. A case in point is the difference between DAO.Recordset and ADODB.Recordset, in the specific case that you don't qualify the Recordset declaration. In that and corresponding cases, order of library declaration makes a difference. Order of presentation can make a difference.

How safe is it? The real question is, how thorough are you?

 

[xavier.batlle]

My point is that's a good idea to delete #if VBA7 conditional if not needed but it's a bad idea to move all the APIs to a single module because :
- Sometimes the same API can use different parameter types on its declaration on different utilities.
- Having a module for each utility or utilities group it's easy to see its dependencies.

 

[KitaYama]

Consolidating duplicate classes and utilities, as long as you use ByRef/ByVal correctly, should be OK. The only time it would be really icky is if you have anything declared STATIC - which retains values across invocations and therefore can make the code no longer automatically re-entrant. Code can be called from multiple places at once and the method of subroutine initialization normally keeps things separate on a per-invocation basis - i.e. re-entrant code that doesn't care how many forms you have open at once that would use it.

Changing the order of declarations would matter if and only if you had overlapping types AND were careless about using them. A case in point is the difference between DAO.Recordset and ADODB.Recordset, in the specific case that you don't qualify the Recordset declaration. In that and corresponding cases, order of library declaration makes a difference. Order of presentation can make a difference.

How safe is it? The real question is, how thorough are you?

One more question if you don't mind.
Does changing a lot of Private Declares to Public Declares have any effect on the memory usage?

 

[KitaYama]

Sometimes the same API can use different parameter types

How about if after moving them, I use AMZ to reorder the whole module alphabetically? In this case I can see different instances of the same API on top of each other and compare them to see if the parameters are the same or not.

 

[The_Doc_Man]

One more question if you don't mind.
Does changing a lot of Private Declares to Public Declares have any effect on the memory usage?

At first glance, I would say that Public/Private has limited or no effect on memory usage. Access doesn't do constant cleanup of memory blocks, preferring to do a massive release on App exit. So the public/private nature of a variable only affects visibility.

The greater difference is whether something is defined in the module declaration area or inside the local declaration of a sub/function - because even a regular form's subs and functions release local variables on sub/function exit. But declaration-area variables don't necessarily get released. And there are restrictions on the effectiveness of PUBLIC declarations in a function that has local-scope variables.

 

[sonic8]

1. Move all APIs into a single new standard module called modWinAPI.
2. Use Find/Replace to change Private Declare to Public Declare.

I don't think this is a good plan.
Most API functions cannot be called in a simple and direct way as normal VBA functions but need initialization, prepared buffer, or are only useable in conjunction with other functions.

I would rather encapsulate those APIs in their own module, as probably have done, and live with a bit of redundancy. - These declarations will probably never change. So there is no problem with maintenance.

 

[isladogs]

I agree with removing the old VBA6 #Else blocks which are now redundant. This should be done not just in API and variable declarations but also in function headers. Doing all this will simplify the code and make it more readable.

However I would also advise abandoning the rest of your plan as being both unnecessary and potentially causing problems for the reasons explained by both @xavier.batlle and @sonic8.

 

[KitaYama]

@xavier.batlle @sonic8 @isladogs
Before following the given advice and keep the things as they are, I just want to be sure you have the correct information.
I gave a count and it hit to 285 API declarations (After deleteing #Else sections) and nobody knows how many of them are duplicates.
Do you still think it's better to leave them as they are?

thanks.

 

[xavier.batlle]

Do you still think it's better to leave them as they are?

Yes.
Years ago, I did what you want to do in a project and I've always regretted it. I remind when I wanted to add a new API module it, was a pain!

 

[isladogs]

I agree.
Also consider reusability / transferability - if you want to reuse one or more of those API dependant functions / modules in another database, do you really want to transfer the entire monster API module (modWinAPI) or indeed check what is needed from that module

However, that is a lot of APIs. Perhaps a more useful exercise would be to determine whether all of those 285 APIs are actually currently in use!

 

[KitaYama]

However, that is a lot of APIs. Perhaps a more useful exercise would be to determine whether all of those 285 APIs are actually currently in use!

Yes. Many of them are used for the ribbon, several for customized TaskDialogs, some for vertically aligning text in report textboxes, and others for various purposes.
A small portion of them were also taken from your site (dimming the screen, centering forms, etc.).
In the end, I followed the advice given and deleted only the conditional declarations, leaving the rest as they are.

Thanks to everyone who replied and shared their experience.
I really appreciate it.