what's your most complex VBA code project? (1 Viewer)

vba_php

Forum Troll
Local time
Yesterday, 19:29
Joined
Oct 6, 2019
Messages
2,884
what is the most complex vba code project you've ever taken on? got any code snippets from the project? I once created an FTP application similar to Filezilla. A lot of the code contains DLL functions and class types.
 

Attachments

  • FTP VBA code.zip
    5.3 KB · Views: 124

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:29
Joined
Jan 20, 2009
Messages
12,849
An application that looked up information stored in a database about text based reports in the file system, found them and parsed them. All the work was done in a Class. The parsed data was held in recordsets. Each recordset that was loaded was added to a Collection so it didn't need to be parsed again in the session.

It analysed and stored critical information about the reports in the database for subsequent use.

Another one verified all the links in selected Word files and offered the opportunity to replace the links that couldn't be found. The results were displayed in a fabricated ADO recordst.
 

isladogs

MVP / VIP
Local time
Today, 00:29
Joined
Jan 14, 2017
Messages
18,186
That's a very subjective question and in the end perhaps somewhat meaningless.

Usually its the last major project with new features that I've finished. The final code may often be simple but the process of achieving that can often involve very complex coding.

Some apps I created 10+ years ago that seemed complex at the time now seem simple as I've reused the code and skills elsewhere since. By contrast, several projects I completed as coding challenges in the past few years now baffle me as I've never reused the code elsewhere.

Perhaps a better question would be:
What coding projects are other forum members most proud of?
 

vba_php

Forum Troll
Local time
Yesterday, 19:29
Joined
Oct 6, 2019
Messages
2,884
several projects I completed as coding challenges in the past few years now baffle me as I've never reused the code elsewhere.
is it still true that google holds coding challenges as a way to pick the best candidates to hire?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:29
Joined
Feb 28, 2001
Messages
27,001
The most complex project was a tracking system for software security maintenance for the U.S. Navy. We had 30+ users (variable over time), 80+ projects with people assigned to manage multiple projects' server, over 1500 servers (variable as we grew and as servers were replaced), half a dozen different operating systems (if you count LINUX, AIX, OpenBSD UNIX, and the different flavors of Windows Server as different), and about 30-40 new designated patches per month. Patches could be in multiple states (newly announced, pending assignment to an admin, pending system downtime, pending permission of manager, pending arrival of actual patch for particular vendor's system, patch failed to run, temporary waiver, completed, determined non-applicable, ... a total of about 20 statuses overall.) In the end, patches were either applied or not, but the various reasons WHY they were not applied were important to the security staff.

Any person on the project could perform the patch and update the status of that server with respect to a given set of patches. Any OTHER person on the project could query the state of their project to see what still needed to be patched. And we could make a summary report that we could send to the project manager (who was a business type, not a system admin) regarding patch activity.

The DB was able to import info on new servers from spreadsheets and was able to produce spreadsheets to summarize project/server status. We also had a help document in MS Word that, if you clicked the HELP button, would be opened read-only to a specific bookmark related to that form or that function - and you could select bookmarks by topic. We also sent e-mail to project managers when needed, and if necessary, we could encrypt the messages over Outlook.

At any given time, we might have a total of over 500,000 pending or completed actions on record. We archived the oldest closed notices every month but "oldest" was relative to the last closure. We kept records for six months after the last closure for a particular notice and archived those records to a different file for long-term retention.

There were five base tables that had a "W" relationship. I can't draw it here, but I can summarize.

Notices - were applied to individual servers via a junction table.

Users - were assigned to projects via a junction table but they had to have the ADMIN role associated in the user table. Three teams of admins were assigned to Windows; three teams were assigned to UNIX; a few people were assigned to VMWare systems.

Servers - were associated to projects via a junction; were associated with notices via a junction. The latter junction (server/notice) was the big kahuna that grew to well over 500K records.

Everything was normalized to a fare-thee-well and we had some five-way joins in that mix because of the complexity of the relationship.

Since it involves U.S. Navy computer security, two factors apply. First, it is still in use to a lesser degree and is covered by certain regulations regarding publiation, and second, I don't own the code. Therefore, I can describe it in general but cannot publish it. Third, being cognizant of that fact, I didn't take copies home with me.
 

vba_php

Forum Troll
Local time
Yesterday, 19:29
Joined
Oct 6, 2019
Messages
2,884
as always Richard, your posts prove to be very insightful. thanks! that was a good read.
 

vba_php

Forum Troll
Local time
Yesterday, 19:29
Joined
Oct 6, 2019
Messages
2,884
Here's one such topic I worked on earlier this year : https://www.access-programmers.co.uk/forums/showthread.php?t=303974
you've got some talent dude! those forms in your app are quite pleasing, in terms of a user interface. I assume you run your own consultancy?

That's a great thread...I'm sure I'll look at that a few times. Lots of good stuff in there. Although, that might be giving away a bit too much information to people who should really pay for advanced programming work. But that's your choice of course.

I see a lot of free information being given away for apps that are used by beginners and non-technical people. for instance, excel and access. there are multiple forums on the web for this too. while there *are* forums for web languages as well, I don't see near as much free help being given for client side of server side languages. and I rarely see anything (like code snippets) advanced posted on forums in the form of web languages.
 

