| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Custom Function with Select Case
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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 "Heather" wrote in message om... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
Application.WorksheetFunction.RoundUp(value)
should be Application.WorksheetFunction.RoundUp(number, num_digits) "Kevin Stecyk" wrote in message ... 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 "Heather" wrote in message om... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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 -- Regards, Tom Ogilvy "Heather" wrote in message om... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
Tom,
I like your solution. Just a slight tweak. Very good. Regards, Kevin "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Heather" wrote in message om... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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 In article , "Kevin Stecyk" wrote: 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
Your function returns n-1 for integers less than zero, e.g.
[A1] = -55 GetRoundUpInt(A1) = -56 P "Kevin Stecyk" wrote in message ... 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 "Heather" wrote in message om... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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" wrote in message ... Your function returns n-1 for integers less than zero, e.g. [A1] = -55 GetRoundUpInt(A1) = -56 P "Kevin Stecyk" wrote in message ... 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 "Heather" wrote in message om... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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" wrote in message ... 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 In article , "Kevin Stecyk" wrote: 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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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. In article , "Kevin Stecyk" wrote: 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" wrote in message ... 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 In article , "Kevin Stecyk" wrote: 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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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 "J.E. McGimpsey" wrote in message ... 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. In article , "Kevin Stecyk" wrote: 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" wrote in message ... 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 In article , "Kevin Stecyk" wrote: 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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
That's correct.
In article , "Kevin Stecyk" wrote: 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. |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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 "Kevin Stecyk" wrote in message ... Tom, I like your solution. Just a slight tweak. Very good. Regards, Kevin "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Heather" wrote in message om... 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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 "Kevin Stecyk" wrote in message ... 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 "J.E. McGimpsey" wrote in message ... 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. In article , "Kevin Stecyk" wrote: 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" wrote in message ... 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 In article , "Kevin Stecyk" wrote: 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. |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Custom Function with Select Case
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 "Phobos" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select Case ignored | Excel Worksheet Functions | |||
| countif function: how to distinguish case/make case sensitive | Excel Worksheet Functions | |||
| Case Select | Excel Worksheet Functions | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| Select Case | Excel Discussion (Misc queries) | |||
![Heather[_7_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
