Category Archives: VBA

Using VBA in Excel

After the last post, I got a couple of emails asking about using VBA in macros.
Remember that I am a programmer at heart, so there might be ways to do things using ‘raw’ excel functionality. I just find it easier to cut code.
My best advice on getting started is  is to use the macro recording function and then look at what gets recorded. Excel is writing VBA. So, with a little bit of knowledge it is possible to do quite complex things.
If I take the lines from the working version I will explain them a section at a time:

Sub trimit()

The macro is a VBA subroutine. The keyword Sub declares a subroutine Trimit() gives it a name. The last line is END SUB Each subroutine is bracketed by these lines allowing multiple routines to reside on the same piece of source code.

x = 1

If VBA finds no keyword at the start of a line it assumes the word LET. Therefore this line is creating a variable called ‘x’ and assigning a value to it of 1. (Read it as LET x = 1).  LET is the original BASIC command to assign a variable with a value. The variable is on the left of the equals sing, the value being assigned on the right.
The next line is almost exactly the same, except it declares a second variable called ‘y’.

y = 1

The fourth line is yet another assignment, but this is also using TWO VBA functions, rather than a specific value.

tvar = trim(Cells(y, x))


This means the line starts LET tvar =
I then use a Trim function. This removes any extra spaces from whatever is passed to it.

What is passed to it, is another function Cells. This takes two arguments. The y position (Row) and the x position, Column of the current sheet.

This means that 1,1 is cell ‘A1’ NOTE: The row is the first argument, and the column is the second.

If you’re using Excel, you already use functions. Functions are a useful piece of code that returns a result. EG: SUM([RANGE]) is something we all use. SUM is passed the Argument of a range and returns the value of all of the cells in that range added up.

 

Okay, that’s the first lesson. Hopefully, you can find where to create a macro/ VBA subroutine and understand how to assign a variable. The remaining part of the explanation will be along in the next few days.

Compile Error: Wrong number of arguments

SQL Data juggling.

Excel is a reasonable tool for juggling data from system to system and it has VBA behind it to make life easy if you can knock up a quick macro. So I thought …

As it happens, I haven’t needed to do too much data fiddling in the last year or so and I know I have become a little rusty. This sent me around in rings.

A quick export, fix a dozen rows and then import it back in.

The data would not go back in using the flat file import export tool for MSSQL. Truncating error.

I needed a quick macro I have written a dozen times to trim every cell in the sheet.

All of a sudden I am popping a compile error:

Compile Error: Wrong number of arguments or invalid property assignment.

What could I be getting wrong? Surely they haven’t changed the trim function in VBA? comment lines out, change things around, add a message to check what is going into the function.

Blushing now on how dim I was, I had to share this.

A) Because I have no pride and it made me chuckle

B) Because I realised that a lot of people can’t easily write a macro from memory to trim every cell and they might appreciate a working version.

What’s the difference?

Notice for some dim, brain out of gear reason I gave the macro the same name as the Trim function. How many years programming knowledge? 40 years! I fell into using a reserved word for something.

Below is the real code in case anybody wants it:

Sub trimit()
 x = 1
 y = 1
 tvar = trim(Cells(y, x))
 Do Until tvar = ""
         Do Until tvar = ""
            Cells(y, x) = tvar
            x = x + 1
            tvar = trim(Cells(y, x))
         Loop
         x = 1
         y = y + 1
         tvar = trim(Cells(y, x))
 Loop
End Sub