Category Archives: Visual Basic

Visual Basic for Excel – Getting Started

Although I have been discouraged from learning anything about Visual Basic by some people…I am doing it anyway!

Visual Basic is a ‘high level’ programming language created by Microsoft. (Apparently high-level means that there is a strong abstraction from the details of the computer, which means it might use more ‘natural language’, should be easier to use, and might hide or automate significant areas of the programme to simplify. This sounds good to me, a beginner).

I have become interested in it over the years, purely because you can use it to automate things in Excel. After I had learned a bit of JavaScript, I went into VBA in Excel to try and make something and I saw that the language made some kind of sense – so I think I am ready to start learning a bit more about it. Note that you can learn the basics of Visual Basic without needing VB Professional, just because there is an editor inside MS Excel.

I’m going to start learning Visual Basic for Excel – or Excel VBA (Visual Basic for Applications) – and I’m going to try to use this http://www.excelvbatutor.com/

Apparently you can use VBA in a number of Microsoft Office applications, and not just Excel, so this could be even more useful than I originally thought.

Accessing the Editor

  1. Click the command button, Which is the one under ActiveX Controls. (Not the other button, which is under Form controls.) This inserts a command button, and if you click on it, it will take you to the Visual Basic Editor. Visual Basic Command Button

 

 

 

 

2.  Alternatively, you can click on Visual Basic in the Developer ribbon and start writing  functions inside the VB Editor

Visual Basic Editor

 

 

 

 

What the Visual Basic Syntax Looks Like

If you choose option 1, the command button, then when you double click on it, it will take you to the Visual Basic Editor, which will already have a bit of code in it, looking like this:

Private Sub CommandButton1_Click()

End Sub

You can write some text between these two lines to make the button do something

Visual Basic Excel

 

 

 

 

 

This will Fill up cells A1:A10 with that text. To see it in action, you have to click out of design view (on developer tab), then click on the button.

Where it says .Value it is because it is giving you the value of that cell. There are a lot of other things you can use other than .Value.  e.g.

  • .Select –  You could have Range(“A1:A4”).Select to select a  range.
  • .Font.Color – to change colour of font (uses RGB)
  • .Interior.Color – to change the colour of the cell background

NOTEthe annoyingness of having to remember that coding languages won’t accept my spelling of colour.

Declare a Variable

Like in other programming languages, if you want to save values  you will need to assign them to variables by

  1.  Giving the variable a name (no spaces or periods)
  2. Assign the variable a data type

You have to declare them before you use them.

If you give the variable type a value like

name=”Tracy”

– then data type is a string. If you don’t yet have a value, but you know that the name variable has to be a string, then in the Editor you can declare the variable like this

Dim name As String

(Trick – Dim name As String *10 – means that the max characters allowed for the name variable is 10).

Write a message Box

message=MsgBox(Prompt, Style Value,Title)

Visual Basic Message Box

 

 

 

Where message is the variable.

Prompt is the message in the box (“Click Yes to Proceed, No to stop”)

Style Value refers to the kind of buttons (vbYesNoCancel)

Title, is in the top of the box (“Login”)

You can also use an Input box.

name= InputBox(“Enter your name”)

 Some Other things you can do in Visual Basic for Excel:

  • Use control flow – with If/Else/Then statements
  • Use Loops with For….Next and Do…Loop
  • Select Case statements e.g. If it is A say “Apple”, if it is B say “Banana”. Etc. The shortcut way of doing a lot of if/else statements
  • Cut up strings using LEFT, RIGHT, LEN, MID. Etc.

To be honest – I don’t think I have yet learnt anything which I couldn’t do with normal Excel formulae…but I am only halfway through the lessons on that site, and the second half promises some useful applications, so we will see.

In other ‘learning to code’ news

  1. TreeHouse – still very impressed by the high quality of the videos, and all I am learning. Expect to add a new module to my site over the weekend in practicing my PHP.
  2. Codecademy – I lost 10 days of my streak (booo), and I hate to say…. I think I might have gone off it a bit. No, not because I lost my streak, but maybe the TreeHouse comparison isn’t helping. Also, I have given up on the Python section. It started off ok, but it is very repetitive and boring. Sometimes it doesn’t make sense until you go to the next lesson. There are some frustrating bugs. Oh, and yes, I lost quite a lot of work due to their database problem the other week. I seem to recall that the jQuery section was kind of crap like this at one point, and then they rewrote it…so I might go back in later. I am going to stop on Python and start anew with Ruby and see if I can reignite the love for Codecademy.
  3. I have had stories published on Commonwealth Banks, Women in Focus and SEOMOZ this week.

 

Leave a reply