VBA in Excel

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

Posts: 38
Joined: Fri Aug 15, 2008 5:51 pm UTC

VBA in Excel

Postby CortoPasta » Thu Sep 17, 2009 3:05 pm UTC

I've been using VBA in MS Excel to write a lot of macros at work, and I was wondering how far I can go with that language/IDE. Is anybody here familiar with it? I'm just wondering how much I can do with it before I hit a dead end. I already know it can be used to open and manipulate other spreadsheets, but can it be used to access other programs or files? If not, does anybody know a language that's powerful enough to let me open and manipulate other programs and files?

Posts: 7
Joined: Wed Feb 04, 2009 5:58 pm UTC

Re: VBA in Excel

Postby bpsp » Thu Sep 17, 2009 10:06 pm UTC

The short answer to this is yes - you can do a lot of stuff using VBA in excel.

Recently at work I've done things such as read PDFs and extract certain pages and words, crawl through websites using internet explorer, and manipulate word documents.

In general, if you need to do something automatic in windows, you can probably learn how to do it through some quick googling. I spent an hour googling "vba adobe pdf" to learn how to read PDFs word by word in VBA.

If you wanted to really be an all-star windows programmer, I would recommend not pursuing VBA, but rather .NET programming, which I also know only a little about. Microsoft stopped supporting VBA in favor of .NET things. So if you want to really dedicate yourself to windows programming, you should pick up a book on C#. You can definitely manipulate pretty much anything using that.

Sorry for the rambling

User avatar
Posts: 1378
Joined: Wed Mar 26, 2008 1:16 pm UTC

Re: VBA in Excel

Postby Cleverbeans » Fri Sep 18, 2009 11:41 am UTC

I use VBA for integrating data in AutoCAD and Microstation (CAD software) with Excel spreadsheets on a pretty regular basis. The main reasons I use VBA is the ability to easily open and manipulate Microsoft Office applications from other VBA enabled software, it's integrated with software I already have so I don't need to purchase any other software, and because the CAD support team I work with doesn't allow code from languages besides VBA and AutoLISP.

A very common task I perform is to open a drawing, collect relevant information, open Excel, and dump the data to a worksheet with specific formatting. VBA can a very frustrating language to work with because of it's bulky syntax and weak native container objects. Since most projects done with VBA tend to have limited scope the bulk can really hurt your turn around time compared to say, AutoLISP. Also with MS officially ending VBA for non-Office applications and transitioning to .NET the need for VBA will diminish over time.

That being said I am excited about the switch to XML for MS Office documents and have been using Python to do a lot of my personal work that doesn't need to be used by the rest of the company. You may want to invest some time getting to know the XML format and another scripting language like Python or Ruby.
"Labor is prior to, and independent of, capital. Capital is only the fruit of labor, and could never have existed if labor had not first existed. Labor is the superior of capital, and deserves much the higher consideration." - Abraham Lincoln

Posts: 38
Joined: Fri Aug 15, 2008 5:51 pm UTC

Re: VBA in Excel

Postby CortoPasta » Mon Sep 21, 2009 5:53 pm UTC

I've been looking at ways to branch out and automate more tasks at work, so maybe I should start looking at .NET more. Python would be cool to, but I'm afraid you lost me when you started talking about XML formats :-/

User avatar
Posts: 1611
Joined: Tue Jan 15, 2008 3:23 am UTC
Location: Wagga, Australia

Re: VBA in Excel

Postby mrbaggins » Thu Sep 24, 2009 8:10 am UTC

XML is just a way of saying how to categorise and format the data.

It used to be that programs like Word would work in a completely different (And godawful) way.

Now, if you pull apart a docx file (XML Document - The new Microsoft Word file) it is, in a very simplistic way....

Code: Select all

    <bold>Page 1 of 2</bold>
    <Paragraph 1>And so begins some stuff</Paragraph 1>
  <footer>Authored by Baggins</footer>

Not only is it really easy to see what type of info everything is, you can see what it is a part of, how it's formatted and it's in a standard form, not a made-by-microsoft one.

The ML in XML means Markup Language... It's the 'Markup' between plain text and formatted stuff. It's the same as the ML in HTML, which marks up HyperText into a format used by web browsers to display web pages (Which is why they also look similar).
Why is it that 4chan is either infinitely awesome, infinitely bad, or "lolwut", but never any intermediary level?

Return to “Coding”

Who is online

Users browsing this forum: No registered users and 7 guests