CODEWITHSUNDEEP
vba
testo
testo

Reputation: 1260

VBA, min/max ... or other mathematical functions

I googled a lot, but I want to be sure:

Do I really need to use "Application.WorksheetFunction.Max" for the max-Function?

If yes, can I shorten this? Is there an overhead if I warp this long construct into a function?

Edit: I have removed the vba-access tag.

Upvotes: 13

Views: 81405

Answers (7)

testo
testo

Reputation: 1260

After I see that my question was unclear, I answer it by myself.

Some people did not know if I mean EXCEL or ACCESS. My fault to give the wrong tag. It was meant as a pure VBA question.

Second mistake: I was providing a EXCEL-way (Worksheet) for my question. But it was meant as pure VBA question.

I can not delete the question, but I like to do that.

So the answer is:

Public Function max(x, y As Variant) As Variant
  max = IIf(x > y, x, y)
End Function

Public Function min(x, y As Variant) As Variant
   min = IIf(x < y, x, y)
End Function

... is doing the job.

Sorry for wasting our time!

Upvotes: 24

hymced
hymced

Reputation: 688

Based on @cybernetic.nomad answer but corrected:

Public Function Max(x, y As Variant) As Variant 
  'Max = -x * (x > y) - y * (y > x) - x * (x = y)
  Max = -x * (x > y) - y * (y > x) - y * (x = y) 'same
End Function

Public Function Min(x, y As Variant) As Variant
   Min = -x * (x < y) - y * (y < x) - x * (x = y)
   Min = -x * (x < y) - y * (y < x) - y * (x = y) 'same
End Function

Upvotes: 0

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

As straight VBA you can do it in one line:

Max = -x * (x > y) - y*(y > x)


Min = -x * (x < y) - y*(y < x)

Upvotes: 0

this
this

Reputation: 1426

There is no context provided here but if you are basically looping and finding a max for each pair, then you're Doing It Wrong™, especially that you are in an Access database. Doing the same thing as a SQL query would be much quicker and easier to design and optimize than trying to come up with a VBA hacky workarounds which force you in a RBAR* mode, which is anathema to all things databases.

So, I would first look carefully at what the code is trying to do, and if this is basically just reams of data to be aggregated, you should not be using VBA but rather SQL.

Heck, if you want to be lazy, Access already provides you with DMax domain function (though they are problematic because they invariably get used in VBA and thus get used in a RBAR manner). You really just want to let SQL do all the work and get a final recordset which the operation becomes a straight read/export/display without any additional work.

If you have looked long and hard at the code and the inputs are not from a data source, are not run in a loop (whether directly or indirectly), then sure, you can knock yourself out with some homebrewn VBA function or referencing Excel library (preferably late-bound).

1) Row-By-Agonizing-Row -- iterations are cool in imperative languages. It's not as cool in declarative languages like SQL.

Upvotes: 0

KySoto
KySoto

Reputation: 428

In access, you dont have those worksheet functions, the only way you would is if either A you coded them in yourself or, B you import the excel library into your access project. Personally, i would go with A since if you import the excel library you are stuck on the exact version of excel that you imported in.

A quick and dirty example would be

Public Function Max(ByVal A As Variant,ByVal B As Variant) As Variant
    If A > B Then
        Max = A
    Else
        Max = B
    End If
End Function

This needs a little TLC so that you aren't trying to compare recordsets or other nonsense and have it throw an error.

EDIT: i suppose you could also get the excel library stuff in if you late bound the reference to excel, so thats a third option.

Upvotes: 1

SRT HellKitty
SRT HellKitty

Reputation: 597

I have 3 ideas for you,

  1. you can skip the "Application" and just write 'Worksheetfunction.Max' in almost all circumstances using VBA.

  2. You can set a variable to be 'Worksheetfunction', i.e.

    Dim foo As Object
Set foo = Application.WorksheetFunction
foo.Max(MyArray)

  3. You can create a function or Sub in the module that does only this. and have a long as the input, not sure this is worth the effort and it may slow the code down a fair amount.

Upvotes: 1

BlueMonkMN
BlueMonkMN

Reputation: 25601

There are two shorter ways I've found to code that:

One

Sub MaxTest()
    Dim A As Integer, B As Integer
    A = Sheet1.Range("$A$1").Value
    B = Sheet1.Range("$A$2").Value
    Sheet1.Range("$B$1").Value = WorksheetFunction.Max(A, B)
End Sub

Two

Sub MaxTest()
    Dim A As Integer, B As Integer
    A = Sheet1.Range("$A$1").Value
    B = Sheet1.Range("$A$2").Value
    Sheet1.Range("$B$1").Value = IIf(A > B, A, B)
End Sub

Upvotes: 5

Related Questions