Optimum Calculation using VBA

[Andrew]

I have developed a file to advise me when I should rise the weight of a
airfreight shipment to receive the best rates available. (It's still
quite rudimentary at this stage)

How it works is like this...let's assume anything up to 45 kgs will
cost $4.70 per kg, anything equal or above 45 kgs will cost $4.00 per
kg. If we consider that 45 kgs X $4.00 costs $180.00, then any shipment
above 38 kgs at $4.70 per kg will cost more (39 kgs X $4.70 will cost
$183.33, nothing less than a whole kg is valid). Therefore, it is best
to raise the shipment weight to 45 kgs to avoid paying undue shipping
costs.

What I would like to do is make a custom function add-in such as I saw
on this site at
http://www.exceltip.com/st/Build_an_Excel_Add-In_in_Microsoft_Excel/633.html,
so that when I select Insert Formula, I can enter the relevant
information and the VBA will do the rest.

One catch...I want a pop-up comment or something similar to advise me
when to raise the shipment rate rather than a separate cell as you see
on my attached file. The reason is that I may not want to change the
weight depending on the circumstances, I just want to know when the
opportunity arises.

Another thing to be considered is that there is a minimum charge,
(let's say $80.00) so any shipment less than 18 kgs will incur this
minimum charge (17 kgs X $4.70 = $79.90). This should change the
airfreight amount automatically, but I still wish to notified so I
understand what has happened.

I am still learning basic VBA, but from I understand of how it works, I
think what I want can be programmed. If anyone can show me how to
achieve this, this will assist me greatly as a reference for future
projects. Thanks very much.

Attachment filename: airfreight rates optimum calculator.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=403794

 

[Don Guillett]

One way. You could also use lookup
ONE LINE
=IF(F19>=45,F19*4,IF(F19*4.7<45*4,F19*4.7,DOLLAR(F19*4.7,2)&" Raise to 45lbs
@$180"))

 

[Andrew]

Thanks. On my attached file I used LOOKUP to work out the actual
airfreight, and then IF to display a message in another cell to advise
raising the airfreight if the situation demanded. (I want to be
notified rather than just automatically increasing the airfreight
because a) I want the choice to go ahead before anything is done, and
b) I need to know what has taken place so I make a note of the change)

The LOOKUP calculation used to determine the airfreight itself was
straightforward, determining optimums was calculated as below.

=IF(Weight>Limit3,"",IF(AND(Weight>Limit2,Weight<Limit3,Weight*Rate3>Limit3*Rate4),"Raise
Weight to Limit
3",IF(AND(Weight>Limit1,Weight<Limit2,Weight*Rate2>Limit2*Rate3),"Raise
Weight to Limit
2",IF(AND(Weight>Base,Weight<Limit1,Weight*Rate1>Limit1*Rate2),"Raise
Weight to Limit 1",IF(Weight*Rate<Minimum,"Changed to Minimum","")))))

Weight = Weight Shipped
Rate = Calculable Rate (determined by LOOKUP)
Airfreight = Airfreight Charged
=IF(Weight*Rate<Minimum,Minimum,Weight*Rate)

Base = 0 kgs
Limit1 = 45 kgs
Limit2 = 100 kgs
Limit3 = 500 kgs

Rate1 = $4.70
Rate2 = $4.00
Rate3 = $3.30
Rate4 = $260

Trouble is I may have several shipments listed in the same table. I
could have a Display cell in the same row to advise optimizing, but I
would prefer not to do this and use an automatic pop-up comment
appearing in the corner of the calculated airfreight cell instead. Once
alerted, I can manually increase the weight if I choose to.

Seems to me that this could be done using VBA using "AddComment" if the
right criteria are met using "If" or "Case Is".

 

[Andrew]

Perhaps this is on the right track...(Sorry, I'm just a newbie)

Function Optimum(Weight As Integer)
' This function is to provide advice for raising weight to receive bes
airfreight rates possible
' by the means of a comment being automatically displayed if a les
than optimum weight is entered.
' It also advises when the Minimum Rate will be charged.
' Below 45 kgs $4.70 per kg
' Below 100 kgs $4.00 per kg
' Below 500 kgs $3.30 per kg
' Above 500 kgs $2.60 per kg
' Minimum Rate $80.00
If Weight = 0 Then
Optimum = 0
Else
Select Case Weight > 500
Optimum = 0
Case Is >= 100, Is < 500, Weight * 3.3 > 500 * 2.6
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Raise Weight to Limit 3"
ActiveCell.Select
Case Is >= 45, Is < 100, Weight * 4 > 100 * 3.3
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Raise Weight to Limit 2"
ActiveCell.Select
Case Is < 45, Weight * 4.7 > 45 * 400, Weight * 4.7 > 80
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Raise Weight to Limit 1"
ActiveCell.Select
Case Is < 80
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:="Minimum Charged"
ActiveCell.Select
End Select
End If
End Function

As you can see, I don't know enough VBA to program properly yet. An
help you can give me would be appreciated.

No greater joy than learning a new trick

 

[Andrew]

I suppose I could always use Conditional Formatting and color code it to
mean different things.

But really I still want to know how to get a custom message via a
comment using VBA .

There is a beginner's VBA programming book due in the mail...can I wait
that long? Aaaaarrrrrgggghhhh!

 

[Andrew]

C'mon guys...surely one of you MVPs out there must know what I'
after....

I'll write you into my will. You can have my entire collection of rar
and exotic stamps (I promise to go out and buy a couple the day afte
tomorrow) :

 

[Don Guillett]

set up a table like this
B C D
0.00 4.70 0.00
45.00 4.00 180.00
100.00 3.30 330.00
500.00 2.60 1300.00


then the one line formula below from 44 will produce 180
44.00
180.00
=MIN(INDEX($B$9:$D$12,MATCH($A$9,$B$9:$B12),2)*$A$9,INDEX($B$9:$D$12,MATCH($
A$9,$B$9:$B12)+1,3))

 

[Andrew]

How about me guys?

Alll I want to know is some VBA code to respond to different ranges of
numbers (I already know how to this with functions such as IF and INDEX
etc. with or without Conditional Formatting) It's the VBA code I want
to learn.

Say for the following ranges,

0-20 Do something like enter the text "Poor"
21-40 Do something like enter the text "Below Average"
41-60 Do something like enter the text "Average"
61-80 Do something like enter the text "Above Average"
81-100 Do something like enter the text "Great"

It has to run in such a way that as soon as one of the above ranges is
calculated by use of functions, the VBA does it stuff and inputs the
text in a specified cell, let's say H20 for arguments sake.

If anyone can tell me how to this, this will be a very big break for
me.

I really would apreciate it.

 

[jaf]

Hi Andrew,
Look up "select case" in VBA help for one solution.

--
John
johnf 202 at hotmail dot com


| How about me guys?
|
| Alll I want to know is some VBA code to respond to different ranges of
| numbers (I already know how to this with functions such as IF and INDEX
| etc. with or without Conditional Formatting) It's the VBA code I want
| to learn.
|
| Say for the following ranges,
|
| 0-20 Do something like enter the text "Poor"
| 21-40 Do something like enter the text "Below Average"
| 41-60 Do something like enter the text "Average"
| 61-80 Do something like enter the text "Above Average"
| 81-100 Do something like enter the text "Great"
|
| It has to run in such a way that as soon as one of the above ranges is
| calculated by use of functions, the VBA does it stuff and inputs the
| text in a specified cell, let's say H20 for arguments sake.
|
| If anyone can tell me how to this, this will be a very big break for
| me.
|
| I really would apreciate it.
|
|
| ---
| Message posted
|

 

[Andrew]

Hi Andrew,
Look up "select case" in VBA help for one solution.

--
John
johnf 202 at hotmail dot com

John,

You are a lifesaver! I finally got it!

Ha ha, from now I make my own conditional formatting...

Andrew

 

[Andrew]

Well, I finally bought a beginners VBA programming book last night an
after half an hour of reading, I came up with the following function
(It doesn't calculate anything just yet, it just displays message boxe
to advise me to raise the weight, which was what I was after in th
first place - I can always do the actual calculation in another cell)

Function Airfreight(Weight, Limit1, Limit2, Limit3, Rate1, Rate2
Rate3, Rate4)
If Weight > 0 And Weight < Limit1 And Weight * Rate1 > Limit1 * Rate
Then MsgBox "Raise Weight to 45 kgs"
If Weight >= Limit1 And Weight < Limit2 And Weight * Rate2 > Limit2
Rate3 Then MsgBox "Raise Weight to 100 kgs"
If Weight >= Limit2 And Weight < Limit3 And Weight * Rate3 > Limit3
Rate4 Then MsgBox "Raise Weight to 500 kgs"
End Function

I intend to replace the messge boxes with comments later on or perhap
I can use both? More experimentation is required.

If, And & Then was all that I was after, perhaps my explanations wer
too difficult to understand

 

[Bob Phillips]

Andrew,

Adapted slightly to return the text (not Msgbox), make it only test until it
matches, and to throw an error if no valid condition is met

Function Airfreight(Weight, Limit1, Limit2, Limit3, Rate1, Rate2, Rate3,
Rate4)
If Weight > 0 And Weight < Limit1 And Weight * Rate1 > Limit1 * Rate2
Then
Airfreight "Raise Weight to 45 kgs"
ElseIf Weight >= Limit1 And Weight < Limit2 And Weight * Rate2 > Limit2
* Rate3 Then
Airfreight "Raise Weight to 100 kgs"
ElseIf Weight >= Limit2 And Weight < Limit3 And Weight * Rate3 > Limit3
* Rate4 Then
Airfreight "Raise Weight to 500 kgs"
Else
Airfreight = CVErr(xlErrValue)
End If
End Function

Maybe this will help in your learning.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Andrew]

Thanks Bob.

Is "Airfreight = CVErr(xlErrValue)" a VBA equivalent to the ISERRO
function?

Andre

 

[Bob Phillips]

Andrew,

No, the opposite in fact. It doesn't test for an error as ISERROR does, it
sets the error. So if there is an invalid value passed in the function, and
none of the 3 criteria that are tested for are met, then the function will
return #VALUE into the calling cell. This makes it more compatible with
built-in Excel functions.

You could also test each argument for individual correctness, and
cross-validate them, but that would be very expensive performance wise in
your function, so I took the single, easy option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)