General VBA question

[joe2324]

the more I study VBA in excel, I see it has a lot of uses in many different situations so mastering it seems like it would be a long process. My question is for those who use VBA on a daily basis, in what ways do you use it most? In my job, I've yet to come across a situation that requires VBA at the moment as I get most stuff done with built in excel functions so i'm curious to know how most people use VBA at work.

thanks,

 

[BengalMagic]

I have some VBA that is kicked off via Windows Task Scheduler at 7a.m and uses embedded SQL code to grab 400,000 records from the previous night, collates those records into individual workbooks for division, company, cost centre and branch and then emails each book to the appropriate recipient (49 of them).

All takes less than a minute.

Used to take three people until lunchtime every day to do the same thing.

 

[carpy1985]

2 Things Mainly:
A) For anything that involves repetitive tasks...
B) When your spread sheet has a lot going on - a userform can really help users to not mess up your spread sheet

 

[joe2324]

Thanks, I can def see connecting to a sql database and sending emails with data as benefical. This would be something very useful if we did not have access to sql server reporting services where you can automate subscriptions and send them out via email.

 

[Macropod]

Being retired, I don't have a day-to-day work need for vba.

Almost all the vba I write is for Word and is to help other solve their problems (eg document automation, transferring data from Word documents to Excel, or Find/Replace operations that are complex, span multiple documents and/or multiple expressions).

Word also comes with another 'language' for programming that gets used extensively - field coding. Fields are commonly used for performing mailmerges, linking Word documents to other files and for doing calculations within a document.

Sometimes, field coding and vba are both used in the same document.

 

[xenou]

Hi,

In my last job (in accounting) I had never typed a journal entry by hand (into our accounting system) in over three years. I created an Excel app to do that for me. Having this application also enabled me to do departmental allocations to dozens of departments, with thousands of lines, in a few minutes -- saving hours of laborious and error prone data entry. This one only one example of several that saved my department as much as a day's work, down to a few minutes instead.

One never really knows - the more you know, the more you can do. I customize dozens of workbooks that I use, in ways as simple as making sure I don't save the workbook with tabs grouped to things as complex as parsing data and re-displaying it in a way that the user requires. Just today I spent two hours writing a script to save a co-worker a repetitive task that takes about 15-30 minutes every month. So in about 6 months the return on the investment is earned and after that what takes up to a half hour now takes a few seconds (imagine the happiness that brings as well - now it takes a few seconds to get done).

I also leverage additional resources through VBA - Regex, direct manipulation of XML documents, email services, and running jobs during the night with scheduled tasks. Some of this cannot be done through the Excel user interface. I get most of my reporting done every day 100% automatically - quite honestly, I wouldn't be in my job without VBA as I couldn't stand the repetition, involving endless typing, copying, and pasting, that I see others dealing with on a daily basis. Though I do tip my hat to the hard work that these "average users" do.

Edit: this reminds me of the basic "lazy" nature of many programmers - I will work my fingers to the bone for hours devising some program to save me a few minutes. But in reality, if the savings is significant enough -- even just ten minutes -- and you can "recoup" your investment within a year -- then you find yourself becoming more productive as the years go by as you start finding you are doing many things in seconds rather than minutes. I've even surprised myself at finding projects that I was just sure I had spent too long creating turning into very productive resources over the long haul. I suspect you are right though, that it takes time and effort to become expert in VBA. I read at least a dozen books and spent about three years honing my skills. On balance, I think it was worth it. One thing you get better at as time goes by is "generalizing" solutions so they can be used in different contexts - allowing you to easily adapt your VBA to other similar situations in your daily work.
ξ

 

[Fazza]

I use Excel VBA in many, many ways. Inlcuding some nifty database interactions.

To me, using Excel without VBA is like having a car and only ever pushing it to get places.

 

[taurean]

VBA is a really powerful tool. And it can be used to manipulate many other apps e.g. I have used Excel VBA to generate automated report that checks for pending items and then sends an email to concerned persons via Lotus Notes.

Some of the reports that I generate on weekly basis need standard formatting and information arrangement. It takes a while to set them at the beginning but once done you start reaping benefits of it.

It can be used to generate some standard csv / txt files (flat files) if your company uses them.

Then it can be used for something as simple as finding out all lines that have been "strikethrough" which can't be filtered straightaway.

You will realize its potential once you start using it. The more you use it then there's more likelihood of finding more options where it can be introduced.

 

[joe2324]

Thanks for the feedback. I have found a few simple tasks using VBA that have helped me out. Most of the reporting I do is automated through reporting services and sharepoint so I dont see how using vba would help me doing any reports, but it is has been useful for automating some basic tasks that normally would take 15 minutes. I've been able to write a few simple macros to help me out. I still have a long way to go, seems like a pretty steep learning curve.

 

[Fazza]

... seems like a pretty steep learning curve.

Yes, it is initially. It gets easier. It is worth doing.

Keep Excelling