Using user-defined functions in cells

[Robert Crandal]

What is the correct way to define a function that can be plugged
into any spreadsheet cell as a formula??

I defined a function as follows:

Public Function DoMyStuff ()
importantData = Sheet1.Range("A1").Value
importantData = importantData * 10
DoMyStuff = importantData ' Return a value
End Function

I then plugged my function into cell "B1" as follows:

=DoMyStuf()

Okay, now I noticed that when anybody edits the
contents of cell "A1" that my "DoMyStuff" is not getting
called again. Do I need some code in my "DoMyStuff"
function to detect if the contents of cell "A1" changed??

Thank you!

 

[Tim Williams]

Generally you want to make your inputs parameters of your UDF.
If they are not, your function won't recalculate when the inputs change (as
you saw).

Public Function DoMyStuff (rngIn)
DoMyStuff = rngIn * 10 ' Return a value
End Function

=DoMyStuf(A1)

Tim