Code Catalog for Access / VBA - Making Code Sharing Easy

[Edgar_]

Now that I think about it, it doesn't have to be just for Access. It could cover the entire Office suite and anything else that runs VBA. With help from the Excel community, even though the VBA crowd can be pretty toxic, just like the Access crowd, we'd have a better shot at getting it adopted.

 

[Isaac]

Here's how I envision it. It has three main parts:

1. The centralized registry of packages.
2. The developer or developers who create and publish the package.
3. The developer who will be using the package.

Scenario:
On a typical day, a developer is working on a form and realizes that a particular combo box should filter its options as the user types. The developer might know how to build this from scratch, or maybe not, but luckily there's a tool available within their environment that helps them integrate existing solutions.

What you as a user of packages will do:
First, the developer opens this tool, which presents a list of popular packages that can be imported. Somewhere in the tool, there's a search bar placed conveniently. The developer types something like 'combo box' or 'filter combo' into the search.

Next, the tool displays a list of packages that match the search criteria. Some example results might be:

• 'Combo Box Cascader' by MikeTools
• 'Filter Combo Boxes, List Boxes, Any List and More' by AlfredTheVBAMaster
• 'FAYT Filter Combos' by @MajP
• Other related packages

The developer can click on any of these options to view an overview of what the package does, but more importantly, how to use it. This helps the developer quickly decide if the package meets their needs before choosing to integrate it. The overview also includes useful details like the number of downloads, issue reports, developer reputation, and other indicators that help with the decision-making process.

Once the developer finds a suitable package, they can click to download it. At this point, the tool provides options based on how the developer wants to integrate it. For example, they might choose to:

• Import just the class module.
• Include a sample form.
• Add a sample database.
• Insert a few lines of code into a selected module to initialize the tool.

This makes it easy for the developer to customize the integration process and get up and running quickly without manually copying code or hunting through examples.

What a package developer will do:
Now, on the other side, the developer who makes the package should make sure to provide:

• A clear description of what the package does.
• A readme or usage guide with examples.
• Version numbers for each release.
• Information about dependencies, if any.
• A changelog explaining what's new in each version.
• Optional sample files like forms or sample databases.
• Metadata like author name, license type, and optionally a trust badge if the platform supports it.
• This makes it easier for others to understand what they're installing and how to use it right away.

We could use GitHub for some of these things.

The centralized registry:
Github could be used to store and serve all packages, their metadata, and any associated sample files, but we need an service to orchestrate the whole process. This other service should be able to:

• Provide an API or service that the tool uses to search, fetch package info, and download packages.
• Track download counts, version history, issue reports, and developer profiles. Same as above.
• Enforce publishing rules, like no duplicate package names, versioning, file size limits, etc.
• Support developer verification and trust scoring to help users identify reliable packages.
• Allow package removal or deprecation, that would be tricky, though.

This part is obviously the hardest, because we're not really sure if it's even going to be used and it requires a ton of development effort. However, we can create a light way version that does the minimal and see how well it is adopted, sort of like an MVP (Minimum Viable Product).

Comes way too close to "using code having no idea how it works" and deploying into prod. (something I happen to personally believe is a catastrophic failure of judgment). I actually prefer the forum style, where I'm giving a bite on a spoon but not the whole plate, and then I build and grow from there. Importing a bunch of someone's code and hitting Go is what this is going to turn into

 

[Josef P.]

actually prefer the forum style, where I'm giving a bite on a spoon but not the whole plate, ...

This makes sense for custom problem-solving to understand the logic.
But let's say you need a JSON conversion. Why would you write it yourself instead of using an existing, proven class or module?

But that brings me to another point:
Who is the target audience? - The range between absolute beginners and experts is large.

 

[jwcolby54]

As for how to make it viable, one good starting point would be to reach out to the owners and admins of the different forums and communities where developers already hang out. We could ask for their help to promote the tool and make it visible to as many developers as possible. Even just having a pinned post, a banner, or a mention in a newsletter would go a long way in getting eyes on it. Or just straight up word of mouth if forums don't want to help.

If the tool picks up enough traction and starts getting widely used, it might eventually find its way into the datasets of large language models. And once that happens, you could have LLMs suggesting something like, "Hey, download this tool and just search for this package, here's how you use it". It would turn into a self-sustaining loop where people find the tool, use it, and the AI suggestions help surface it even more. It should not be that difficult, take the VBA-JSON library as example, the LLMs know it.

