Excel functions in VBA

[Kimed]

Hi,

I've tried to use some functions that are not in VBA, but are listed among "available" in Excel, like Max, Floor, and few others. The compiler gave me a "Sub or Function not defined" error, though. As far as I understand, a module must be checked in VB's Tools/References dialog, but for the life of me, I can't determine which one. I have currently checked:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Office Runtime 1.0 Type Library
Microsoft Visual Basic for Applications Extensibility 5.3

Among other ~400 references I couldn't find any title more or less relevant to what I need, and I'm not sure it would be wise just to check them all and see what happens. Well, I might, but I'd rather ask first.

Thanks.

 

[xlbo]

How are you trying to call the function - if you are coding VBA in excel and are trying to use excel functions, you should not need any extra references

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Hi,

you need to reference your function as follows:

Application.WorksheetFunction.Max

If you just put Max, Excel thinks you're referring to a VBA Function or Sub.

Cheers,

Roel

 

[GlennUK]

You can actually shorten

Application.WorksheetFunction.Max

to

Application.Max

if you want.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.