assign value to range in VBA function

[Guest]

This code works in a VBA Sub but not in a VBA function
Dim wsPurchase As Worksheet
Set wsPurchase = Worksheets("Purchase")
Dim rCriteria As Range
Set rCriteria = wsPurchase.Range("A1:A2")
rCriteria.Offset(1, 0).Value = sSet

Is there a way to change a worksheet cell from within a VBA function?

 

[Niek Otten]

No. A function can only return a value (replace its call). You cannot change
anything in a worksheet or Excel's settings from a function.

 

[AnExpertNovice]

Not knowing what sSet was and assuming s meant string I put quotes around
sSet.

The code worked fine from both a Subroutine and Function, as was expected.
I'm using Excel 2002 SP3 under Win XP SP1.

 

[Guest]

Yes and your code should work the same way.... except if you are calling the
function from a cell ie you are using the function as a worksheet function
(in C4: =MyFunc(...) ) in which case changing the sheet/cells through the
function is not allowed.

 

[tony h]

A function can only change the value in the cell from which it i
called.

There are several answers to what you want to do but a bit mor
information on exactly what would help

 

[Niek Otten]

< except if you are calling the function from a cell>

A very useful addition! I tend to forget that and always assume functions
are called from a worksheet.

Thanks!

 

[Guest]

I am using the function as a worksheet function C4: =MyFunc(...)
I am trying to set the criteria dynamically each time the MyFunc(sSet) is
called
total = Application.WorksheetFunction.DSum(rDB, rColumn, rCriteria)