Hazza's excel vba tutorials - part 1: introduction, etiquette, hello world

[hazzacanary]

EXCEL VBA TUTORIALS

PART 1 - INTRODUCTION - layout, cell references

what is excel vba?

It is a version of microsoft's vb (visual basic) programming language, embedded into every install of microsft excel.
The acronym vba stands for visual basic for applications, and it is inculded with every office program.

why is it useful?

It can be very good for automating certain more menial procedures, or offering more powerful functions than can be found in the
forumla bar. It is also fairly accessible in a workplace or public institution (school, university) as almost
everyone uses microsoft office!

Cons?

It isn't very useful outside of excel!

YOUR FIRST PROCEDURE

lets start with the usual hellow world application. Through doing this, we'll learn some of the crucial basic
elements of excel programming. Anyways, on with the programming.

To get to the vba window in excel, either go to tools->macros->visual basic (office 2003 or older) or go to developer ->visual basic (office 2007 or newer)
then we want to click insert-> module

Ok, on with the code. we're going to create a hello world application that prints the words "hello world" into cell A1/R1C1 on our worksheet. It goes something like

this:

Code Select Expand



sub helloworld()

cells(1, 1).value = "hello world!"

end sub



ok, there a few things to notice there. We'll start with the first line:

Code Select Expand



sub helloworld()



Notice how it started with sub? every procedure in excel vba has to start with either "sub" or "function", depending on what it will be used for (functions are for

calculations, sub for processes). These "subs" or "functions" can also be "public" or "private" or on their own, although I can't recall why at this moment in time! We

also have to name the sub in this line (we called it "helloworld" here). now for the second line:

Code Select Expand



cells(1, 1).value = "hello world!"



there's a lot to learn from this line. The first part is one of the two ways you can refer to cells in vba; a cells(i, j) reference, or a range() reference. I use cell

references most of the time, although both types have their ups and downs. ok, the next part:

Code Select Expand


.value =


This is merely what you're "doing" to the object you selected before it. Here, we selected a cell, and with the ".value" command, we're telling it what its value will

be. However, like many commands in any language, you can't use ".value" with out an = after it (and something for it to actually be equal to!)

Code Select Expand


"hello world!"


Like many other languages, any raw-data must be enclosed in quotation marks. I think either " or ' is fine.

Code Select Expand


end sub


This final command goes hand in hand with the starting command. In excel vba, you have to finish what you started. This means that you have to end every code with

either "end sub" or "end function", depending on what you started with. Like with the start, these subs/functions can also be "public" or "private" or simply on their
own.

Well that's it for the first vba tutorial. I don't know when I'll get around to doing part 2, but keep pestering me and I'm sure it will be done eventually. If you

have any requests for part 2, please dont hesitate to post them here. Anyways, thanks for reading, I'll see you around!