K
Keith R
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
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