1
I consistently get an overflow error at the "SOxBenefit s =" (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").Se lect
Application.Got o Reference:="SS2 BikeStorage"
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 DepthBackfillCo mpact 'Depth to be backfilled and compacted
Dim UnitCostBackfil lCompact '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 CostShowerChang ing
Dim CostLandscaping CurbingDrainage
Dim CostImportedFil l
Dim ConstructionCos ts
Dim WaterCosts
Dim SavedUserBenefi ts
Dim EmissionsSaving s
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
DepthBackfillCo mpact = Cells(8, 2) 'Depth to be backfilled and compacted
UnitCostBackfil lCompact = 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 [$]
CostShowerChang ing = Cells(14, 3)
CostLandscaping CurbingDrainage = Cells(15, 3)
CostImportedFil l = Cells(16, 3)
End Sub
Sub Analysis()
'internal cost variables
Dim TotalAsphaltCos t ' 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 AsphaltUnitWeig ht = 150 '150 lbs/cubic foot
'Construction costs
SpaceArea = 1.533 * 18 * 9 'assuming 18'x9' spaces and 53% aisle area
TotalAsphaltCos t = DepthAsphalt * SpaceArea * AsphaltUnitWeig ht * UnitCostAsphalt / (12 * 2000) '
TotalSealCost = SealerFactor * UnitCostSeal * SpaceArea / 9
TotalExcavCost = (SpaceArea * DepthExcav * UnitCostExcav) + (SpaceArea * DepthBackfillCo mpact * UnitCostBackfil lCompact)
TotalSavedConst ructionCosts = FTE * (TotalAsphaltCo st + TotalSealCost + TotalExcavCost)
ConstructionCos ts = CostRack + CostShowerChang ing + CostLandscaping CurbingDrainage + CostImportedFil l - TotalSavedConst ructionCosts 'interior changing + landscaping/curbing + bike racks
'Operations Costs
'Water
Const ShowersPerDay = 15
Const MinutesPerShowe r = 5
Const GallonsPerMinut e = 2
Const WorkdaysPerYear = 250 '5 * 50
Const GallonsPerCubic Foot = 7.48
Const CostPerCubicFoo t = 0.64
Const PGivenA = 23.88 '(for i=3.4%, n=50)
Dim GallonsPerYear
Dim WaterCosts
'GallonsPerYear = ShowersPerDay * MinutesPerShowe r * GallonsPerMinut e * WorkdaysPerYear
GallonsPerYear = 37500
WaterCosts = GallonsPerYear * CostPerCubicFoo t * PGivenA / GallonsPerCubic Foot
'Pavement resurfacing
Const AsphaltResurfac ingCost = 1.25 'cost/ft^2
SavedResurfacin g = FTE * SpaceArea * AsphaltResurfac ingCost * (0.61 + 0.37)
'Emissions Costs
'CO2
Const CO2EmissionsMil e = 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 * CO2EmissionsMil e * CO2Cost / CO2EmissionsTon
'NOx
Const NOxEmissionsMil e = 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 = (FTEMilesPerYea r * NOxEmissionsMil e * NOxCost) / NOxEmissionsTon
'SOx
Dim SOxEmissionsMil e As Long 'average emission rate of SOx in grams/mi from
SOxEmissionsMil e = 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 = (FTEMilesPerYea r * SOxEmissionsMil e * SOxCost) / SOx2EmissionsTo n
'PM-10
Const PM10EmissionsMi le = 0.0052 'average emission rate of PM10 in grams/mi from
Dim PM10EmissionsTo n
PM10EmissionsTo n = 454 * 200 'grams PM10 per ton PM10
Const PM10Cost = 46148 'average cost of PM10 credits per ton PM10
PM10Benefits = FTEMilesPerYear * PM10EmissionsMi le * PM10Cost / PM10EmissionsTo n
'Total emissions savings
EmissionsSaving s = CO2Benefits + NOxBenefits + PM10Benefits
'Saved User Costs (gas and vehicle maintenance)
Const FedReimbursemen tRate = 0.485
SavedUserBenefi ts = FedReimbursemen tRate * FTEMilesPerYear
End Sub
Sub Output()
Cells(18, 5) = ConstructionCos ts
Cells(19, 5) = WaterCosts
Cells(20, 5) = SavedUserBenefi ts
Cells(21, 5) = EmissionsSaving s
End Sub
