Is this valid???????

[EDrone]

I'm rather new to Excel programming, but I'm having problems doing what appears to something very simple. I want a formula to return the value of a calculation, AND set some values in some other cells. I've simplified down to two lines of code and the second assignment statement is causing me grief when I type the following in Cell A1
=MyFunc()

Function MyFunc() as Integer
MyFunc = 1
ActiveSheet.Range("A5").Value = 1
End Function

Error Returned :
Error 1004
Application-defined or object-defined error

Is this not valid? Or am I missing the boat on what you can do in Formulas?

This code works great if it was Sub, but I also can't figure out how to run the Sub as a formula. It appears Subs can only be run from menus, controls, etc... or through code.

Thanks for any help I can get.

 

[Emily]

Function MyFunc() as Integer
MyFunc = 1
ActiveSheet.Range("A5").Value = 1
End Function

It should be:
Function MyFunc() As Integer
MyFunc = ActiveSheet.Range("A5").Value
End Function

 

[Andrew Poulsom]

Function procedures used in worksheet formulas cannot manipulate ranges. They can only return a result. So:

ActiveSheet.Range("A5").Value = 1

will result in an error.

What is the relationship between the result of your function and the data in the other cells? Define it and use formulas in the other cells to reference the cell containing your function.

 

[EDrone]

Emily and Andrew. Thanks for your quick response and help.

Andrew,
I was afraid you would say that you couldn't manipulate a Range within a Function. My original goal was to have a function with multiple parameters (Cell references). The function would query a database and dump the results and return the recordcount as the function's return value. I wanted it to be dynamic so that if any of the cells used as parameters changed the recordset would change also. Is there a better way?

 

[Andrew Poulsom]

I don't really know. Couldn't you use the Sheet_Change event to run your query etc? Test on the cell that's changed using Target.Address.

 

[EDrone]

I'll give that a shot. Thanks again.