Hi,

I just started writting vba, request for help in solving some problem.

| C | D | E | F | G
---|----- |------|----|-----|--------------------------
4 | 1 | 2 | 3 | X | =calc_G(c4,D4,E4,F4)
---|-----|------|-----|---- |--------------------------
5 | 4 | 5 | 6 | Y | =calc_G(c5,D5,E5,F5)
---|---- |---- -|-----|---- -|--------------------------
6 | | | | Z |
| | | | |


I would like to write a function which returns some value to G5.
inside the function i also like to calculate a value that should be stored in F5.

that means the value of Y needs to be calcuated from the previous values of C,D,E and F rows
i have nearly 10 to 15 cases where i need to use these cases.

my question is this.

is there any way i can return a value from the function and also inside the function
some calculated value to be set to particular cell.


function test( args)
Dim var

cellXX = var -10
funtion = var

endfunction

"Spint" wrote:
is there any way i can return a value from the function
and also inside the function some calculated value to be
set to particular cell.

No, not in a VBA function per se. You could do that in a VBA sub(routine),
but not one that is invoked by an Excel calculation (through a VBA function)
directly or indirectly.

Generally, Excel does not permit a VBA function to change the state of Excel
worksheets directly or indirectly. There are some exceptions, corner-cases
really. I don't remember them off-hand.

yes, you can use a function to perform like a sub in vba. The difference with a sub is that the function should a return a value...

On Wednesday, July 25, 2012 8:55:14 AM UTC+1, joeu2004 wrote:
"Spint" > wrote:
> is there any way i can return a value from the function
> and also inside the function some calculated value to be
> set to particular cell.

No, not in a VBA function per se. You could do that in a VBA sub(routine),
but not one that is invoked by an Excel calculation (through a VBA function)
directly or indirectly.

Generally, Excel does not permit a VBA function to change the state of Excel
worksheets directly or indirectly. There are some exceptions, corner-cases
really. I don't remember them off-hand.

Clarification.... I wrote:
"Spint" wrote:
is there any way i can return a value from the function
and also inside the function some calculated value to be
set to particular cell.

No, not in a VBA function per se. You could do that in a VBA
sub(routine), but not one that is invoked by an Excel calculation
(through a VBA function) directly or indirectly.

Generally, Excel does not permit a VBA function to change the
state of Excel worksheets directly or indirectly.

I might not have said it clearly. I always have trouble explaining this.
It is __not__ as simple as "you cannot do this in a function" or "you can to
this only in a sub(routine)". It depends on the context in which the code
is executed.

Let me try again....

Generally, you cannot change Excel state -- for example, by changing the
value of a cell directly -- in VBA code when it is invoked from an Excel
formula directly or indirectly. It does not matter whether that VBA code is
in a function or sub(routine).

Conversely, you can change Excel state in VBA code that is not invoked from
an Excel formula directly or indirectly; for example, by executing a macro.

Consider the following example....

Function myFunc()
Call sub1
myFunc = 123
End Function

Sub mySub()
Call sub1
End Sub

Sub sub1()
Range("A1") = func1()
End Sub

Function func1()
Range("B1") = 456
func1 = 789
End Function

We get an error when we invoke sub1 and func1 indirectly using the Excel
formula =myFunc().

But there is no error when we invoke sub1 and func1 indirectly by executing
the macro mySub directly, for example by pressing alt+F8 in Excel.