Home |
Search |
Today's Posts |
#1
|
|||
|
|||
writing a value to a particular cell from function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a value to a particular cell from function
"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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a value to a particular cell from function
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing a value to a particular cell from function
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help writing function | Excel Worksheet Functions | |||
Function writing help | Excel Worksheet Functions | |||
WRITING A WHAT IF FUNCTION | Excel Worksheet Functions | |||
Help Writing Function | Excel Worksheet Functions | |||
writing its own function | Excel Programming |