Converting Cell Formula (MAX) to VBA Formula

[Rufus Clupea]

Hi Folks,

Just a quickie (I think/hope)...

I had a formula in a spreadsheet cell:
=TRUNC(MAX(((MAX(E5-5,(E8-4)))/10),1))

that I needed to move into code, i.e.:
Sheet1.Range("J18") = TRUNC(Max(((Max(E5 - 5, (E8 - 4))) / 10), 1))

The VBE/Compiler doesn't like it in this form.

After reading several similar questions/threads, I've tried:

• Putting Sheet1.Range and quotes around E5 & E8
• Putting WorksheetFunction. in front of the 2 Maxes
• Using: MyValue = Application.WorksheetFunction.Max()
  

but I don't think I'm implementing it correctly. (I tried defining it as a function).

Public Function MyValue()
MyValue = Application.WorksheetFunction.Max()[FONT=verdana]

Sheet1.Range("J18") = TRUNC(MyValue(((Myvalue(E5 - 5, (E8 - 4))) / 10), 1))
[/FONT]

I've also tried every combination of the above that I could think of.

I'm stumped.

 

[Fluff]

Re: Need Help Converting Cell Formula (MAX) to VBA Formula

If you just want the value, try

Range("J18").Value = [TRUNC(MAX(((MAX(E5-5,(E8-4)))/10),1))]

 

[Rufus Clupea]

Re: Need Help Converting Cell Formula (MAX) to VBA Formula

Wow, this is the first time I've seen that notation!

Of course it worked; I'm just not sure why...
The page I first looked it up on says:

... the square brackets are a replacement for the Range/Parentheses/Quotation Marks construct.

OK, then it goes on to say:

It can be used on either side of the equal sign.

Huh? I'm having a little trouble getting my head around/understanding that.

Guess I've got some more reading/hair-pulling ahead...

Thanks for the tip.

Now, how do I ask this without sounding like a jerk... Is there another different solution I should know about for similar situations in the future?

 

[Fluff]

Re: Need Help Converting Cell Formula (MAX) to VBA Formula

With VBA there are always different ways to do something
The [] are a shorthand version of

Range("J18").Value = Evaluate("TRUNC(MAX(((MAX(E5-5,(E8-4)))/10),1))")

 

[Rufus Clupea]

Re: Need Help Converting Cell Formula (MAX) to VBA Formula

Aha, YES!

Odd that neither of these... techniques(?) are even mentioned in the VBA books I have (or maybe not?)

Thank you.

 

[Fluff]

Re: Need Help Converting Cell Formula (MAX) to VBA Formula

You're welcome & thanks for the feedback

 

[Rufus Clupea]

Re: Need Help Converting Cell Formula (MAX) to VBA Formula

That should have been...

Odd that neither of these... techniques(?) are even mentioned in the VBA books I have (or maybe not so odd?)

I think I'm collecting quite a bit of feedback for authors/potential authors (having done some writing myself in the distant past... ) Lots has to do with their indices (and things that should be there, but are hidden in obscure places... )