Can someone find this error?

  • Thread starter Thread starter jclark419
  • Start date Start date
J

jclark419

Hey everyone, I keep getting an error within my code and can't seem t
solve the problem. If you could all check it out and let me know. Firs
the code:


Code
-------------------

Option Explicit


Public Function styrene() As Double

Dim ExtCount As Integer, IntCount As Integer

ExtCount = 7

While ExtCount <= 15

For IntCount = 17 To 21

Dim additivereactor As Integer
additivereactor = ThisWorkbook.Worksheets("Input").Cells(IntCount, 4).Value

Dim results As Integer
results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value

If additivereactor = results Then
styrene = ThisWorkbook.Worksheets("calculations").Cells((IntCount - 15), 6).Value + styrene
Else
styrene = styrene + 0
End If

ThisWorkbook.Worksheets("Calculations").Cells(20, ExtCount).Value = styrene


Next IntCount

ExtCount = ExtCount + 1

Wend

End Function

-------------------


additivereactor and results are always integers. The error is this lin
of code:


Code
-------------------

results = ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value

-------------------


Any help is appreciated.

~Jaso
 
There are a couple of problems here.

1. The line of code that is bombing... are the values in
ThisWorkbook.Worksheets("Calculations").Cells(19, ExtCount).Value all
integers? If not this will bomb out. you are safest to bring in the values as
strings and validate them as integers then convert them using CInt.

2. What is the line styrene = styrene + 0 supposed to do?

3. The line ThisWorkbook.Worksheets("Calculations").Cells(20,
ExtCount).Value = styrene is not valid in a function. Functions return
values. They do not modify sheets.
 
Just some added information:
3. The line ThisWorkbook.Worksheets("Calculations").Cells(20,
ExtCount).Value = styrene is not valid in a function. Functions return
values. They do not modify sheets.
Click to expand...

The term "function" used in this statement is pertinent only to a user
defined function used in a worksheet or called by such a function. Used as
a function in VBA, there is no such restriction.
 
Jim Thomlinson said:
The line ThisWorkbook.Worksheets("Calculations").Cells(20,
ExtCount).Value = styrene is not valid in a function. Functions return
values. They do not modify sheets.
Click to expand...

Only when called from the worksheet.

When called from a Sub/Function, it's perfectly legal for a function to
change a cell value (as long as the Sub/Function wasn't called from the
worksheet).
 
Very true. Since it is declared publicly it is available as a UDF so I added
the comment (probably should have been more specific). Thanks Tom & JE.
 
Back
Top