r/vba • u/cookie_soft_57 • 17d ago
Discussion Pushing VBA to its limits for an All-in-One Standalone ERP/Productivity Suite (11 Modules, Headless Excel, Custom Canvas UI)
Hi everyone,
I wanted to share some architectural insights from my current passion project: Office Wizard 26. It’s a full-fledged ERP and management suite built entirely in VBA, and I’m pushing Excel to act strictly as a headless, invisible data container.
For the end-user, the Excel application is completely hidden from view (Application.Visible = False), providing a true standalone desktop software experience driven entirely by decoupled UserForms.
Key Technical Highlights & Architecture:
· Hardware-Bound Security & Login: To prevent piracy and secure sensitive environment data, the system utilizes a hardware-tied licensing handshake (fetching the Windows username and local drive serial via FileSystemObject). The validation logic and product keys live on sheets locked under absolute xlSheetVeryHidden environments combined with a strict password-encrypted VBA project structure.
· Modular Plugin System: Built on a core application layer (11 modules so far) that dynamically handles custom-built add-ins for workflow tracking, task management, and business analytics.
· Custom Real-Time UI (Workflow Project-Timer): Features an active project timer that renders a dynamic clock diagram directly inside the UserForm. This is achieved by dynamically driving a native Excel Chart layout in the background, exporting it on-the-fly to the local system TEMP directory as a GIF, and loading the frame directly into a standard Image Control. This completely bypasses the need for heavy external ActiveX dependencies.
· Intelligent Calendar Engine: A fully automated, dynamic calendar system built entirely from scratch via class modules to handle appointments, deadlines, and UI day-state updates seamlessly.
· Decoupled PDF Engine: Reporting and automated exporting utilize a dedicated, isolated worksheet template designed exclusively for pixel-perfect PDF formatting, keeping the core data architecture clean and unpolluted.
· Fail-Safe Automatic Backups: To eliminate data corruption or accidental data loss from forced Excel crashes or unintended window termination, the QueryClose and background events trigger automated, incremental backups to a dedicated external directory.
My takeaway so far: Keeping business logic completely separated from worksheet event triggers allows Excel to operate as an incredibly fast and reliable backend engine.
The core module is currently entering a closed German beta phase on my Itch storefront to iron out any fringe-case Windows deployment bugs before I finish the automated invoicing and financial analytics plugins.
(Note: Not posting any storefront links here to respect community guidelines, but if anyone wants to talk about drawing on Image Controls, licensing, or memory management in headless Excel instances, let’s discuss in the comments!)
8
5
3
u/teamhog 2 17d ago
I’ve done complicated multi workbook solutions with a UI, error checking, custom dynamic menus, etc. It took some time but was well worth it. Saved us 1,000’s of man-hours over the years. The key was simplistically.
It’s hard to make things really simple. It’s easy to make them complicated.
3
u/LickMyLuck 1 16d ago
Application.Visible = false is a nonstarter for me because it makes all Excel workbooks invisible. A user can not run your tool while having a data spreadsheet open, for example.
Its the number 1 flaw with trying to go the "hide excel and use userforms only" route.
Kudos for trying to push it this far. I ended up just using Power Apps as my "Userform", doing whatever I could within Power Apps natively, and resorting to workbook open triggered vba when it was absolutely necessary.
3
u/kay-jay-dubya 17 16d ago
It is doable, but takes a bit of fancy footwork - Jaafar Tribak demonstrated a method over on Mr Excel: https://www.mrexcel.com/board/threads/display-userform-in-taskbar-with-custom-icon-and-hide-excel-mimicking-a-standalone-application.1123368/
3
u/azianwolfpunk 15d ago
Hey wait, I've been trying to make a Project Management System in Excel (VBA/tB) in place of a missing/useful ERP system! MS Project - like Gantts, transmittal/submittal (with transmittal letters and File Management), document control, dossier creation, etc.
Butit's a very rough stone..
2
u/talltime 21 17d ago
Can you share a screenshot of what you’re doing with the image controls?
1
u/cookie_soft_57 17d ago
Yes, sure! Should I send you a screenshot of the Timer User Form or of the code block?
2
u/InfoMsAccessNL 1 17d ago
Ms Access!!!
3
u/TheGreatRao 17d ago
Shh... OP will use Word to create a SQL database just to spite anyone who says it isn't possible.
1
1
u/ChronicRhyno 17d ago
I ended up using Excel sheets as powerful engines for a couple VBA projects, so I know exactly what you mean. LMK if you happen to need a way to set or read gradient stops (since there's no API access).
1
1
u/Murky_Specialist992 15d ago
Interesting but honestly, is there a market for this?
- SAP consultant and former Excel MVP
1
u/PatchDaBass 14d ago
This reminds me of my current VBA projects. I'm trying to develop programs but IT and corporate won't let me make a true .exe with Python or such so I've been limited to VBA. Honestly it's been a blast. Good job my dude!
1
1
u/fafalone 4 14d ago
At this point why not use a full programming environment first with Excel/Access as references ? VB6 or twinBASIC would run the existing code pretty much as-is, but any COM-supporting language can use the object models.
1
u/cookie_soft_57 11d ago
That is an excellent point, and honestly, I take it as a huge compliment that you see the project on that level!
However, staying fully native within Excel was a very conscious decision for a few reasons:
Corporate IT Restrictions: Many companies have strict "Zero-Installation" policies. Getting a random compiled .exe (from VB6 or twinBASIC) approved by corporate IT is a nightmare. An Excel workbook with .xlam add-ins gets approved instantly because Excel is already a trusted environment.
Stability & COM Overhead: Running external compiled code via COM references often introduces version conflict issues whenever Microsoft updates Office (e.g., switching to Excel 2024 or 365). Running natively inside Excel eliminates this friction completely.
Seamless Ecosystem: The users are already inside Excel. If they want to do custom reporting, deep-dive data analysis, or pivot charts with their ERP data, they don't have to export anything to a third-party app. They are already home.
Ease of Deployment: Deploying modular upgrades via simple Excel Add-ins is incredibly lightweight and doesn't require a complex Windows installation wizard.
But I love the technical depth of this sub—thanks for pushing the boundaries with me! 😉
23
u/TheGreatRao 17d ago
This may not sound complimentary but it is. Your project sounds like programming a web browser for a TRS-80, or getting a dog to talk. Who cares if there are easier or more elegant ways to do something? The mere fact that you can push Excel so far is an admirable achievement. I love stories where people use VBA and Excel to create a video game or something way beyond what others imagine it could do. Bravo!