Thread:Coolest/MostUseful Thing You've Ever Coded in VBA

[dk54]

I may be flamed for this (maybe its been posted before)...

...But it's something I really want to get some input on. I really have no idea what the upper limits of VBA are.

What are some of the most useful macros/applications you can code in VBA?

Coolest thing you've automated?
Can you do things like data-mine websites or third-party softwares?
Coolest thing you've inserted into OLE? (What kind of power does this hold?)

 

[Cindy Ellis]

I've done a few things that have pushed me to my limits of Excel VBA knowledge...I doubt that it's really pushing Excel hard at all. The following isn't intended to sound like bragging, it's really a testament to what Excel can do if you're willing to learn how to tell it what you want.
I don't know if any of these are cool. I think a bit like an engineer, so sometimes I think things are cool that nobody else does .

I wrote a macro that analyzed nearly 9000 files automatically (~ 1800 rows by 84 columns each, 1500 data files from each of 6 hardware systems) to assess the consistency of data over the duration of a system reliability test. The macro iterated over several levels of sub-directories to ensure that all the applicable files were included, opened each file, extracted and calculated 50 pieces of summary data for each one, appended it to a summary file, closed the data file, and went on to the next file. Each file took only 4 or 5 seconds for the entire process, but that still meant around 10 hours of continuous macro run time.
It also meant that I finished in a weekend a process that had previously taken several weeks!
I also automated the data reduction for what we call "burn-in" cycles from one of our systems. The macro included data summaries and charts of several parameters, and was used for more than 5 years by our manufacturing department (upwards of 1000 systems, 20 to 30 cycles each) without needing modifications except when they changed versions of Office. The overall code was around 28 pages printed out. It would be much shorter now, because I code more efficiently now, but sometimes you have go with "if it ain't broke, don't fix it".
More recently, I was able to automate a process of opening, extracting, parsing, and summarizing data from around 20000 pdf files. It was just a one-time thing, but allowed the data to be extracted over the course of a couple of a day or two rather than weeks or months.

Other than the one for manufacturing (which was before I knew about MrExcel) this site and its contributors deserve a lot of credit for my success with VBA.

Cindy

 

[Ruddles]

VB rather than VBA. Where I used to work we had 400+ servers, each of which did a nightly backup, some of which ended with various warning and error messages. The guy responsible for checking the backup logs used to spend all day every day attaching to each server in turn, finding the log file (which could be in one of three or four different folders), going to the end of the log and scrolling up to see how the latest backup had ended.

He was lucky to get through anything like a hundred a day - considerably fewer if he had to keep stopping to investigate and correct problems - so it was often in excess of a week before we noticed that backups weren't working on some machines.

So I wrote a program which attached to each server in turn, extracted the latest messages from the log file, worked out how old the messages were and whether there were any warnings or errors, then added the summary details to a daily Web page on the company's intranet.

The program was scheduled to load and run via Windows task Scheduler at 6am every day (after all the backups had finished) and by the time we got in at 8am all we had to do was check the Web page by scrolling down and looking at the red-amber-green blobs. It turned a job which one man couldn't hope to get done in a 37-hour week into a task which he rattled off in less than an hour.

Potential saving to the company at a time when jobs were being 'rationalised': probably 0.9FTE = £25k+ per annum.

 

[delaneyjm]

It's pretty tame compared to some other posts but it got me some serious commendations from my superiors across the country.

There was an individual who would receive a list in a CSV file of all the helpdesk tickets that hadn't updated in a while. His task was to look at all the tickets and generate an email to each tech with a list of their tickets that hadn't been updated.

Enter me, who wrote a simple VBA script that uses AutoFilter and a loop construct plus a touch of LDAP lookup to make sure the right person is getting selected and then generating the emails into the person's Drafts folder for review. Before, it was sometimes taking the individual up to 3 or 4 business days to generate all the emails. Depending on how big the file was, now takes 4-7 seconds.

 

[Ruddles]

Before, it was sometimes taking the individual up to 3 or 4 business days to generate all the emails. Depending on how big the file was, now takes 4-7 seconds.

It's always best to wait until Someone Important notices how much time it's taking to do something manually before you step in and automate it - that way you get much more kudos!