How to fill cells from User Defined Function?

[Billy]

Hello!

I created user defined function (myFunc) which take two arguments,
make some calculations and return back result to cell from where was
called. Until here everyting work fine.

Problem is here: At the time when I make calculation I have to write
some values to other sheet (e.g. Summary) in workbook and here I got
error 1004. Why I am doing wrong?. I know that has to be some little
trick. If I call the same over sub procedure (TestOk), everything work
normally.

Below is my sample code from the Excel module. That code will be
actullay called from XLA Add-in.

Regards,
Billy

-----------------
Function myFunc(intValue1 As Integer, intValue2 As Integer) As Integer
Dim oshSummary As Worksheet, intTempValue As Integer

On Error GoTo myError
Set oshSummary = ActiveWorkbook.Sheets("Summary")
If intValue1 < 10 Then
intTempValue = intValue1 * 2 + intValue2

'Why I get error??: 1004 - 'Application-defined or object-defined
error
oshSummary.Range("A1").Value = intValue1
oshSummary.Range("A2").Value = intValue2
Else
intTempValue = intValue1 + intValue2
End If

Set oshSummary = Nothing
myFunc = intTempValue

myExit:
Exit Function
myError:
Debug.Print "Err>myFunc: " & Err.Number & " - '" & Err.Description
Resume myExit
End Function

Sub TestOk()
Sheets("Summary").Range("A1").Value = 10
Sheets("Summary").Range("A2").Value = 20
End Sub
-----------------

 

[Bob Phillips]

You are not doing anything wrong, that is how UDFs work. They can return a
value to a cell, but they cannot change other cells.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernard Liengme]

A function may not change anything in a worksheet (value, format, etc).
All it can do is return its value
best wishes

 

[Niek Otten]

Just in case you were going to ask, they can't indirectly either. So no use trying to call a Sub that changes worksheets from a
Function that was called from a worksheet formula.
Functions, called from VBA (not via a worksheet formula) however, can change worksheets. Unfortunately, I must say; it drifts away
from the mathematical conventions for functions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| You are not doing anything wrong, that is how UDFs work. They can return a
| value to a cell, but they cannot change other cells.
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my addy)
|
|
|
| | > Hello!
| >
| > I created user defined function (myFunc) which take two arguments,
| > make some calculations and return back result to cell from where was
| > called. Until here everyting work fine.
| >
| > Problem is here: At the time when I make calculation I have to write
| > some values to other sheet (e.g. Summary) in workbook and here I got
| > error 1004. Why I am doing wrong?. I know that has to be some little
| > trick. If I call the same over sub procedure (TestOk), everything work
| > normally.
| >
| > Below is my sample code from the Excel module. That code will be
| > actullay called from XLA Add-in.
| >
| > Regards,
| > Billy
| >
| > -----------------
| > Function myFunc(intValue1 As Integer, intValue2 As Integer) As Integer
| > Dim oshSummary As Worksheet, intTempValue As Integer
| >
| > On Error GoTo myError
| > Set oshSummary = ActiveWorkbook.Sheets("Summary")
| > If intValue1 < 10 Then
| > intTempValue = intValue1 * 2 + intValue2
| >
| > 'Why I get error??: 1004 - 'Application-defined or object-defined
| > error
| > oshSummary.Range("A1").Value = intValue1
| > oshSummary.Range("A2").Value = intValue2
| > Else
| > intTempValue = intValue1 + intValue2
| > End If
| >
| > Set oshSummary = Nothing
| > myFunc = intTempValue
| >
| > myExit:
| > Exit Function
| > myError:
| > Debug.Print "Err>myFunc: " & Err.Number & " - '" & Err.Description
| > Resume myExit
| > End Function
| >
| > Sub TestOk()
| > Sheets("Summary").Range("A1").Value = 10
| > Sheets("Summary").Range("A2").Value = 20
| > End Sub
| > -----------------
|
|

 

[Bob Phillips]

I wasn't going to ask Niek <bg>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Billy]

I know that function can have only one exit, but I was still hope that
I can do that task through function call because I can read any cell
in workbook when function is executing..

Thanks for all answers.

 

