2

Assuming I have the following MS Access 2016 function:

Public Function TestFunction(ByVal strTest As String) As String

Is there a way to call the function with something similar to:

strReturn = TestFunction(If a = 1 Then "Apple" Else "Orange")

I would like to write something as compact as possible, and avoid having to write multiple lines for the call, such as:

If a = 1 Then strArg = "Apple" Else strArg = Orange
strReturn = TestFunction(strArg)
braX
  • 11,506
  • 5
  • 20
  • 33
MiniG34
  • 312
  • 2
  • 12
  • 2
    Consider not doing funky stuff to save a single line of code. Just going for the obvious, verbose solution will save anyone trying to figure out what you're doing and why you're doing lots of trouble it in the future. Unless you got a truly lengthy list, in which case you probably want something else entirely, like a function looking it up in a table (not mixing code and data is a good practice), or an array/collection/dictionary. – Erik A May 04 '20 at 17:27

3 Answers3

1

You can accomplish this using IIf, e.g.:

strReturn = TestFunction(IIf(a=1,"Apple","Orange"))

But this is generally discouraged when working with expressions other than constants, because IIf will always evaluate both the then and else argument before returning the appropriate value depending on the outcome of the test expression, which can sometimes lead to undesired results.

For example, evaluating the following expression at the Immediate Window (Ctrl+G) will result in a division by zero error, even though the else expression will never be returned:

?iif(true,"Apple",1/0)
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • 1
    why is `IIf` strongly discouraged, besides the note you mentioned about evaluating both statements? – Scott Holtzman May 04 '20 at 16:45
  • What about a scenario with an `If` and `Else If`, similar to a `Select Case` statement, where there is only two possible values, ie `If a = 1 Then "Apple" Else If a = 2 "Orange"`? – MiniG34 May 04 '20 at 16:48
  • @ScottHoltzman For that reason - it can lead to undesired side effects unless used with care - I suppose *strongly* is a bit strong, I'll remove that. – Lee Mac May 04 '20 at 16:48
  • 1
    @MiniG34 You can use a nested `IIf` expression. – Lee Mac May 04 '20 at 16:49
  • 3
    `IIf` isn't any more special than any other function call in VBA: its arguments are evaluated before the function call is made, so that their evaluated results can be passed as arguments to the function, which then decides which one to output given the Boolean first parameter. What's heavily discouraged, is passing anything other than constant expressions for the arguments. Absolutely nothing wrong with passing string literals. Different story when passing side-effecting function calls. – Mathieu Guindon May 04 '20 at 17:33
  • @MathieuGuindon Thank you for your input Mathieu, I've revised my explanation accordingly. – Lee Mac May 04 '20 at 17:53
0

If the value for a comes from a well defined range then the solution would be to use either the Choose Function for a contiguous range or Switch Function for a non contiguous well defined range.

Of course the more grown up solution would be to replace both Choose or Switch with a Dictionary.

freeflow
  • 4,129
  • 3
  • 10
  • 18
0

Why not something like this for multiple possibilities

Dim myArg as String

Select Case a
    Case 1: myArg = "Apple"
    Case 2: myArg = "Orange"
    Case 3: myArg = "Pear"
   'etc.
End Select

strReturn = TestFunction(myArg)
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • My goal is to write something as compact as possible. Of course, this would work, but since I have call the same function 50+ times with different conditionnal statements as arguments, I want to avoid having to write different Select Case every time and try to keep everything as one line. – MiniG34 May 04 '20 at 17:12
  • 1
    `50+ times with different conditional statements as arguments` does not seem simple. very hard to write on one line unless you only 1 or 2 conditionals. The other option is create a separate function to calculate the conditional argument and then pass that to the function. That way you only call the function that returns the conditional and the testFunction. You could use module level or global variables to help as well. – Scott Holtzman May 04 '20 at 17:22
  • 1
    Yes, I was just thinking about writing a function :) I think that might solve the "compactness" issue. – MiniG34 May 04 '20 at 17:24
  • 1
    @MiniG34 - i do that *all the time* – Scott Holtzman May 04 '20 at 17:24