By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

Overflow error while coding in Excel

P: 1
I consistently get an overflow error at the "SOxBenefits =" (source code below), while coding in a macros from within Excel, Microsoft XP. I've tried the following:

1. setting a variable equal to a number * CLong(#)
2. changing the variable type, as well as making sure all subsequent variables are of the same type. When attempting to use the ULong type, Excel gives me a mismatch error. I found a thread on how to link a windows file, but the "reference" tab is grayed out. Not sure how to link otherwise.
3. commenting out my code and just using the raw numbers, I still get an overload error, but shouldn't because my variable is under 1E9.

any suggestions?

Thanks.


Sub SS2BikeStorage()
'
' SS2BikeStorage Macro
'
' Keyboard Shortcut: Ctrl+b
'
Range("B15").Select
Application.Goto Reference:="SS2BikeStorage"



Dim FTE 'Number of full-time equivalent employees
Dim NumParkingSpace 'Total Number of Parking Spaces
Dim ParkingArea 'Total parking lot area [s.f.]
Dim DepthExcav 'Depth of required excavation ["]
Dim UnitCostExcav 'cost to excavate per cubic yard
Dim DepthBackfillCompact 'Depth to be backfilled and compacted
Dim UnitCostBackfillCompact 'cost to backfill and compact per cubic yard
Dim DepthAsphalt 'Depth of asphalt ["]
Dim UnitCostAsphalt 'cost of asphalt per ton
Dim UnitCostSealer 'cost of sealer per gallon
Dim CostRack 'cost of bike rack [$]
Dim CostShowerChanging
Dim CostLandscapingCurbingDrainage
Dim CostImportedFill
Dim ConstructionCosts
Dim WaterCosts
Dim SavedUserBenefits
Dim EmissionsSavings

Const AvgMilesPerDay = 15
Const WorkdaysPerYear = 250 '50 * 5

Dim FTEMilesPerYear As Long
FTEMilesPerYear = 250 'AvgMilesPerDay * WorkdaysPerYear

End Sub
Sub Main() 'Main calling program, runs all others
Data 'inputs data
Analysis 'computes costs and benefits
Output 'Displays program output
End Sub


Sub Data()
FTE = Cells(4, 2) '
NumParkingSpace = Cells(5, 2) '
ParkingArea = Cells(6, 2) 'Total parking lot area [s.f.]
DepthExcav = Cells(7, 2) 'Depth of required excavation ["]
UnitCostExcav = Cells(7, 3) 'cost to excavate per cubic yard
DepthBackfillCompact = Cells(8, 2) 'Depth to be backfilled and compacted
UnitCostBackfillCompact = Cells(8, 3) 'cost to backfill and compact per cubic yard
DepthAsphalt = Cells(11, 2) 'Depth of asphalt ["]
UnitCostAsphalt = Cells(11, 3) 'cost of asphalt per ton
UnitCostSealer = Cells(10, 3) 'cost of sealer per gallon
CostRack = Cells(13, 3) 'cost of bike rack [$]
CostShowerChanging = Cells(14, 3)
CostLandscapingCurbingDrainage = Cells(15, 3)
CostImportedFill = Cells(16, 3)

End Sub
Sub Analysis()
'internal cost variables

Dim TotalAsphaltCost ' construction cost of asphalt
Dim TotalSealCost 'construction cost of sealer
Dim SpaceArea 'square footage per parking space including aisles
Dim TotalExcavCost 'total cost of excavation

Const SealerFactor = 0.15 'gallons of sealer/square yard
Const AsphaltUnitWeight = 150 '150 lbs/cubic foot

'Construction costs
SpaceArea = 1.533 * 18 * 9 'assuming 18'x9' spaces and 53% aisle area
TotalAsphaltCost = DepthAsphalt * SpaceArea * AsphaltUnitWeight * UnitCostAsphalt / (12 * 2000) '
TotalSealCost = SealerFactor * UnitCostSeal * SpaceArea / 9
TotalExcavCost = (SpaceArea * DepthExcav * UnitCostExcav) + (SpaceArea * DepthBackfillCompact * UnitCostBackfillCompact)

TotalSavedConstructionCosts = FTE * (TotalAsphaltCost + TotalSealCost + TotalExcavCost)

ConstructionCosts = CostRack + CostShowerChanging + CostLandscapingCurbingDrainage + CostImportedFill - TotalSavedConstructionCosts 'interior changing + landscaping/curbing + bike racks
'Operations Costs
'Water
Const ShowersPerDay = 15
Const MinutesPerShower = 5
Const GallonsPerMinute = 2
Const WorkdaysPerYear = 250 '5 * 50
Const GallonsPerCubicFoot = 7.48
Const CostPerCubicFoot = 0.64
Const PGivenA = 23.88 '(for i=3.4%, n=50)
Dim GallonsPerYear
Dim WaterCosts

'GallonsPerYear = ShowersPerDay * MinutesPerShower * GallonsPerMinute * WorkdaysPerYear
GallonsPerYear = 37500
WaterCosts = GallonsPerYear * CostPerCubicFoot * PGivenA / GallonsPerCubicFoot

'Pavement resurfacing
Const AsphaltResurfacingCost = 1.25 'cost/ft^2
SavedResurfacing = FTE * SpaceArea * AsphaltResurfacingCost * (0.61 + 0.37)
'Emissions Costs
'CO2
Const CO2EmissionsMile = 550.4 'average emission rate of CO2 in grams/mi from MEPA GHG Protocol
Const CO2EmissionsTon = 907185 'grams CO2 per ton CO2
Const CO2Cost = 7.5 'average cost of CO2 credits per ton

CO2Benefits = FTEMilesPerYear * CO2EmissionsMile * CO2Cost / CO2EmissionsTon

'NOx
Const NOxEmissionsMile = 0.95 'average emission rate of NOx in grams/mi from
Dim NOxEmissionsTon
NOxEmissionsTon = 454 * CLng(2000) 'grams NOx per ton NOx
Const NOxCost = 27704 'average cost of NOx credits per ton

NOxBenefits = (FTEMilesPerYear * NOxEmissionsMile * NOxCost) / NOxEmissionsTon

'SOx
Dim SOxEmissionsMile As Long 'average emission rate of SOx in grams/mi from
SOxEmissionsMile = 0.95
Dim SOxEmissionsTon As Long
SOxEmissionsTon = 908000 '454 * 2000 grams SOx per ton NOx
Dim SOxCost As Long 'average cost of SOx credits per ton
SOxCost = 12809
Dim SOxBenefits As Long

SOxBenefits = (FTEMilesPerYear * SOxEmissionsMile * SOxCost) / SOx2EmissionsTon

'PM-10
Const PM10EmissionsMile = 0.0052 'average emission rate of PM10 in grams/mi from
Dim PM10EmissionsTon
PM10EmissionsTon = 454 * 200 'grams PM10 per ton PM10
Const PM10Cost = 46148 'average cost of PM10 credits per ton PM10

PM10Benefits = FTEMilesPerYear * PM10EmissionsMile * PM10Cost / PM10EmissionsTon

'Total emissions savings
EmissionsSavings = CO2Benefits + NOxBenefits + PM10Benefits

'Saved User Costs (gas and vehicle maintenance)
Const FedReimbursementRate = 0.485

SavedUserBenefits = FedReimbursementRate * FTEMilesPerYear

End Sub

Sub Output()
Cells(18, 5) = ConstructionCosts
Cells(19, 5) = WaterCosts
Cells(20, 5) = SavedUserBenefits
Cells(21, 5) = EmissionsSavings
End Sub
Oct 6 '08 #1
Share this Question
Share on Google+
1 Reply


P: 58
Your method of declaring variables is working against you.
Variables declared within a sub are only usable within that sub.
Variables declared at the top of the module before all subs are public variables that can be used in all subs.

Using "Option Explicit" as the first line in a module generates a msg when you try to use a variable that hasn't been declared. It is good practice to have that line in every module!

A "Long" data type can only contain integers. If you want to use floating point numbers use a "Double" data type.

One of the reasons you are getting an error msg is you appear to be trying to divide by zero in the line...
"SOxBenefits = (FTEMilesPerYear * SOxEmissionsMile * SOxCost) / SOx2EmissionsTon"
Note that "SOxEmissionsTon" (variable) is not the same as "SOx2EmissionsTon" (variable).
Using "Option Explicit" would have identified that issue for you.
Oct 6 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.