Result of DAY formula as variable

[bukimi]

Hello!

I got a formula in my cell assigned via VBA:

ActiveCell.FormulaR1C1 = "=DAY(MAX(values!C8))"

It finds the highest number of day among all values in the column.

I want it to be my variable without having this value in a cell.
I looked into Worksheetfunction options, but it seems that DAY formula isn't available there. Is there a workaround for such cases or I'm forced to use a cell for it (and then empty it)?

 

[FormR]

Hi, you could try:

myvariable = day(worksheetfunction.Max(sheets("Values").Columns(8)))

I looked into Worksheetfunction options, but it seems that DAY formula isn't available there.

VBA has it's own native Day() function.

 

[bukimi]

Wow, since I was already using DAY in VBA, I'm ashamed for not thinking about that. Never thought about mixing native function with Worksheetfunction result.
Simple and smart.

Thank you! Very good lesson for me.