Using Excel functions in a module - To Application.WorksheetFunction or not

[mikeymay]

I am building a tool that will need distributing to a number of users and there is the TRIM function being used in a module.

Now I have run into issues before where a tool wasn't being distributed and was only used by one person and different users have required the use of either of the following prior to the function

• Application.WorksheetFunction
• Application
• WorksheetFunction

How do I establish which of these will be required or is there a catch all option?

 

[Jaafar Tribak]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

The post is not clear to me

 

[mikerickson]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

TRIM is very unlikely to error. I would use the syntax WorksheetFunction.Trim("some string")

I think what you are referring to is that WorksheetFunction.Match will cause a VBA run-time error if there is no match, while Application.Match will return a CVErr value when there is no match.

With Match (and HLookup, VLookup, etc.) I would use the Application.Match syntax so the result can be tested with IsNumeric or IsError rather than having to write a VB error handler.

But TRIM is very unlikely to error so WorksheetFunction.Trim would be my preference.

 

[mikeymay]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

Thanks Mikerickson, I think that has answered my question.

 

[mikeymay]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

I am now experiencing this issue with the following

currMinAxis = WorksheetFunction.Min(Sheets("Daily Report").Range("DailyReport_AllAvgILS")) - 0.15

This is working as expected when I am using the file but my client is experiencing a 'Run-time error '1004': Unable to get the Min property of the WorksheetFunction class'

I am at a loss as to how this should work and what combination of Application and WorksheetFunction I should use

 

[mikerickson]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

Try qualifying the range even more fully.

Workbooks("Workbook1.xlsm").Sheets("Daily Report").Range("DailyReport_AllAvgILS")

or even
Workbooks("Workbook2.xlsm").Names("DailyReport_AllAvgILS").RefersToRange

and switching to Application.Min wouldn't hurt.

 

[mikeymay]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

Thanks, I'll give that a go.

 

[RoryA]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

You should only get that error if there are error values in the range in question.

 

[mikerickson]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

It looks like another situation where WorksheetFunction causes a run-time error, but Application returns an error value.

 

[RoryA]

Re: Using Excel functions in a module - To Application.WorjsheetFunction or not

That's always the case, as far as I am aware.