Runtime error 1004 write data to a cell

[Andreas Weber]

Hello

Always i get an error 1004 on the function above and i can't find my
mistake. I want to read values from the cell an write the steeps in an other
cell. On acces to the cell i get always error 1004.

Option Explicit

Function CalcTest(col Val As Integer, colRes As Integer) As Double
On Error GoTo ErrorHandle

Dim res As Double
Dim sum As Double
Dim i As Long
Dim errNr As Integer

res = 1
sum = 0
For i = 5 To 15
res = Cells(i, colVal).Value 'Read Value
sum = sum + res
Cells(i, colRes).Value = sum 'Write Result, here comes the
error 1004
Next i
CalcTest = sum
Exit Function

ErrorHandle:
errNr = Err.Number
Resume Next

End Function


Thanks for all help, i am very confused about this problem. I need this code
for an other function.

Andreas

 

[B Lynn B]

I pasted your code directly into a module in a blank workbook and tested it.
I did make one edit, removing the space from "col Val", but I'm assuming that
is not in the code you're actually running, because it errors on calling the
function with the space left in. In my test, it worked just fine.

Do you have any protections set on the sheet? That could cause the error.
If that is the source of the problem, you can solve by setting protection
with UserInterfaceOnly property set to True.

 

[Gary Brown]

it runs fine for me AFTER I change the col Val to ColVal

 

[Andreas Weber]

On my function colVal is together, it was a faut on copy the code this
window. But on my system the code doesn't work correct. I have excel 2007 sp2
german version. The worksheet isn't protected. I can't explain this problem
for me. I have tested it on 2 systems win7 and winxp.

Hope you can give me any idee

Greetings

Andreas

 

[B Lynn B]

Maybe we can give better help if you provide more detail around the
conditions under which you get the error. Does the code ALWAYS error
regardless where or how it is run? Are you using the function in a formula
in your spreadsheet? Or are you calling the function through other code?
When you call it, is the active sheet the same one where you want the value
of "sum" to be written?

 

[Andreas Weber]

I use the code as an user defined function in the active worksheet. The
function calc the sum and give it return. But the cells on the result column
are empty. I set an an breakpoint in the function and then i can see that on
these line
Cells(i, colRes).Value = sum 'Write Result, here comes the
the error 1004 comes. On the next steep I install an english version on an
other system.

Andreas

 

[B Lynn B]

If you want to send me a copy I can see if I can determine the problem that
way. Post reply here if you do that - then I'll know to go look for it.

bbras612 at comcast dot net

 

[Andreas Weber]

Yes it is possible to send you a copy of an excel workbook. Where can I send
the file.
Thanks for your help.
My Mail is a_aweber(at)bluewin.ch

Andreas

 

[Andreas Weber]

Thanks for your help. Now it works fine. The faut was that I made an call of
an UDF and not a call with an event. Now I insert a button an call the
function from these button. Thank you very mutch.

Andreas

 

[Andreas Weber]

Thank you for your help. Now it works fine. It was the problem with the UDF.
For all others here the helpful answer from bras

So when I and the other person who responded on the message board ran the
code as code, then of course we didn't see the problem. I don't really make
UDF's to use in formulas, so wasn't aware of these rules. Essentially this
indicates your final solution will need to somehow involve calling the
function with code rather than in a cell formula. Maybe it would be
worthwhile thinking about trying to use the Worksheet_Change event to make
that happen

Limitations of UDF's
• Cannot "record" an Excel UDF like you can an Excel macro.
• More limited than regular VBA macros. UDF's cannot alter the
structure or format of a worksheet or cell.
• If you call another function or macro from a UDF, the other macro
is under the same limitations as the UDF.
• Cannot place a value in a cell other than the cell (or range)
containing the formula. In other words, UDF's are meant to be used as
"formulas", not necessarily "macros".
• Excel user defined functions in VBA are usually much slower than
functions compiled in C++ or FORTRAN.
• Often difficult to track errors.
• If you create an add-in containing your UDF's, you may forget that
you have used a custom function, making the file less sharable.
• Adding user defined functions to your workbook will trigger the
"macro" flag (a security issue: Tools > Macros > Security...).

Andreas