Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More UDF problems; some won't update, some won't stop running!
I have about 6 UDFs total, and use them in many cells in my workbook. I have three problems: (1) any time I have any problem with the code (e.g. I interrupt any code, even if it is in a different UDF) all my cells with UDF's return the result of #####. I have added application.volatile to each, and tried things like pressing F9 to recalc, and the only way I have found to reset those cells is to enter the cell, place the cursor at the end of the UDF (not making any changes), and hit enter to force a recalc. Is there a better/easier way? I have donzens and dozens of these cells, and it takes forever to reset them all. (2) I have a UDF that has 2 source ranges (each is only one cell). I want the UDF to update _only_ when one of those two cells changed, but it triggers at other times anyway. This UDF updates a boatload of named ranges, and it really slows down the workbook when it runs. (3) Below is an exerpt from the UDF in #2; when it does run, it should only update the named range for the XAxis once (all graphs point to the same named range for the X-axis) and I don't have any loop programmed in, but it loops through from the beginning of the UDF to reset the XAxis, then starts again at the top of the UDF, more than a dozen times before it continues with the rest of the named ranges. I have _no_ idea what would cause this behavior, unless updating the named range somehow triggers a recalc event and restarts the macro from the top, but I've never seen that kind of behavior before- and if it was resetting it, I'd think it would become a permanent loop and I'd have to Ctrl-Break to get out of it, but after some number of cycles, it continues on it's own and sets the rest of the named ranges! Many, many, MANY thanks to anyone who can help- Keith R XL 97 ---------------------------------------------------------------------------- ------------------------------ Public Function ChangeGraph(GType As Range, ActiveSite As Range) As Integer 'assume activesite stays constant and GType is changed to "2" If GType.Value = 1 Then <snip ElseIf GType.Value = 2 Then ActiveWorkbook.Names.Add Name:="GraphsXAxis", _ RefersTo:="=(" & Evaluate(Names("active").Value) & "!$A$8:$A$31)" 'it gets this far, then restarts at the top and re-evaluated Gtype and 'resets/re-adds GraphsXAxis again and again 'then eventually it hits the next line and loops as expected For i = 1 To 14 itxt = Trim(Str(i)) If Len(itxt) = 1 Then itxt = "0" & itxt TempCol = ColumnLetter(3 + (i - 1)) 'data starts in C TempCol2 = ColumnLetter(18 + (i - 1)) 'target data starts in Column+15 'MsgBox TempCol & Chr(13) & TempCol2 ActiveWorkbook.Names.Add Name:="Graph" & itxt & "_A", _ RefersTo:="=(" & Evaluate(Names("active").Value) & _ "!$" & TempCol & "$" & "8" & ":$" & TempCol & "$" & "31" & ")" ActiveWorkbook.Names.Add Name:="Graph" & itxt & "_T", _ RefersTo:="=(" & Evaluate(Names("active").Value) & _ "!$" & TempCol2 & "$" & "8" & ":$" & TempCol2 & "$" & "31" & ")" ActiveWorkbook.Names.Add Name:="Graph" & itxt & "_YTD", _ RefersTo:="=(" & Evaluate(Names("active").Value) & _ "!$" & TempCol & "$" & "38" & ":$" & TempCol & "$" & "61" & ")" Next Else MsgBox "Only values of 1 or 2 can be accepted in ChangeGraph Function", , "Error: Value Out Of Range" End If End If ChangeGraph = 0 End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop running macros everytime I run excel? | Excel Discussion (Misc queries) | |||
How do I stop a Macro from running? | Excel Worksheet Functions | |||
How do I stop spanner from running in Excel. | Excel Discussion (Misc queries) | |||
How to stop getting the file save box when running a macro | Excel Discussion (Misc queries) | |||
How to Pause or Stop a running Macro | Excel Programming |