User Defined Forumla and persistence
Hi,
I have a new problem with Excel... and it appear to me in the world of the "User Defined Formulas". If I write a new formula like this (into a VBA module): Function TEST(RngName As String, Optional FncName As String = "SUM") As Variant Application.Volatile Dim TheCaller As Range Set TheCaller = Application.Caller Dim R As Range Set R = ActiveWorkbook.Names(RngName).RefersToRange TEST = Application.Evaluate(FncName & "(" & R.Address & ")") End Function and I use it in a sheet (e.g. "Sheet 1") it works fine... but when I go to another sheet (e.g. "Sheet 3") and I forse a recalculus (F9 shortcut) when I return to "Sheet 1" the function show the 0 (ZERO) value :( Why? Something idea? Thanks in adavance Eros Pedrini |
User Defined Forumla and persistence
Hi Eros,
When Application.Evaluate evaluates a string containing a range reference that is not qualified by a worksheet, it assumes it is on the active sheet. So when you change sheet you get a different answer. So you either need to use Worksheet.evaluate (which assumes the unqualified reference is on Worksheet) or use .address(External:=True) which gives you a qualified reference. For more limitations and advice on EVALUATE see http://www.DecisionModels.com/calcsecretsh.htm Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Eros Pedrini" wrote in message ... Hi, I have a new problem with Excel... and it appear to me in the world of the "User Defined Formulas". If I write a new formula like this (into a VBA module): Function TEST(RngName As String, Optional FncName As String = "SUM") As Variant Application.Volatile Dim TheCaller As Range Set TheCaller = Application.Caller Dim R As Range Set R = ActiveWorkbook.Names(RngName).RefersToRange TEST = Application.Evaluate(FncName & "(" & R.Address & ")") End Function and I use it in a sheet (e.g. "Sheet 1") it works fine... but when I go to another sheet (e.g. "Sheet 3") and I forse a recalculus (F9 shortcut) when I return to "Sheet 1" the function show the 0 (ZERO) value :( Why? Something idea? Thanks in adavance Eros Pedrini |
All times are GMT +1. The time now is 12:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com