isladogs

MVP / VIP
Local time
Today, 00:29
Joined
Jan 14, 2017
Messages
18,186
I give away a lot of complex code. I do indeed have my own company.

There are several reasons why I give away code including:
a) Partly this is pay-back for when I got a lot of help as a beginner some years ago.
b) Other developers may be able to adapt the code for their own innovative projects
b) Sometimes the apps are cut down version of commercial apps and additional sales may occur as a result
c) Also doing so can lead indirectly to unrelated contract work
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:29
Joined
Feb 28, 2001
Messages
27,001
To be clear, I offer time here, limited though it may be, because this site helped me when I was setting up that monster I described earlier. I think of it as "paying it forward."
 

scott-atkinson

I'm with the Witch.......
Local time
Today, 00:29
Joined
Aug 31, 2006
Messages
1,622
I have limited Access VBA skills, built a complex database about 12 years ago, it still works Iv'e been told, but havent touched Access since.

Now do a lot of VBA coding in Excel, and the most challenging project I've completed is building a Forecasting Replenishment workbook for multiple Distribution Centres, with lots of forecasting methodologies, and replicate it 13 times for different functions and build in self event management, updating process.

Took me about a month to complete end to end..
 

kevlray

Registered User.
Local time
Yesterday, 17:29
Joined
Apr 5, 2010
Messages
1,046
I have never created anything particularly complex, but I did have to look at an Access DB that someone else had created (and no longer with the organization). It was reading Excel files, doing great number of calculations (at one time we discovered some of the calculations were incorrect, former employee?) and exported to Excel creating sheets and graphs on the fly. That took a while to understand the flow.
 
Last edited:

scott-atkinson

I'm with the Witch.......
Local time
Today, 00:29
Joined
Aug 31, 2006
Messages
1,622
I have never created anything particularly complex, but I did have to look at an Access DB that someone else had created (and no longer with the organization). It was reading Excel files, doing great number of calculations (at one time we discovered some of the calculations were incorrect, former employee?) and exported to Excel creating sheets and graphs on the fly. That took a while to understand the flow.

I know that feeling, I inherit quite a lot of Excel workbooks where 'people' have created Code, and I have to try and fathom out what the hell they were trying to achieve... Scratches Head repeatedly... :banghead:
 

vba_php

Forum Troll
Local time
Yesterday, 19:29
Joined
Oct 6, 2019
Messages
2,884
and I have to try and fathom out what the hell they were trying to achieve... Scratches Head repeatedly... :banghead:
excel is easy for people to start using cuz one can open it up and immediately start typing crap into the cells. this is what a lot of small business people do cuz they need to write stuff down quickly.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:29
Joined
Feb 28, 2001
Messages
27,001
I have to try and fathom out what the hell they were trying to achieve...

This is exactly why when I was a development team leader three jobs ago that I told my team we were going to make code one day and if it tested OK, we would document it immediately after the test. AND where lines of code were involved in a way that would allow comments, we would place comments on no less than 50% of the lines. There were rules about meaningful comments and sometimes I would have to counsel them on leaving a useless comment.

Adam, you are correct that Excel allows folks to be sloppy. But sadly, so does any other system of development tools. Excel has no monopoly on that fine point. But I absolutely don't disagree with you on the tendencies to "shoot from the hip" and "damn the torpedoes, full speed ahead" mentalities.

Programming and developing robust, useful apps will require thought, not reckless advance. We are code commandos, not barbarian warriors rushing a barrier. We PLAN our way into a problem and take it down by well-selected actions. We don't bash in the door and tromp all over things. (Well, most of us don't...)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:29
Joined
Jan 20, 2009
Messages
12,849
AND where lines of code were involved in a way that would allow comments, we would place comments on no less than 50% of the lines. There were rules about meaningful comments and sometimes I would have to counsel them on leaving a useless comment.

With a requirement to place comments on half the lines I'm not surprised you got useless comments.

I cannot stand comments like:
' Increment counter
' Set value on variable
' Test for EOF
' Close Form

Excessive commenting makes code harder to read. Well structured code is largely self documenting.

I would much rather see a few lines of comments at the top of a sub or block of code explaining what it does.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:29
Joined
Jul 9, 2003
Messages
16,245
true, but good developers will create separate documents to explain the project flow anyway.
I recently coded a payment collection form with discounts at certain levels, discounts for club members, refund on canclation, but only if cancelled within 48 hours, and a couple of other special cases.
I coded it lovely, it all worked!

Went back to it a week or so later and it had me scratching my head! I always put comments in, to help me follow my own logic, but in this particular case it was so involved that there was no way just commenting could convey what was going on.

When you are in the zone coding, you can just bash it out, but looking at it later, I found it difficult to to understand what I had done. It took me nearly as long to draw up some flow charts and a presentation explaining it!

I know if I don't get the explanation of what I was thinking down somewhere, if I have to revisit it at a later stage it will take me forever to sort it out.



Sent from Newbury UK
 

Users who are viewing this thread

Top Bottom