Visual Basic for Applications

[Flechmen 0]

Well, since there's been so many threads recently about learning new languages, I thought why not a thread about learning a scripting language.

 

Visual Basic for Applications is what I'm learning in school right now. It's a pretty simple language, but also a bit of a useless one. But, it's an interesting introduction to programing. I've actually been having a bit of fun with it.

 

So, what VBA is, is a language mostly used in scripting Microsoft Office. However, it is used for other programs as well.

What the class I'm in, and what this thread will try to do, is teach how to script Office to do things it's not meant to do.

 

VBA in office is a bit of a controversial topic, because there's some people who think its great and want to keep it in future versions of Office, others that think its horrid and a tremendous security risk, and 90% of the users who didn't even know it existed.

Its also a very old function of Office. This aspect of the software has not changed at all since Office 97 came out. The book we're using in class was written in 2000 and is still applicable today.

 

So, let's get started with some basics.

Obviously, you will need Microsoft Office. Just about any version will do, I'm using 2007 myself so everything here will be based on that.

 

First, we need to tweak some settings.

Go ahead and open up Word. Click the Office button, then "Word Options".

Once there, click the checkbox next to "Show Developer tab in the ribbon" and click OK. This will give you a new tab in all Office applications.

 

The first thing we'll do, is record a simple macro. A macro is a script that automates something.

 

Click the Developer tab on the ribbon, and in the Code group, you should see "Record Macro". Go ahead and click that, and click OK on the box that comes up.

You are now recording your actions within the document.

Now, type "This is my first macro"; hit "Stop Recording" on the ribbon.

Click "Macros" in the Code group on the ribbon, a box should come up showing what macros you have recorded.

Click on the macro we just made to select it, it should be named Macro1. Click "Edit" off to the right.

 

This should bring up a VB editor window and the code that generates the macro we just recorded.

Let's take a look at the macro.

Sub Macro1()
'
' Macro1 Macro
'
'
    Selection.TypeText Text:="This is my first macro"
End Sub

So, what is going on here?

Sub Macro1()

This indicates that we have a sub procedure, and that the name of that procedure is "Macro1".

 

'
' Macro1 Macro
'
'

This is a comment, it does nothing. It tells whoever is looking at the code what it is. Comments can be anything, and I'll show you more later.

 

    Selection.TypeText Text:="This is my first macro"

This is what our macro is actually doing. It's typing text ("typetext") into the selection (where the cursor is), and the text it typed was "this is my first macro". Pretty simple, isn't it?

 

End Sub

End of the macro.

 

So, lets have some fun with this. In the VB editor here, we can tweak our macro to do other things.

 

Lets have it come up with a little message box saying it was successful.

The code for a message box is pretty simple. Start by entering the following:

    Selection.TypeText Text:="This is my first macro"
    msgbox 

When you hit space after typing "msgbox", you will see a little help popup. So, lets continue on.

msgbox "Great Success!",

After you put the , at the end, another list of options will come up showing what kind of buttons we want on our box. The default is "okonly", so we can simply put another comma here. I would encourage you to play around with the different options here and see what they do.

Lets continue

msgbox "Great Success!",,"woot"

With this, "woot" will be shown as the title of our message box. This is optional of course.

We won't worry about the other options for now. Hit the enter key and review your code.

Sub Macro2()
'
' Macro2 Macro
'
'
    Selection.TypeText Text:="This is my first macro"
    MsgBox "Great success!", , "Woot"
    
End Sub

To test it, simply hit F5 on your keyboard.

 

So, I'll leave that as our first lesson. I'll see how well this thread goes over before posting more.

[HuskyDog† 0]

I learned Visual Basic in highschool, for Windows forms applications. That's what Visual Basic 6 was all about. Horrible, horrible environment, made it easy to make really bad applications. But it was interesting for learning. Looks like there's not much difference between that and VB scripting for Office. I never tried that before. Might as well. I know Basic well enough anyway.

 

I thought you were going to be advocating Visual Basic for writing Windows programs in this thread. If that was the case, I'd have stated "No! Use VC++ or VC#!" They are much better suited for that kind of thing. But then again, that has nothing to do with this thread, so ya.

[Flechmen 0]

I thought you were going to be advocating Visual Basic for writing Windows programs in this thread. If that was the case, I'd have stated "No! Use VC++ or VC#!" They are much better suited for that kind of thing. But then again, that has nothing to do with this thread, so ya.

 

Goodness no, I know VB is a horrid language.

Still fun to tinker with

[AzariahWolf† 0]

I can build some pretty major thing in VB.NET. Been going through a course on it, and I've learned to do some pretty crazy things.

 

This is as application building program, by the way, not a way to mod Office.

[Flechmen 0]

This is as application building program, by the way, not a way to mod Office.

 

VBA (Office scripting) and VB.NET are two different things, yes. VBA is more aimed towards being a scripting language, has more program dependent hooks and such.

[Guest Wolfin]

/me believes the object structure between VBA and VB.net are different as well.

 

Although, VB.net is an equally bad language....in fact, .NET itself is really a bad framework, because, like Java, applications run in a managed environment and not truly native. This is bad because:

1) It severely hinders performance and increases overhead

