Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter March 2008

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Complete Excel Training Course

Got any Questions? Free Excel Help


How to determine which cells or functions are causing the error. In other words, debug formulas.

Some Excel features to keep in mind when debugging formulae are;

1) GoTo Error Cells. F5 and then click Special, or Edit>Go to..

2) Formula Auditing. View>Toolbars - Formula Auditing.

3) Excel 2002 or higher. From the Formula Auditing toolbar.Watch Window & Evaluate Formulas

The Excel help is quite complete in detailing the above features so I will show you a fast way to debug formulae. Also see Articles abouttroubleshooting errors in worksheets in Excel  andHow to use the Error Checking Options button in Excel.

1) Select you formula cell and then click the VERY 1st Function name immediately after the = sign in the Formula bar.

2) Now click the Fx symbol left of the Formula bar, or go Insert>Function.

3) This will show each step/argument of the particular formula, so look for the error value. NOTE: the very bottom value is the nested formula result, while the left and below the last argument box is the value returned from that particular formula ONLY.

4) From the formula bar, select the next function in the nested formula and repeat step 3.




If you have ever used the CHOOSE Function in Excel as a Formula, you will know how handy the Function is in VBA. If you haven't, it's time to learn.

The Choose Function in VBA works the same as the Worksheet Function. That is, it returns a value based on the numeric value of the index argument, which MUST be above zero. The only difference is that in VBA, the Choose Function is not limited to 30 choices. However, having said that the text below from the VBA help is worth noting.

Choose evaluates every choice in the list, even though it returns only one. For this reason, you should watch for undesirable side effects. For example, if you use the MsgBox function as part if an expression in all the choices, a message box will be displayed for each choice as it is evaluated, even though Choose returns the value of only one of them.

If you don't know already, TRUE/FALSE in VBA returns a value of -1 and 0 respectively. This means we can use Boolean logic in the Choose Function BUT we MUST add +2 to the TRUE/FALSE to ensure the value is greater than zero. That is, TRUE+2=1 and FALSE+2=2.

Ok, onto some examples.

Private Sub CheckBox1_Click()Dim lVal As LonglVal = CheckBox1 + 2   Run Choose(lVal, "Macro1", "Macro2")End Sub

The above code will run Macro1 when CheckBox is checked (TRUE) or Macro2 if not checked (FALSE).

Ok, that shows how we can use Choose with Boolean logic, now lets look at how in can be used in a Macro loop. Note that we do NOT bother to loop through ALL cells in each range. That is, we make good use of Excel's built-in MUCH faster features.

Sub ChooseInLoop()Dim rRange As RangeDim lLoop As LongDim strValFound As String, strValReplace As String    For lLoop = 1 To 4        'Pass name to find to variable      strValFound = Choose(lLoop, "Bob", "Bill", "Ray", "Jan")        'Pass replacement name to find to variable      strValReplace = Choose(lLoop, "Bobby", "Billy", "Raymond", "Janet")        'Pass range to replace to variable      Set rRange = Choose(lLoop, Sheet1.Range("A1:A100"), _            Sheet1.Range("C1:D100"), Sheet2.Range("A1:A100"), _            Sheet2.Range("D500:E1000"))                 'Do the replacements     rRange.Replace What:=strValFound, _        Replacement:=strValReplace, _        Lookat:=xlValue, MatchCase:=False    Next lLoopEnd Sub

Until next month, keep Excelling

Got any Questions? Free Excel Help

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Excel Data Manipulation and Analysis

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help