 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008How to fill cells from User Defined Function?
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 ----------------- 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.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello! > [quoted text clipped - 46 lines] > End Sub > ----------------- 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. SignatureKind 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. [quoted text clipped - 49 lines] | > End Sub | > ----------------- I wasn't going to ask Niek <bg> Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > 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 [quoted text clipped - 62 lines] > | > End Sub > | > ----------------- 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. 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. SignatureKind 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. "Niek Otten" <nicol...@xs4all.nl> wrote... >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. Thanks, Harlan! I won't try SignatureKind regards, Niek Otten Microsoft MVP - Excel | "Niek Otten" <nicol...@xs4all.nl> wrote... | >Just in case you were going to ask, they can't indirectly either. [quoted text clipped - 6 lines] | fragile, outside the recalc apparatus, possibly subject to VERY | noticeable delays, and a bad idea, but it is possible. A function may not change anything in a worksheet (value, format, etc). All it can do is return its value best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > Hello! > [quoted text clipped - 46 lines] > End Sub > ----------------- 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. SignatureGary''s Student - gsnu200765 > Hello! > [quoted text clipped - 46 lines] > End Sub > ----------------- Clever! Let's keep it a secret! SignatureKind regards, Niek Otten Microsoft MVP - Excel | You can get a UDF to change more than one cell with the help of a helper macro: | [quoted text clipped - 83 lines] | > End Sub | > ----------------- "Niek Otten" <nicol...@xs4all.nl> wrote... >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 <GarysStud...@discussions.microsoft.com> wrote... >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: [quoted text clipped - 16 lines] >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. 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. SignatureGary''s Student - gsnu200766 > Gary''s Student <GarysStud...@discussions.microsoft.com> wrote... > >You can get a UDF to change more than one cell with the help of a [quoted text clipped - 40 lines] > about it is, er, unhelpful. As always, testing code before posting it > is a good habit to develop. Gary''s Student <GarysStud...@discussions.microsoft.com> wrote... ... > . . . 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? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.