Cannot assign Cell a value in module function

[Nick]

I've created a very simple module function test() that simply displays
the name of the ActiveSheet within a Text Box then attempts to assign
a value to a cell within the worksheet. When I define any cell within
my worksheet as
"=test()", the funtion fires. Here is the function:

Public Function test()
On Error GoTo ErrDump

MsgBox ("ActiveSheet: " & ActiveSheet.Name)
ActiveSheet.Range("D16").Value = 99.99

ErrDump:
MsgBox (Err.Description)

End Function


As a comparison, I also created a simple Command Button within the
same worksheet that also displays the name of the Active worksheet
within a Text Box then attempts to assign a value to a cell within the
worksheet. The following Subroutine executes when I click the button:

Private Sub CommandButton1_Click()
MsgBox ("ActiveSheet: " & ActiveSheet.Name)
ActiveSheet.Range("D15").Value = 99.99
End Sub


Here is the problem.

The first function displays the name of the worksheet, in my case
"Sheet1" then produces the following error on the cell assignment
statement:

"application-defined or object-defined error"

however, the command button code works perfectly. My question is
simple:


why?


Thanks,
Nick

 

[Ryan Poth]

Nick,

This is because VBA functions, by design, are not allowed to alter data on
your spreadsheet. Their purpose is to return data only. You may only alter
data in your spreadsheet through a macro (sub). This is why your *sub* works
and your *function* does not.

HTH,
Ryan