473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Overflow error while coding in Excel

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
1 1784
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

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

Similar topics

2
by: middletree | last post by:
For my ASP app which tracks tech support tickets, I have a report which breaks down how much time each ticket spent in a particular status (there are 7 statuses, and I am not trying to find out how...
19
by: Jim | last post by:
I have spent the past few weeks designing a database for my company. The problem is I have started running into what I believe are stack overflow problems. There are two tab controls on the form...
2
by: Andy Davis | last post by:
Dear Group I am trying to automate process of adding a new record id in my form using the following code when the user clicks the "Add New Record" button. For example if the last record id is...
2
by: steve.turner | last post by:
Hi all, I'm hoping someone can stop me tearing (what's left of) my hair out over this problem... I have a linked Excel file from which I append the data to an empty local Access table. I...
0
by: Bob | last post by:
I hope somebody can help. I'm linking to an excel spreadsheet with several fields that come across as double. I can query the linked table fine, but when I make a form with the query as the...
3
by: MLH | last post by:
A wide comma delimited text file import into A97 failed with an overflow error. I'm estimating it to have been no more than 1000 records. 100% of all the fields were quoted strings or ZLS's. ...
0
by: snowdream1982 | last post by:
Dear all, I hit an error OVERFLOW but when I insert the sql statement below manually in the SQLEditor, it goes well, no errors at all. My coding: SQLInsertTempMf = "INSERT INTO tempmf " &...
7
by: LucasLondon | last post by:
Hi, I have the code below that I adapted to loop through each column of data (Columns A to GR) to perform an a calculation based on the data in rows 2 to 31 of each column and place the...
1
by: Scott M. | last post by:
Many methods return objects when they are called. With Excel these objects are placed in memory and must be destroyed via ReleaseComObject as you have done with your NAR method, but the line: ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.