Wednesday, January 25, 2006

Debugging VBA

From Wills page a link to J-Walks page regarding debugging VBA
John discusses typical errors:

Types of Errors

There are basically three types of errors that can occur:

* Syntax errors:A variety of errors related to entering the code itself. These include incorrectly spelled keywords, mismatched parentheses, and a wide variety of other errors. Excel flags your syntax errors and you can't execute your code until they are correct.
* Run-time errors:
These are the errors that occur while your code is executing. There are many, many types of run-time errors. For example, if your code refers to an object that doesn't exist, you'll get a run-time error. Excel displays a message when there is a run-time error.
* Logical errors:
These are errors that occur through faulty programming. Logical errors may or may not cause a run-time error. In many cases they will simply produce incorrect results.
... and plenty more helpful information there.


Posted by: Jethro | 11:59 PM |

Saturday, January 14, 2006

De-Lurking Week

Belatedly I am writing a post to let you know that it is International De-Lurking Week
So if you read this site, regularly or not, then come on - comment!

Say who you are, why you read or what colour the sky is? I don't really care what.

Just comment.

I know theres 4000 unique readers a month hitting the www.spyjournal.biz website so someone must have something to say.
At the very least tell the other readers who you would choose out of Kylie or Gwen.


Image courtesy of Paper Napkins


Posted by: Jethro | 1:05 PM |

Tuesday, January 10, 2006

Zooming an Excel spreadsheet for named ranges

Holding down the CTRL button and scrolling with your mouse scroll wheel allows you to zoom in and out of an Excel spreadsheet.
One cool feature that this allows you to gain as a result is only noticed when scrolling to 25%.
Once you get there any named ranges that are entirely visible on the screen are displayed with a box around them and the name in bold blue writing in the range itself. Very useful!


Posted by: Jethro | 1:40 PM |

Wednesday, January 04, 2006

Reading complex formulas in Excel

Sometimes we end up having to write complex formulas in order to get the required logical results from a spreadsheet.
Here is one.
=OR(LEFT(OFFSET(data!$A2,0,$C$22-1),2)="SM",
LEFT(OFFSET(data!$A2,0,$C$22-1),5)="ANDER",
OFFSET(data!$A2,0,$C$22-1)="MILLER",
AND(OFFSET(data!$A2,0,$C$22-1)="BROWN",
LEFT(OFFSET(data!$A2,0,$C$17-1),2)="TO"),
OFFSET(data!$A2,0,$C$22-1)="JONES")

On its own this can look very confusing.
However once you take out the added complexity of the OFFSET functions (which just delineate the array to query) the formula can be restated like this.

=OR(LEFT(cell_reference1,2)="SM",
LEFT(cell_reference1,5)="ANDER",
cell_reference1="MILLER",
AND(cell_reference1="BROWN",
LEFT(cell_reference2,2)="TO"),
cell_reference1="JONES")

Ok this is a little less complicated but still quite confusing.
Lets now give the cell references names that make sense in the context of the spreadsheet. I will assume that the spreadsheet is a list of first and last names where cell_reference1 is last name and cell_reference2 is first name.

Now we can work out the logic of this formula.
This formula will return TRUE if any of the following conditions are true:
Last name begins with SM
Last name begins with ANDER
Last name is MILLER
Last name is BROWN, but only where first name begins with TO
Last name is JONES

Deconstructing this formula is reasonably simple. Building it can also be quite simple as long as the basic rules of formula syntax are followed. The most important thing to remember is actually the mathematical operators sequence.
The actual sequence these operators are performed in Excel is:
: (colon)
  (single space)
, (comma)
–
%
^
* and /
+ and –
&
= < > <= >= <>

Equally important is to use the correct function syntax. So in this case AND(logical1,logical2, ...) and OR(logical1,logical2,...) along with LEFT(text,num_chars) and OFFSET(reference,rows,cols,height,width) have been used.


Posted by: Jethro | 8:50 AM |