2) Bundling the 3.5SP1 .NET framework adds 50-250MB to the size of your application (or, to your user's download at least), and adds a system reboot on all platforms except Windows 7. If you use .NET 4, even windows 7 users need to install that bloatware.

3) Your code is not as secure on .NET, and can be easily reverse engineered from bytecode (yes, all languages have this issue to some degree, but, .net is among the worst)

4) Cross platform usage is very hampered. Mono tries to let .net run on Linux, but, even it has issues with pulling that off and it doesn't always work.

 

In short...

 

...don't use proprietary software libraries!

 

/me gets off soapbox.

[HuskyDog† 0]

But on the other hand...

 

.NET allows for an easy programming environment. It ensures consistency of look & feel between multiple programs. It's a one-time installation. And it is really cross-platform. There are always issues with porting a library to another platform, but not being native really does have advantages in that area. Several native programs are much harder to port than several programs using a library, because you only have to port the library once.

 

I don't think .NET is horrible. I always loved the programming tools that Microsoft provided. Programming within .NET is actually a cinch, and I never noticed performance issues before.

[Guest Wolfin]

Just because you don't notice it; doesn't mean it isn't using more resources. And, .net isn't a library so much as a virtual machine. Yes, you do need libraries; but, I've always seen .NET as bloatware...granted with very pretty tools and a lot of press to feed it to people.

[Acton† 0]

Just because you don't notice it; doesn't mean it isn't using more resources. And' date=' .net isn't a library so much as a virtual machine. Yes, you do need libraries; but, I've always seen .NET as bloatware...granted with very pretty tools and a lot of press to feed it to people.

[/quote']

 

Wolfin, Knowing the age of most furries, I been play with code before many furries were born. I cut my programming teeth on Apple II Microsoft basic plus 6502 asembly. Then I moved on to C and when I moved to the PC Boardland C++.

 

.NET takes care most of the underlying task compared to "the good old days" when one had to make or buy all the underlying libraries. A developer will still to have deal with bloat in application development. Try writing you own socket connection API.

 

Here is an example just to down an RSS feed in C# .NET

WebRequest request = WebRequest.Create(URL);

request.Credentials = CredentialCache.DefaultCredentials;

HttpWebResponse responce = (HttpWebResponse)request.GetResponse();

Stream DataStream = responce.GetResponseStream();

StreamReader ResponceStream = new StreamReader(DataStream);

XmlTextReader XmlReader = new XmlTextReader(ResponceStream);

XmlDocument RSSDocument = new XmlDocument();

RSSDocument.Load(XmlReader);

XmlNodeList Channel = RSSDocument.SelectNodes("rss/channel");

XmlNodeList ItemList;

int Chloop;

int RecordCount;

int ChannelId;

int ItemLoop;

DataTable DbBufr = new DataTable();

DataRow Results;

for (Chloop = 0; Chloop <= Channel.Count; Chloop++)

{

RSSTitle = Channel.Item(0).SelectSingleNode("title").InnerText;

//if Channel title is in db do not add

 

 

If I had to write this myself in old Boarland C++ and compile native, I would have to write (write debug and validate), buy or bum a dictionary book size of code for the WebRequest, HttpWebResponce classes just to get to the Stream Readder and repeat the same devopment code to XML reader.

Then again back in my day connections was using bios to access the serial port.

For the rest you can get the express editions a spin at:

 

http://www.microsoft.com/express/downloads/

---

[Guest Wolfin]

Ouch; I feel a bit rebuffed

 

At any rate; I do, actually, use .net from time to time (heck, I'm a Microsoft Partner). And, knowing the age of most furries, students can grab VS 2010 (the full suite) over at http://dreamspark.com (Acton, being as old as he is, will need to pay for it....as will I...being the dense guy that didn't go to college).

 

/Wolfin

[HuskyDog† 0]

But I did not have to pay for it, since my MSDNAA account from my school was never deactivated.

 

I agree with Acton, though, and that's the stance I tend to take. I may not have been programming as long as Acton has (I actually don't know how long that is!) but I did use Borland C++, and for Java. I learned on QBasic. I've been programming for about 10 years. Back when I was learning C++, we did do a little bit with GUI stuff, but we only did so using some API from Carnegie Mellon university.

 

I started on Visual Studio finally on my own, years later, and with .NET (in fact, when a Microsoft employee friend of ours gave me C# .NET 2003). It was a far cry from before. I was sold.

 

I know what you mean by bloat. Java is incredibly slow from being run on the JVM. However, .NET doesn't seem to have that same slowdown. And I prefer it wholeheartedly over having to do native stuff. Native is fun sometimes, but I don't like it because it is lower level than is necessary and locks you into one specific platform, rather than having the advantage of some semblance of portability.

[MomCat† 0]

I took Basic in High School in the mid 70's. Each line of the program had to be filled in on a separate scan card with a num.2 pencil.

[Guest Wolfin]

MomCat's coolness gains another 5 points

[Acton† 0]

I took Basic in High School in the mid 70's. Each line of the program had to be filled in on a separate scan card with a num.2 pencil.

 

That's beats me,

When i took basic in 1978 we ether used the teletype or 80 line terminals we the computer club built and use acoustic couplers to connect them to the school's main-frame at 300 baud.

[HuskyDog† 0]

*basks in the glow of retro computing*