Excel Custom Function with Select Case

H

Heather

Hi
Below is a copy of my code, but Excel returns the message: Sub or
function not defined. Roundup is selected. If I change to
Round(which I really don't want to do), then Max is highlighted. If I
change Max to Large(value,1), it also gets selected with the above
message.

If anyone can help me devise a function that tests which range 2
separate values fall into and what the difference is to step up to the
next rank. This difference then needs to be divided by 35, and the
rounded up interger is displayed.
Thanks


Function GiftCertificate(PCV, GCV)
'Calculates the number of Gift Certificates required to
'move to the next rank

GiftCert = 35
BronzeGCv = 1000
SilverGCV = 3500
CQSliverGCV = 3500
GoldGCV = 10000
PlatinumGCV = 25000
DiamondGCV = 50000
DDiamondGCV = 250000
BronzePCv = 100
SilverPCV = 350
CQSliverPCV = 600
GoldPCV = 1000
PlatinumPCV = 2500
PlatinumPCV = 5000
PlatinumPCV = 25000


Select Case PCV
Case 0 To 99: GiftCertificate = Max(RoundUp((BronzePCv - PCV) /
GiftCert, 0), RoundUp((BronzeGCv - GCV) / GiftCert, 0)) * GiftCert
Case 100 To 349: GiftCertificate = Max(RoundUp((SilverPCV - PCV) /
GiftCert, 0), RoundUp((SilverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 350 To 599: GiftCertificate = Max(RoundUp((CQSliverPCV - PCV)
/ GiftCert, 0), RoundUp((CQSliverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 600 To 999: GiftCertificate = Max(RoundUp((GoldPCV - PCV) /
GiftCert, 0), RoundUp((GoldGCV - GCV) / GiftCert, 0)) * GiftCert
Case 1000 To 2499: GiftCertificate = Max(RoundUp((PlatinumPCV -
PCV) / GiftCert, 0), RoundUp((PlatinumGCV - GCV) / GiftCert, 0)) *
GiftCert
Case 2500 To 4999: GiftCertificate = Max(RoundUp((PlatinumPCV -
PCV) / GiftCert, 0), RoundUp((DiamondGCV - GCV) / GiftCert, 0)) *
GiftCert
Case 5000 To 24999: GiftCertificate = Max(RoundUp((PlatinumPCV -
PCV) / GiftCert, 0), RoundUp((DDiamondGCV - GCV) / GiftCert, 0)) *
GiftCert
End Select



End Function
 
K

Kevin Stecyk

Heather,

Roundup is a "traditional" spreadsheet function and not a VBA function.
Should you want to use Roundup in VBA, you must use the following syntax:

Application.WorksheetFunction.RoundUp(value)

The same applies to min and max. You might want to check XL help for List
of Worksheet Functions Available to Visual Basic.

Below is a "roll-your-own" round up function written by Myrna Larson and
myself. We were interested in speed, and application.worksheet.functions
tend to be a bit slow. Though for your purposes it might be plenty fast.

So you either have to roll your own functions or use the
application.worksheet functions.

Hope this helps.

Regards,
Kevin





'\==========================================================================
'\ Sub/Fun: GetRoundUpInt
'\
'\ Purpose:
'\ Arguments: Value as double
'\ Returns: Rounds up the double value to the next integer value.
'\ Called By: UDFs in this module
'\ Notes:
'\
'\ Keywords: Roundup
'\ Originated: Kevin & Myrna 2 Jun 2003
'\ Rev Hist:
'\==========================================================================
'\
'\
Function GetRoundUpInt(dbValue As Double) As Integer
Dim dbTemp As Double

dbTemp = dbValue

If dbValue >= 0 Then

If Int(dbValue) >= dbTemp Then
GetRoundUpInt = Int(dbValue)
Else
GetRoundUpInt = Int(dbValue) + 1
End If
Else
If Int(dbValue) >= dbTemp Then
GetRoundUpInt = Int(dbValue) - 1
Else
GetRoundUpInt = Int(dbValue)
End If
End If

End Function
 
K

Kevin Stecyk

Application.WorksheetFunction.RoundUp(value)

should be

Application.WorksheetFunction.RoundUp(number, num_digits)
 
T

Tom Ogilvy

Function GiftCertificate(PCV, GCV)
'Calculates the number of Gift Certificates required to
'move to the next rank

GiftCert = 35
BronzeGCv = 1000
SilverGCV = 3500
CQSliverGCV = 3500
GoldGCV = 10000
PlatinumGCV = 25000
DiamondGCV = 50000
DDiamondGCV = 250000
BronzePCv = 100
SilverPCV = 350
CQSliverPCV = 600
GoldPCV = 1000
PlatinumPCV = 2500
PlatinumPCV = 5000
PlatinumPCV = 25000
With Application
Select Case PCV
Case 0 To 99: GiftCertificate = .Max(.RoundUp((BronzePCv - PCV) / _
GiftCert, 0), .RoundUp((BronzeGCv - GCV) / GiftCert, 0)) * GiftCert
Case 100 To 349: GiftCertificate = .Max(.RoundUp((SilverPCV - PCV) / _
GiftCert, 0), .RoundUp((SilverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 350 To 599: GiftCertificate = .Max(.RoundUp((CQSliverPCV - PCV) _
/ GiftCert, 0), .RoundUp((CQSliverGCV - GCV) / GiftCert, 0)) * GiftCert
Case 600 To 999: GiftCertificate = .Max(.RoundUp((GoldPCV - PCV) / _
GiftCert, 0), .RoundUp((GoldGCV - GCV) / GiftCert, 0)) * GiftCert
Case 1000 To 2499: GiftCertificate = .Max(.RoundUp((PlatinumPCV - _
PCV) / GiftCert, 0), .RoundUp((PlatinumGCV - GCV) / GiftCert, 0)) * _
GiftCert
Case 2500 To 4999: GiftCertificate = .Max(.RoundUp((PlatinumPCV - _
PCV) / GiftCert, 0), .RoundUp((DiamondGCV - GCV) / GiftCert, 0)) * _
GiftCert
Case 5000 To 24999: GiftCertificate = .Max(.RoundUp((PlatinumPCV - _
PCV) / GiftCert, 0), .RoundUp((DDiamondGCV - GCV) / GiftCert, 0)) * _
GiftCert
End Select
End With
 
J

J.E. McGimpsey

If you're interested in speed, this is about 40% faster on my
machine than the one you posted:

Function VBRoundUpInt(ByVal dIn As Double) As Double
VBRoundUpInt = Fix(dIn - Sgn(dIn) * (dIn <> Fix(dIn)))
End Function
 
P

Phobos

Your function returns n-1 for integers less than zero, e.g.

[A1] = -55

GetRoundUpInt(A1) = -56

P
 
K

Kevin Stecyk

Phobos,

You are correct! I think I have one too many "=" signs.

But in any event, I like J.E. McGimpsey 40% faster solution better. I just
need to better understand it.

Regards,
Kevin


Phobos said:
Your function returns n-1 for integers less than zero, e.g.

[A1] = -55

GetRoundUpInt(A1) = -56

P


Kevin Stecyk said:
Heather,

Roundup is a "traditional" spreadsheet function and not a VBA function.
Should you want to use Roundup in VBA, you must use the following syntax:

Application.WorksheetFunction.RoundUp(value)

The same applies to min and max. You might want to check XL help for List
of Worksheet Functions Available to Visual Basic.

Below is a "roll-your-own" round up function written by Myrna Larson and
myself. We were interested in speed, and application.worksheet.functions
tend to be a bit slow. Though for your purposes it might be plenty fast.

So you either have to roll your own functions or use the
application.worksheet functions.

Hope this helps.

Regards,
Kevin
Click to expand...
'\==========================================================================
'\ Sub/Fun: GetRoundUpInt
'\
'\ Purpose:
'\ Arguments: Value as double
'\ Returns: Rounds up the double value to the next integer value.
'\ Called By: UDFs in this module
'\ Notes:
'\
'\ Keywords: Roundup
'\ Originated: Kevin & Myrna 2 Jun 2003
'\ Rev Hist:
Click to expand...
'\==========================================================================
'\
'\
Function GetRoundUpInt(dbValue As Double) As Integer
Dim dbTemp As Double

dbTemp = dbValue

If dbValue >= 0 Then

If Int(dbValue) >= dbTemp Then
GetRoundUpInt = Int(dbValue)
Else
GetRoundUpInt = Int(dbValue) + 1
End If
Else
If Int(dbValue) >= dbTemp Then
GetRoundUpInt = Int(dbValue) - 1
Else
GetRoundUpInt = Int(dbValue)
End If
End If

End Function
Click to expand...
Click to expand...
 
K

Kevin Stecyk

J.E. McGimpsey,

Thank you very much for your solution!

I don't completely understand it, so please allow me to ask a few follow-up
questions.

I tried using a pos 9.2, and your routine gives the correct answer of 10.

I understand the following:

dIn=9.2
Sgn(dIn)=1 (positive)
(dIn<>Fix(dIn))=True (which I interpret to be 1)

My question is, when I examine "Sgn(dIn) * (dIn <> Fix(dIn))", the PC
shows -1, but I think (incorrectly, it appears) that it should be positive
1, because it is 1*1? Where I am going wrong with my thinking?

Again, appreciative of your answer.

Regards,
Kevin


J.E. McGimpsey said:
If you're interested in speed, this is about 40% faster on my
machine than the one you posted:

Function VBRoundUpInt(ByVal dIn As Double) As Double
VBRoundUpInt = Fix(dIn - Sgn(dIn) * (dIn <> Fix(dIn)))
End Function
Click to expand...
fast.
 
J

J.E. McGimpsey

VBA, like most other languages I'm familiar with, coerces the
boolean True to -1 (i.e., all bits set in a two's complement binary
representation), rather than +1.

XL is different.
 
K

Kevin Stecyk

J.E. McGimpsey,

Just to make sure I am clear, in XL

a) on a spreadsheet, an expression that is "TRUE" is treated as 1.
b) in vba code, an expression that is "TRUE" is treated as -1.

If I incorrect, please let me know.

Again, thank you for your solution--I like it!

Regards,
Kevin
 
H

Heather

Hi Guys
Thanks for all the help. I'm quite happy that the WorksheetFunction
helped to resolve the issue. As it is a very small business plan,
speed is not the issue, but I appreciate all the discussion and helps
and tweaks.
Thanks
Heather
 
P

Phobos

Just to clarify:

In VBA, True = -1 False = 0

But, also in VBA:

0 = False <any other value> = True


Try this code:

Sub test()
Dim isFalse As Boolean
Dim x As Integer
For x = -20 To 20
isFalse = x
Debug.Print x & " " & isFalse
Next
End Sub


You will see in the immediate window after execution that all values except
0 return True.

P
 
K

Kevin Stecyk

Phobos,

Thank you for elaborating on J.E. McGimsey's comments. That subroutine is
interesting for it demonstrates that all values except 0 are evaluated as
true. And looking at J.E. McGimpsey's earlier comments, true "...coerces
the boolean True to -1..."

Okay, that is great to know! Again, thank you very much.

Regards,
Kevin
 
Top