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