PDA

View Full Version : Solved: Use a Formula in code



Djblois
11-05-2008, 09:38 AM
I am trying to parse some text from a worksheet and put it in a form. However this will not work? Can someone help fix it. I already have the with me so that is not it.

.lblYearSC.Caption = Application.WorksheetFunction.Text(.Right(Range("B3"), 4))

GTO
11-05-2008, 10:05 AM
Greetings Daniel,

You mention that that there is a 'With ...' someplace above the single line of code provided. Assuming its something like

With UserForm1
'...statements...
.lblYearSC.Caption = Application.WorksheetFunction.Text(.Right(Range("B3"), 4)))
End With

...then what's the ".Right" ?

Would this work(?):
With Userform1
'...statements...
.lblYearSc.Caption = Right(Range("B3"),4)
End With

Hope this helps,

Mark

Djblois
11-05-2008, 10:19 AM
That worked!!!

Djblois
11-05-2008, 10:33 AM
How do I know when I need to use Application.WorksheetFunction and when not to?

Bob Phillips
11-05-2008, 10:46 AM
Application was how worksheet functions were invoked prior to Excel 97, and has been retained for compatibility. Although both work, there are some nuances in the way in which they work.
• WorksheetFunction,and Application.WorksheetFunction, supports the "auto list members" option, whereas Application alone does not.
• Not all worksheet functions are exposed to VBA. Functions not exposed by the WorksheetFunction object usually have a VBA equivalent (e.g., Left, Year), but they do not necessarily work in exactly the same way.
• Functions within Add-ins, such as the Analysis Toolpak, cannot be called with Application or WorksheetFunction.
• WorksheetFunction is faster than Application, by an order of circa 20%.
• Errors are handled differently. When a function is called directly using Application, such as Application.VLookup, the result is a variant containing an error. When called directly using WorksheetFunction, for example WorksheetFunction.VLookup, the function will raise an error. Both can be managed, but in different ways



Dim res As Variant
res = Application.VLookup(1, Range("A1:B10"), 2, False)

res = WorksheetFunction.VLookup(1, Range("A1:B10"), 2, False)


Here, the VLOOKUP function is being invoked to lookup a value that does not exist in the range A1:B10. The first method, Application.Vlookup, returns an error to the variable, whereas the second method raises an error.
Application calls can also be trapped using the IsError statement


If IsError(Application.VLookup(1, Range("A1:B10"), 2, False)) Then
Debug.Print "error"
End If


In a similar manner, WorksheetFunction calls can be wrapped in error handling code to trap the errors.



On Error Resume Next
res = WorksheetFunction.VLookup(1, Range("A1:B10"), 2, False)
On Error GoTo 0
Debug.Print res


In this instance, res will be an empty variable.