Microsoft Office Tutorials and References
In Depth Information
The following example demonstrates the first syntax of the Do Until loop. This example makes the code a bit
clearer because it avoids the negative comparison required in the Do While example:
Function ROWOFLARGEST4(c)
NumRows = Rows.Count
MaxVal = Application.Max(Columns(c))
r = 1
Do Until Cells(r, c) = MaxVal
r = r + 1
Loop
ROWOFLARGEST4 = r
End Function
Finally, the following function is the same procedure but is rewritten to use the second syntax of the Do Until
loop:
Function ROWOFLARGEST5(c)
NumRows = Rows.Count
MaxVal = Application.Max(Columns(c))
r = 0
Do
r = r + 1
Loop Until Cells(r, c) = MaxVal
ROWOFLARGEST5 = r
End Function
The On Error statement
Undoubtedly, you've used one of Excel's worksheet functions in a formula and discovered that the formula re-
turns an error value (for example, #VALUE!). A formula can return an error value in a number of situations, in-
cluding these:
• You omitted one or more required argument(s).
• An argument was not the correct data type (for example, text instead of a value).
• An argument is outside of a valid numeric range (division by zero, for example).
In many cases, you can ignore error handling within your functions. If the user does not provide the proper
number of arguments, the function simply returns an error value. It's up to the user to figure out the problem. In
fact, this is how Excel's worksheet functions handle errors.
In other cases, you want your code to know if errors occurred and then do something about them. Excel's On
Error statement enables you to identify and handle errors.
To simply ignore an error, use the following statement:
Search JabSto ::




Custom Search