[Niek Otten]

Yes, you can, but you shouldn't. You should always access cells via the argument list. Otherwise Excel is not aware of the
dependencies and might not recalculate when you change those cells.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I know that function can have only one exit, but I was still hope that
| I can do that task through function call because I can read any cell
| in workbook when function is executing..
|
| Thanks for all answers.

 

[Harlan Grove]

Just in case you were going to ask, they can't indirectly either.

....

Picky: it CAN do it indirectly, but it involves a time delay. udfs can
launch other processes via VBA's Shell function, and the launched
process could connect to the running Excel session via Automation, and
then it could change any- and everything in the Excel session. It's
fragile, outside the recalc apparatus, possibly subject to VERY
noticeable delays, and a bad idea, but it is possible.

 

[Gary''s Student]

You can get a UDF to change more than one cell with the help of a helper macro:

Here is an example of a UDF that returns a value to the cell containing it.
It will also cause an update to cell C1:


In a standard module:

Public triggger As Boolean
Public carryover As Variant
Function reallysimple(r As Range) As Variant
triggger = True
reallysimple = r.Value
carryover = r.Value / 99
End Function


In worksheet code:

Private Sub Worksheet_Calculate()
If Not triggger Then Exit Sub
triggger = False
Range("C1").Value = carryover
End Sub

Whenever reallysimple is called it returns a value. It also sets the global
flag triggger and the global variable carryover.

Now the event macro runs whenever the worksheet is calculated. As soon as
it sees that triggger has become true, it knows that reallysimple has been
executed and there is work to do. It clears triggger and moves carryover to
cell C1.

So even though a UDF can only directly change a single cell, it can
indirectly change many cells (with a little help from its friends).


Note that in this example, it is easier to just put the correct fomula in C1
directly.

 

[Niek Otten]

Thanks, Harlan!
I won't try

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| >Just in case you were going to ask, they can't indirectly either.
| ...
|
| Picky: it CAN do it indirectly, but it involves a time delay. udfs can
| launch other processes via VBA's Shell function, and the launched
| process could connect to the running Excel session via Automation, and
| then it could change any- and everything in the Excel session. It's
| fragile, outside the recalc apparatus, possibly subject to VERY
| noticeable delays, and a bad idea, but it is possible.

 

[Niek Otten]

Clever!

Let's keep it a secret!


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| You can get a UDF to change more than one cell with the help of a helper macro:
|
| Here is an example of a UDF that returns a value to the cell containing it.
| It will also cause an update to cell C1:
|
|
| In a standard module:
|
| Public triggger As Boolean
| Public carryover As Variant
| Function reallysimple(r As Range) As Variant
| triggger = True
| reallysimple = r.Value
| carryover = r.Value / 99
| End Function
|
|
| In worksheet code:
|
| Private Sub Worksheet_Calculate()
| If Not triggger Then Exit Sub
| triggger = False
| Range("C1").Value = carryover
| End Sub
|
| Whenever reallysimple is called it returns a value. It also sets the global
| flag triggger and the global variable carryover.
|
| Now the event macro runs whenever the worksheet is calculated. As soon as
| it sees that triggger has become true, it knows that reallysimple has been
| executed and there is work to do. It clears triggger and moves carryover to
| cell C1.
|
| So even though a UDF can only directly change a single cell, it can
| indirectly change many cells (with a little help from its friends).
|
|
| Note that in this example, it is easier to just put the correct fomula in C1
| directly.
| --
| Gary''s Student - gsnu200765
|
|
| "Billy" wrote:
|
| > Hello!
| >
| > I created user defined function (myFunc) which take two arguments,
| > make some calculations and return back result to cell from where was
| > called. Until here everyting work fine.
| >
| > Problem is here: At the time when I make calculation I have to write
| > some values to other sheet (e.g. Summary) in workbook and here I got
| > error 1004. Why I am doing wrong?. I know that has to be some little
| > trick. If I call the same over sub procedure (TestOk), everything work
| > normally.
| >
| > Below is my sample code from the Excel module. That code will be
| > actullay called from XLA Add-in.
| >
| > Regards,
| > Billy
| >
| > -----------------
| > Function myFunc(intValue1 As Integer, intValue2 As Integer) As Integer
| > Dim oshSummary As Worksheet, intTempValue As Integer
| >
| > On Error GoTo myError
| > Set oshSummary = ActiveWorkbook.Sheets("Summary")
| > If intValue1 < 10 Then
| > intTempValue = intValue1 * 2 + intValue2
| >
| > 'Why I get error??: 1004 - 'Application-defined or object-defined
| > error
| > oshSummary.Range("A1").Value = intValue1
| > oshSummary.Range("A2").Value = intValue2
| > Else
| > intTempValue = intValue1 + intValue2
| > End If
| >
| > Set oshSummary = Nothing
| > myFunc = intTempValue
| >
| > myExit:
| > Exit Function
| > myError:
| > Debug.Print "Err>myFunc: " & Err.Number & " - '" & Err.Description
| > Resume myExit
| > End Function
| >
| > Sub TestOk()
| > Sheets("Summary").Range("A1").Value = 10
| > Sheets("Summary").Range("A2").Value = 20
| > End Sub
| > -----------------
| >

 