It's one of those things where if the community adopts it early, it has a real shot at sticking around and becoming part of the normal workflow.

Is there a code repository / categorizing / filtering tool in existence for us to glom onto (a technical term) and use for our purposes? Or is the design and implementation of this tool required to get going?

 

[Isaac]

Why would you write it yourself instead of using an existing, proven class or module?

No reason, as long as you study the code, understand it, and approve it yourself.
I agree with you, but we all know that a certain % of users of those 'packages' will never know anything about what it's doing, and will deploy it to production that way. Then disaster stikes when there is an error. Or, worse - much worse - than an error....Disaster strikes by way of inaccurate reporting that nobody ever even knows about.

 

[Isaac]

Who is the target audience? - The range between absolute beginners and experts is large.

That's a good question.

 

[Edgar_]

Comes way too close to "using code having no idea how it works" and deploying into prod. (something I happen to personally believe is a catastrophic failure of judgment). I actually prefer the forum style, where I'm giving a bite on a spoon but not the whole plate, and then I build and grow from there. Importing a bunch of someone's code and hitting Go is what this is going to turn into

Absolutely, open source code faces these challenges by default, the important part is that it will let you advance much faster without a lot of bureaucracy and review upfront. That's why it should rely on the feedback of the community. Eventually, the best packages will rise to the top, the bad ones will go to the bottom and the evildoers will get slashed. We could always have a community-verified group of packages too. That's the entire point, having an active community. The way it is today, we just don't have that.

Is there a code repository / categorizing / filtering tool in existence for us to glom onto (a technical term) and use for our purposes? Or is the design and implementation of this tool required to get going?

As far as I'm concerned, there's only individual efforts to bring repos into VBA projects, @Josef P. says he has some tools that help him already, but we need the rest of the thing: we need a system to orchestrate the categorization, filtering, indexing, ranking, etc.

That said, I encourage you to try starting a project in Python or NodeJS to know what it's like. Explore their package managers, pip for Python, npm for NodeJS, and download a few libraries you think your project could benefit from. You'll quickly see how much faster and easier development becomes.

For example, let’s say you want to build a tool that calculates the number of active days between two dates. You know damn well date manipulation is a real PITA. But if you pick NodeJS, you’ll be working in JavaScript, and thanks to npm, you have access to libraries like DayJS, Moment, or Luxon. All of them are great and proven in battle.

Instead of manually juggling Date objects, you just import one of these libraries and start using straightforward functions based on your date format, like this:

Instead of something like this using Vanilla JS

Notice that you don't need to run the calculations yourself, the library already does it for you.

 

[AHeyne]

A negative aspect of code sharing is that it usually creates a mess.
If I want to use external code, I usually rework it completely so that, for example, the naming and most importantly the casing (we all know the problem) fits for me. I pay a lot of attention to this, it's a real PITA.

Is that a nuisance for you that you put up with?

 

[Josef P.]

As far as I'm concerned, there's only individual efforts to bring repos into VBA projects, @Josef P. says he has some tools that help him already, but we need the rest of the thing: we need a system to orchestrate the categorization, filtering, indexing, ranking, etc.

This is possible with my current add-in:

The "Package" file:

AccessCodeLib/_examples/Package_FilterStringBuilder.bas at draft · AccessCodeLib/AccessCodeLib

Reusable VBA code for Microsoft Access applications - AccessCodeLib/AccessCodeLib

github.com

Note: This is just for demonstration. I would rather write the "real" package file as JSON or XML.


[Letter case / naming]

If I want to use external code, I usually rework it completely so that, for example, the naming and most importantly the casing (we all know the problem) fits for me. I pay a lot of attention to this, it's a real PITA.

There is only one good way to do this: define and follow a coding style.

Whether Hungarian notation or just PascalCase is used for variables shouldn't matter. One could debate for hours about which is "better."
It's important to adhere to uppercase and lowercase. And in my opinion, there's only one practical rule for VBA: write everything in PascalCase, except for the prefix, when using Hungarian notation.
The more unique the procedure names or variable names are, the fewer problems there are. Therefore: don't be afraid of long variable names.

Info on this topic:

