Multiple If function for more than 7 values

[echo_park]

Hi all, I have a question regarding VBA code for an if function.

In looking for an answer, I found this code provided on a website
(www.techonthenet.com) It seems to be what I'm looking for but returns
the error '#NAME' when I try and add it to Excel. Does anyone know
what's wrong with the code? The strange thing is that there's an
example of this as a download which seems to return the same error, I'm
wondering if it's something to do with the version of Excel I'm
using... (I have 2003)

Function CalcValue(pVal As String) As Long

If pVal = "10x12" Then
CalcValue = 140

ElseIf pVal = "8x8" Then
CalcValue = 64

ElseIf pVal = "6x6" Then
CalcValue = 36

ElseIf pVal = "8x10" Then
CalcValue = 80

ElseIf pVal = "14x16" Then
CalcValue = 224

ElseIf pVal = "9x9" Then
CalcValue = 81

ElseIf pVal = "4x3" Then
CalcValue = 12

Else
CalcValue = 0
End If

End Function

The values in the code are not the one's I would be using, but they are
the ones provided in the example (which also does not seem to work).

 

[Bob Phillips]

It works for me. You have to store the function in a standard code module.
Hit Alt-F11, then Insert>Module, copy and paste the code. Then in a
worksheet, use either

=CalcValue("9x9")

or

=CalcValue(A1)

where A1=9x9

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Scoops]

Hi echo_park

It works fine for me, make sure you've got the code in a module or in
the code area of the sheet you're using the function in.

And, aesthetically, you might like this:

Function CalcValue(pVal As String) As Long
Select Case pVal
Case "10x12"
CalcValue = 140
Case "8x8"
CalcValue = 64
Case "6x6"
CalcValue = 36
Case "8x10"
CalcValue = 80
Case "14x16"
CalcValue = 224
Case "9x9"
CalcValue = 81
Case "4x3"
CalcValue = 12
Case Else
CalcValue = 0
End Select
End Function

Regards

Steve

 

[Dana DeLouis]

The values in the code are not the one's I would be using...

Would this general idea give you something to work with?
However, this suggests that 10*12 equals 120 instead of 140.

Sub Demo()
Dim s, n
s = "10x12"

'Simple check
If s Like "*x*" Then
n = Evaluate(Replace(s, "x", "*"))
Debug.Print n ' <- 120
End If
End Sub

 

[echo_park]

Hmm... I've done nothing wrong in the entering of the code, I've
checked and triple checked that. Just doesn't work on this PC for some
reason. Whatever I do I just get '#NAME' and nothing. I've saved and
logged off and made sure everything is as it should be, but if there's
no error in the code it must be something on this PC that's messing it
up...

Thanks for your help anyway!

 

[echo_park]

Ok now Excel seems to have changed the error... its a 'VALUE#!' error
now!

I selected the formula from the list and it suddenly changed... Any
ideas?

 

[echo_park]

Ok third post in a row to let you know I've got it sorted now... not
sure exactly how I fixed the problem, but I fiddled around and suddenly
things started working!

Thanks for the continued help, this forum is great.