[Harlan Grove]

You can get a UDF to change more than one cell with the help of a
helper macro:

Actually it'd have to be either the worksheet's Calculate event
handler or the workbook's SheetCalculate event handler. Standard
macros won't work.

Here is an example of a UDF that returns a value to the cell
containing it. It will also cause an update to cell C1:

In a standard module:

Public triggger As Boolean
Public carryover As Variant
Function reallysimple(r As Range) As Variant
triggger = True
reallysimple = r.Value
carryover = r.Value / 99
End Function

In worksheet code:

Private Sub Worksheet_Calculate()
If Not triggger Then Exit Sub
triggger = False
Range("C1").Value = carryover
End Sub

....

Unless you disable events before then reactivate events after the C1
assignment, if calculation is automatic, your event handler would
cause an infinite loop if Excel didn't blow through the VBA call stack
because the C1 assignment will itself trigger the Calculate event. Not
good coding at all!

Even with bracketting disable/re-enable event coding, you still run
the not inconsiderable risk of repeated looping until you've blown
through VBA's call stack if any range argument to reallysimple in turn
contains a formula that refers to C1.

Avoiding circular recalculation is a PITA - failing to caution others
about it is, er, unhelpful. As always, testing code before posting it
is a good habit to develop.

 

[Harlan Grove]

Clever!

Let's keep it a secret!

....

Too bad it's now publicly memorialized in USENET.

So you believe Microsoft's inclusion of [Sheet]Calculate event
handlers was a huge mistake? Or you just don't believe anyone should
use them in ways you wouldn't yourself?

 

[Gary''s Student]

Hi Harlan:

Thanks for your comments. I investigated your concern about getting into an
infinite loop. I put a Msgbox at the very top of the event code to trap the
calculate event. On my system (Excel 2003 SP3 / Win XP) the event did not go
into a loop, whether the calculate mode was automatic or not.

The aproach is bad, however, even if the code works. It is better to
install additional functions in the other cells instead of relying on an
obscure event to fill them.

The concept of using public variable to signal other code elements is
valuable if you want to simulate throwing or catching exceptions.

b.t.w thank you for your comments and advise. In my world I only get
feedback on the speed of solutions, not their quality.

 

[Harlan Grove]

. . . On my system (Excel 2003 SP3 / Win XP) the event did not go
into a loop, whether the calculate mode was automatic or not.

....

You're right for simple use when none of the udf call refer to cells
in turn referring to C1. I didn't notice the triggger variable.

However, if B1 contains the formula =100+C1 and A1 contains the
formula =reallysimple(B1), it does loop. Change the event handler to

Private Sub Worksheet_Calculate()
MsgBox "event handler"
On Error GoTo CleanUp
Application.EnableEvents = False
Range("C1").Value = carryover
Application.Calculate
CleanUp:
Application.EnableEvents = True
End Sub

and it doesn't loop. Moral: don't rely on state variables.

The concept of using public variable to signal other code elements
is valuable if you want to simulate throwing or catching exceptions.

?

Why not use Err.Raise?