How To: Enforce Consistent Letter Casing in VBA

Conquer VBA's case-changing "feature" once and for all with this simple, brute-force solution: the StandardizeLetterCasing() function.

nolongerset.com

My add-in to check letter case: https://github.com/AccessCodeLib/ACLibDeclarationDictionaryAddIn

 

[AHeyne]

@Josef P. : Thanks for the links, I already knew them
I agree with you. I use long variable- and procedurenames (I like reading code like a book), don't use prefixes like "lng", "str" and so on (what unfortunately has been misunderstood as Hungarian Notation decades ago) and have my coding style and follow it.
Unfortunately I can't get used with using PascalCase for everything, like e.g. variables. That burns my eyes.

 

[gemma-the-husky]

Surely one problem is that developers end up giving way stuff that's taken them a lot of hard work to develop.

For example, it really was difficult to find and build parts of an application to manage interfacing with a Restive API instance in Access VBA, and process the JSON responses. It was actually quite difficult to get the right VBA syntax. I was never able to collect a pdf file as a stream of bytes, and save it as a viewable pdf. (I think that's because VBA uses unicode/2 byte characters). My terminology may be slightly off.

Anyway, I spent ages on all this, and I'm reluctant to give it all away.

Sorry if this comes over as curmudgeonly, but a lot of people here give away an enormous amount in general.

 

[Uncle Gizmo]

The more unique the procedure names or variable names are, the fewer problems there are.

One of the most overlooked benefits of using the three-letter prefix, particularly for form controls like "txtMyTextBox" or "chkMyCheckBox," is that it automatically eliminates any chance of using a reserved word. You don't ever have to think about it. One of the biggest mistakes people make is naming a text box "Date" or accidentally using some other reserved word. Unfortunately, this is not something automatically verified by Access. It is only discovered when you start writing code against the objects, and then you get errors that are difficult to resolve until you learn from your mistakes! I also apply the same trick to function names and will name them using this pattern: "fMyFunction."

 

[Uncle Gizmo]

On my website I have a compendium of my favorite links:- https://niftyaccess.com/ms-access-links/ --- it is searchable and you can export the results of the search into a PDF (I think from memory)

it's done with a Google sheet and an add in provided by MayMay --- unfortunately the add-in is no longer supported !

 

[GPGeorge]

I think I see the emergence of one of the issues that make endeavors like this so hard to implement.

Specifically, I'm referring to the fact that each and every one of use has preferences and standard practices for our own work. Sometimes they overlap, sometimes they don't.

Also on display are subtle and not-so-subtle differences of opinion on the best way to implement a system, a curated site of links to all kinds of random code, a repository of vetted, standardized code, an add-in that installs code from a central (or distributed) location or locations. Probably other variations can be identified.

Unless and until consensus is achieved, it's not going to go very far.

I think, in fact, that is the point on which previous proposals have foundered. Everyone likes the general idea; not everyone agrees on the right way to implement it.

If you all can come to an agreement about how to set up and how to operate, and stick to that agreement through subsequent efforts, there is a good chance of success. If the process is delayed and delayed while those issues are hashed out, and momentum lost, there's a considerably lower chance of success.

 

[Josef P.]

Actually, capitalization is just a technical problem that can be solved technically. If you want, your own spelling will always be used, even after an import.

Possible (automated) procedure:

1. Create a dictionary before importing
2. Import code modules
3. Check against dictionary and fix it

 

[Edgar_]

A negative aspect of code sharing is that it usually creates a mess.
If I want to use external code, I usually rework it completely so that, for example, the naming and most importantly the casing (we all know the problem) fits for me. I pay a lot of attention to this, it's a real PITA.

Is that a nuisance for you that you put up with?

Whenever I see code I want to use in Access, I adapt it to my style so I can understand it better later.

But working with a code sharing tool like this feels different. I see it more like importing APIs for things I don't want to deal with myself. In that sense, I care less about how it was solved and more about how to use it. I can plug it into my own interfaces, swap one library for another, or just use it as-is. If I feel like it, I can dig into how it works, but I'd rather focus on the fact that it gets the job done.

There will always be someone else who enjoys dissecting things, submitting improvements, or making their own version. But you don't have to do any of that, and that's one of the beauties of it.

If none of what I say makes sense to you, I suggest that you go ahead and get some existing package manager, there are package managers for Python, NodeJS, Linux, etc. Try working with them and see if you find any need to make modifications. Most of the time, if you're someone who just wants to get it done, you will import it and use it. And the package manager will inform you about deprecation or vulnerabilities found and stuff like that. The entire web is standing on top of other's work, for example, and it comes with cons, but it works, in the end.

If you want an example of that spirit, just view this page's source code, you will see this today:

<script src="https://accessprogrammers.b-cdn.net/forums/js/siropu/am/core.min.js?_v=5cfb883b" defer></script>
<script src="https://accessprogrammers.b-cdn.net/forums/js/xf/action.min.js?_v=5cfb883b" defer></script>
<script src="https://accessprogrammers.b-cdn.net/forums/js/xf/message.min.js?_v=5cfb883b" defer></script>
<script src="https://accessprogrammers.b-cdn.net/forums/js/xf/lightbox-compiled.js?_v=5cfb883b" defer></script>
<script src="https://accessprogrammers.b-cdn.net/forums/js/xf/captcha.min.js?_v=5cfb883b" defer></script>
<script src="https://accessprogrammers.b-cdn.net/forums/js/xf/editor-compiled.js?_v=5cfb883b" defer></script>
<script src="https://accessprogrammers.b-cdn.net/forums/js/xf/attachment_manager-compiled.js?_v=5cfb883b" defer></script>
<script src="https://accessprogrammers.b-cdn.net/forums/js/sv/useractivity/last_seen.min.js?_v=5cfb883b" defer></script>
<script async src="https://www.googletagmanager.com/gtag/js?id=UA-237618-9"></script>

Those are just some of the dependencies used for the frontend. Some might be used as they are, others might be tweaked and others, like the tag manager, as you can see, is being imported from the source.

 

[Isaac]

I use long variable- and procedurenames

Thank you! I constantly say, variable/alias (etc) should be DESCRIPTIVE, not CRYPTIC.
You want it to help you remember what it means once you're 3 pages down from the declarations area.

 

[Uncle Gizmo]

You want it to help you remember what it means once you're 3 pages down from the declarations area.

In my early attempts at coding, being clever, I did include some cryptic names for things, because I thought people might steal my code ... I thought what the hell, I will make it difficult for the bar-stewards... it was a few months later when I realized I had just made it very difficult for myself !!!

 

[jwcolby54]

It is a real thing that if you take out vowels, the rest is readable, especially when you throw in CamelCase or whatever you call it.. The human brain just works that way .

MyLngDcmntNmIsRdbl
MyLongDocumentIsReadable

There are obvious exceptions, places where the vowels are too long

ISeeThtYouCntDoThs

In any event I do strip out vowels when I can. And I'm not religious about it. I'm not about to go edit someone else's code to "do it my way" as long as someone else's code is readable.

I do abhor the use of
x, y, z in counter / loop names and stuff like that.

I more care about "hungarian" notation. I like being able to read the variable name and know I need to place a double in a dblVariable. Or a recordset in a rstVariable.

lngMyLong
strMyString
varMyVariiant
dblMyDouble
Etc.

One if the problems with VBA is that it can coerce variables from one type to another which can have unintended consequences when done without understanding what might happen.

 

[Josef P.]

There will always be different spellings for different developers. But I personally don't see this as a problem. If a class, framework or whatever works well, I don't really care about its variable naming. The main thing is that I can understand the content of the code if necessary.

A thought experiment:
Let's assume that the catalog is technically solved and available. There is also the add-in or whatever to find and import what you want.

Which sources/packages would be included in this catalog?
Who would provide these packages?

Example: VBA-MicrosoftGraph by Maria Barnes
I haven't tried the code myself yet, but I think it's a good example that can save developers a lot of work if you can import it easily.
Thanks at this point to Maria!
Ideally, Maria would send a pull request with the package definition to the catalog or maybe even provide such a package file in her own repository that you can then use/clone.
For open source on GitHub (or similar) I don't see a big problem in defining packages for this. If necessary, you could create a fork and make it available in the package.

What about the many good sources in forums?
How many of these authors would be willing to make their code available on GitHub or similar platforms?

So I'll come back to the first post:
Where are the sources that could be used via an "Access-npm"?
And where are the developers who would provide this?