Back to Excel Newsletter Archives Back to Excel Newsletter Archives
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.
See Also: EXCEL FORMULA ERRORS
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 SubUntil next month, keep Excelling
Got any Questions? Free Excel Help
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help