Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    19

    Unanswered: What's so great about VBA in Excel?

    Forgive the cynical title above, I just want to know from those of you who use VBA in Excel on a regular basis... Why?

    I'm curious to know what is the purpose of having an excel sheet perform VBA, and have it have a user form, etc.

    If someone could explain to me why they have user forms on their excel spreadsheets, I think that would help me understand how to use this feature in Excel.

    Plus, I feel kind of sheltered because everyone at work knows me as the person who is the "Excel Master", and now my office given title is kind of joke if I don't know VBA for Excel. (Okay, not really, but I really do want to know how to use VBA within Excel)

    Also, any websites that have screen shots on how to use VBA within Excel would be much appreciated.

    Thanks!
    I hope this post isn't too much of a waste of your time.

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I wrote Excel VBA and Access applications before I retired from a financial institution. It helped to have employers who encouraged skill development and paid for the books and classes I took to further my programming skills. I think you need a positive attitude about VBA and ask yourself “how can I use it in some practical way to make my job easier?”

    One assignment was to use Excel and VBA to electronically read and compile monthly data from multiple sources, do calculations and present the data so that the Excel-spreadsheet-savy team could view the summarized results at the call-in team meeting, and see a region’s data or company performance as a whole. For one example of a form, they clicked on a command button that opened a form to select the State they wanted to view. That action un-hid the worksheet for the State. The pick-list form also gave the options to select All or check individual check boxes for as many states as they wanted to review.

    For this task, I needed to develop an application so that, after the data was collected, I just click on a button and the data is imported, scrubbed and formatted. Without VBA, this would have involved a lot of manual labor every month with the increased possibility of human error.

    In one man’s opinion, VBA programming increased the “fun” factor on the job. Start by learning from a book, such as “Teach Yourself Excel Programming with Visual Basic For Applications in 21 Days”, do the examples, study the objects and code in the examples you write so that you could re-create the example without referring to the book. Your goal is to get ahead on the job by building your skills, and when the opportunity comes to develop an automated Excel solution at the office, you will be the candidate to do it. Rewards should follow if you make your boss look good.

  3. #3
    Join Date
    Sep 2011
    Posts
    19
    I'm sorry I didn't mean to come across as having a negative tone.

    I am genuinely interested in learning how to incorporate VBA into Excel. I'll have to check into our company's online education books to see if they have any information there.

    I was reaching out to people who use VBA in Excel in hopes of getting a better idea of how I could use it in my spreadsheets. I currently rely on MS Access and using VBA through there. So I'm familiar with VBA, I just didn't know how to use it in a practical way for Excel.

  4. #4
    Join Date
    May 2012
    Posts
    1

    LOT's of great things!

    Something I'm currently using Excel for is a connection to Quality Center. In VBA, you can use references and QC has something called OTA.

    I have a form that I can login with my QC username and password. Using VBA, I mask the password so no one can see it. Then, the VBA creates a form showing the domain and projects available for me to connect to. I select my domain and project using the same form.

    The next page, I created options to download my test folders, testsets, tests, tests with design steps, defects and my requirements from the requirements module.

    Depending on my selection, each of these are downloaded into individual sheets which parse and process all the data into my daily reporting format.

    Why is this so great? My report is done with a few clicks of a button. Manually doing all this with excel would involve copying and pasting back and forth between QC and excel... and it will take HOURS. Just the parsing power alone is worth it.

    Another thing I do with VBA is I take all the status reports (whih are in excel) of my subordinates and parse them into an Access databae. VERY handy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •