Thread: assign value to range in VBA function

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?

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.

--
Kind regards,

Niek Otten

"excelman" <excelman@discussions.microsoft.com> wrote in message
news:59143DD3-9F93-4F7C-9D6D-D1C9EFE8C1A2@microsoft.com...
> 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?
>

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

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

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.

--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"excelman" <excelman@discussions.microsoft.com> wrote in message
news:59143DD3-9F93-4F7C-9D6D-D1C9EFE8C1A2@microsoft.com...
> 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?
>

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.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"excelman" wrote:

> 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?
>

< 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!

--
Kind regards,

Niek Otten

"sebastienm" <sebastienm@discussions.microsoft.com> wrote in message
news:5F13B63B-27AA-4B42-ACE3-9C9EB99B39A7@microsoft.com...
> 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.
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "excelman" wrote:
>
>> 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?
>>

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)

"tony h" wrote:

>
> A function can only change the value in the cell from which it is
> called.
>
> There are several answers to what you want to do but a bit more
> information on exactly what would help.
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=510293
>
>