Help with Range.Value2, Range.Text and UDFs
Hi, I'm creating an Excel application level solution (using AddIn and UDF classes). I have 2 UDFs: Cache() and AnotherFunction(). The Cache() function inits/creates a cache on a remote server, which returns an ID that can be referenced by AnotherFunction(). The AddIn is set to find all Cache() functions and run them when a workbook is loaded (ensure that the caches are initialized on the server). Of course, any AnotherFunctions() functions that references the Cache() function will also update (when the Application.Calculation is set to Auto). But here's the scenario that's baffling me: AddIn: Finds a Cache() function, inserts it. I then do a quick test to check the values of the range (Range.Value2, Range.Text, Range.Formula), all which are correctly populated. UDF: Since an AnotherFunction() function refers to the Cache() function, the AnotherFunction's UDF is executed once the Cache() function returns. However, when I try to retrieve the Range.Value2 from the parameter, I get null. Only Range.Text and Range.Formula is populated. Am I missing a step? Why is Value2 null in the UDF, but correctly populated in the AddIn? Please help. Thanks. |
Help with Range.Value2, Range.Text and UDFs
..Value and .Value2 get set to Empty during a calculation event when the cell
is uncalculated. see http://www.decisionmodels.com/calcsecretsj.htm ..Text only gets reset after calculation when the formatting layer gets refreshed. Using .text in Addins or UDFs is dangerous because: - you may not get the current value if it has not yet been refreshed - if the user changes the formatting you may get an unexpected value Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "JsjsLim" wrote in message ... Hi, I'm creating an Excel application level solution (using AddIn and UDF classes). I have 2 UDFs: Cache() and AnotherFunction(). The Cache() function inits/creates a cache on a remote server, which returns an ID that can be referenced by AnotherFunction(). The AddIn is set to find all Cache() functions and run them when a workbook is loaded (ensure that the caches are initialized on the server). Of course, any AnotherFunctions() functions that references the Cache() function will also update (when the Application.Calculation is set to Auto). But here's the scenario that's baffling me: AddIn: Finds a Cache() function, inserts it. I then do a quick test to check the values of the range (Range.Value2, Range.Text, Range.Formula), all which are correctly populated. UDF: Since an AnotherFunction() function refers to the Cache() function, the AnotherFunction's UDF is executed once the Cache() function returns. However, when I try to retrieve the Range.Value2 from the parameter, I get null. Only Range.Text and Range.Formula is populated. Am I missing a step? Why is Value2 null in the UDF, but correctly populated in the AddIn? Please help. Thanks. |
Help with Range.Value2, Range.Text and UDFs
I can't tell which classes the data structure belong to by your description and which classes are private and which classes are public. Usually excel defaults all classes as private. If a data structure is shared between two different classes it must be made public. "JsjsLim" wrote: Hi, I'm creating an Excel application level solution (using AddIn and UDF classes). I have 2 UDFs: Cache() and AnotherFunction(). The Cache() function inits/creates a cache on a remote server, which returns an ID that can be referenced by AnotherFunction(). The AddIn is set to find all Cache() functions and run them when a workbook is loaded (ensure that the caches are initialized on the server). Of course, any AnotherFunctions() functions that references the Cache() function will also update (when the Application.Calculation is set to Auto). But here's the scenario that's baffling me: AddIn: Finds a Cache() function, inserts it. I then do a quick test to check the values of the range (Range.Value2, Range.Text, Range.Formula), all which are correctly populated. UDF: Since an AnotherFunction() function refers to the Cache() function, the AnotherFunction's UDF is executed once the Cache() function returns. However, when I try to retrieve the Range.Value2 from the parameter, I get null. Only Range.Text and Range.Formula is populated. Am I missing a step? Why is Value2 null in the UDF, but correctly populated in the AddIn? Please help. Thanks. |
Help with Range.Value2, Range.Text and UDFs
What's happened here is: I have an addin class (deals with the office fluent ribbon bar, custom task panes, etc), and a UDF class (adds user-defined functions into Excel). The UDF class provides 2 functions, one of which returns a value that needs to be used by the other (in my case, a cache function that returns a cache id). The other function accepts the value in (either as a Excel Range object, or a string) via the parameter. If the value is an Excel Range, my code uses the range.Value2 property to retrieve the value in the range. -------------- At my addin level: when a workbook is loaded, it is scanned for these functions. If the addin finds a cache function, the function is executed again. This then sets off a chain event (due to Excel's Auto Calculation), where any dependent functions are updated as well (including the other function that I've mentioned above). The problem is, when the range (containing the cache function) is passed into the other function, the range.Value2 property returns null, even though I'm expecting it to be populated. Addin code: private void Application_WorkbookOpen(Workbook workbook) { foreach(Worksheet worksheet in workbook.Worksheets) { // simple sample of finding the first cache formula and updating its value Range cacheRange = worksheet.UsedRange.Find("=Cache", missing, missing...); // Reinsert formula cacheRange.Formula = cacheRange.Formula; // If you were to get the value of cacheRange.Value2, you'll see that the // it is NOT null, and correctly populated with the returned cache ID // Debug.Assert(cacheRange.Formula!=null); } } UDF code: public string Cache(...) { string cacheID = _client.InitializeCache(); return cacheID; } public string AnotherFunction(object cacheID,...) { string strCacheID; if (cacheID is string) { // String argument. strCacheID = (string)cacheID; } else if (cacheID is Range) { // Excel range argument // we need to extract the value from the range strCacheID = ((Range) cacheID).Value2; // note: But here's where my problem starts. Value2 always returns null, even // note: though it is clearly populated in the AddIn } } I hope my examples will make things a bit clearer. I know it's confusing, since it spans multiple classes, and I really appreciate any help. Thanks "Joel" wrote: I can't tell which classes the data structure belong to by your description and which classes are private and which classes are public. Usually excel defaults all classes as private. If a data structure is shared between two different classes it must be made public. "JsjsLim" wrote: Hi, I'm creating an Excel application level solution (using AddIn and UDF classes). I have 2 UDFs: Cache() and AnotherFunction(). The Cache() function inits/creates a cache on a remote server, which returns an ID that can be referenced by AnotherFunction(). The AddIn is set to find all Cache() functions and run them when a workbook is loaded (ensure that the caches are initialized on the server). Of course, any AnotherFunctions() functions that references the Cache() function will also update (when the Application.Calculation is set to Auto). But here's the scenario that's baffling me: AddIn: Finds a Cache() function, inserts it. I then do a quick test to check the values of the range (Range.Value2, Range.Text, Range.Formula), all which are correctly populated. UDF: Since an AnotherFunction() function refers to the Cache() function, the AnotherFunction's UDF is executed once the Cache() function returns. However, when I try to retrieve the Range.Value2 from the parameter, I get null. Only Range.Text and Range.Formula is populated. Am I missing a step? Why is Value2 null in the UDF, but correctly populated in the AddIn? Please help. Thanks. |
Help with Range.Value2, Range.Text and UDFs
ClassID needs to be a public variable. It is only defined inside the class cache. Even though cashe is public is doesn't mean the casheid is also public. "JsjsLim" wrote: What's happened here is: I have an addin class (deals with the office fluent ribbon bar, custom task panes, etc), and a UDF class (adds user-defined functions into Excel). The UDF class provides 2 functions, one of which returns a value that needs to be used by the other (in my case, a cache function that returns a cache id). The other function accepts the value in (either as a Excel Range object, or a string) via the parameter. If the value is an Excel Range, my code uses the range.Value2 property to retrieve the value in the range. -------------- At my addin level: when a workbook is loaded, it is scanned for these functions. If the addin finds a cache function, the function is executed again. This then sets off a chain event (due to Excel's Auto Calculation), where any dependent functions are updated as well (including the other function that I've mentioned above). The problem is, when the range (containing the cache function) is passed into the other function, the range.Value2 property returns null, even though I'm expecting it to be populated. Addin code: private void Application_WorkbookOpen(Workbook workbook) { foreach(Worksheet worksheet in workbook.Worksheets) { // simple sample of finding the first cache formula and updating its value Range cacheRange = worksheet.UsedRange.Find("=Cache", missing, missing...); // Reinsert formula cacheRange.Formula = cacheRange.Formula; // If you were to get the value of cacheRange.Value2, you'll see that the // it is NOT null, and correctly populated with the returned cache ID // Debug.Assert(cacheRange.Formula!=null); } } UDF code: public string Cache(...) { string cacheID = _client.InitializeCache(); return cacheID; } public string AnotherFunction(object cacheID,...) { string strCacheID; if (cacheID is string) { // String argument. strCacheID = (string)cacheID; } else if (cacheID is Range) { // Excel range argument // we need to extract the value from the range strCacheID = ((Range) cacheID).Value2; // note: But here's where my problem starts. Value2 always returns null, even // note: though it is clearly populated in the AddIn } } I hope my examples will make things a bit clearer. I know it's confusing, since it spans multiple classes, and I really appreciate any help. Thanks "Joel" wrote: I can't tell which classes the data structure belong to by your description and which classes are private and which classes are public. Usually excel defaults all classes as private. If a data structure is shared between two different classes it must be made public. "JsjsLim" wrote: Hi, I'm creating an Excel application level solution (using AddIn and UDF classes). I have 2 UDFs: Cache() and AnotherFunction(). The Cache() function inits/creates a cache on a remote server, which returns an ID that can be referenced by AnotherFunction(). The AddIn is set to find all Cache() functions and run them when a workbook is loaded (ensure that the caches are initialized on the server). Of course, any AnotherFunctions() functions that references the Cache() function will also update (when the Application.Calculation is set to Auto). But here's the scenario that's baffling me: AddIn: Finds a Cache() function, inserts it. I then do a quick test to check the values of the range (Range.Value2, Range.Text, Range.Formula), all which are correctly populated. UDF: Since an AnotherFunction() function refers to the Cache() function, the AnotherFunction's UDF is executed once the Cache() function returns. However, when I try to retrieve the Range.Value2 from the parameter, I get null. Only Range.Text and Range.Formula is populated. Am I missing a step? Why is Value2 null in the UDF, but correctly populated in the AddIn? Please help. Thanks. |
Help with Range.Value2, Range.Text and UDFs
I think there's been a misunderstanding. I'll try to explain my situation again: I have created 2 Excel functions. 1: Cache(), which returns a cache id 2: AnotherFunction(cacheId,...), which accepts a cache id as an argument Assume: A1: =Cache() A2: =AnotherFunction(A1,...) When the value in A1 updates (eg, =Cache() returns a new id), the AnotherFunction() gets called, with the A1 Excel Range object as the argument. AnotherFunction() then proceeds to extract the value from the Range object (via Range.Value2 property). My problem: I have an Excel addin which calls the =Cache() function when the workbook is loaded. Immediately after calling it, the A1 Excel range object is in the following state: Range.Formula: "=Cache()" Range.Text: "Cache_id_1" Range.Value2: "Cache_id_1" When the AnotherFunction() is called, the Excel range object is in the following state: Range.Formula: "=Cache()" Range.Text: "Cache_id_1" Range.Value2: null AnotherFunction() is accepting an Excel Range object (not one of my custom class), which is passed in by Excel because of the =AnotherFunction(A1,...) formula. So my question is, why are 2 objects (that supposedly represent the same A1 cell) have different Value2 values? What could cause it? And I how do I overcome it? Thanks "Joel" wrote: ClassID needs to be a public variable. It is only defined inside the class cache. Even though cashe is public is doesn't mean the casheid is also public. "JsjsLim" wrote: What's happened here is: I have an addin class (deals with the office fluent ribbon bar, custom task panes, etc), and a UDF class (adds user-defined functions into Excel). The UDF class provides 2 functions, one of which returns a value that needs to be used by the other (in my case, a cache function that returns a cache id). The other function accepts the value in (either as a Excel Range object, or a string) via the parameter. If the value is an Excel Range, my code uses the range.Value2 property to retrieve the value in the range. -------------- At my addin level: when a workbook is loaded, it is scanned for these functions. If the addin finds a cache function, the function is executed again. This then sets off a chain event (due to Excel's Auto Calculation), where any dependent functions are updated as well (including the other function that I've mentioned above). The problem is, when the range (containing the cache function) is passed into the other function, the range.Value2 property returns null, even though I'm expecting it to be populated. Addin code: private void Application_WorkbookOpen(Workbook workbook) { foreach(Worksheet worksheet in workbook.Worksheets) { // simple sample of finding the first cache formula and updating its value Range cacheRange = worksheet.UsedRange.Find("=Cache", missing, missing...); // Reinsert formula cacheRange.Formula = cacheRange.Formula; // If you were to get the value of cacheRange.Value2, you'll see that the // it is NOT null, and correctly populated with the returned cache ID // Debug.Assert(cacheRange.Formula!=null); } } UDF code: public string Cache(...) { string cacheID = _client.InitializeCache(); return cacheID; } public string AnotherFunction(object cacheID,...) { string strCacheID; if (cacheID is string) { // String argument. strCacheID = (string)cacheID; } else if (cacheID is Range) { // Excel range argument // we need to extract the value from the range strCacheID = ((Range) cacheID).Value2; // note: But here's where my problem starts. Value2 always returns null, even // note: though it is clearly populated in the AddIn } } I hope my examples will make things a bit clearer. I know it's confusing, since it spans multiple classes, and I really appreciate any help. Thanks "Joel" wrote: I can't tell which classes the data structure belong to by your description and which classes are private and which classes are public. Usually excel defaults all classes as private. If a data structure is shared between two different classes it must be made public. "JsjsLim" wrote: Hi, I'm creating an Excel application level solution (using AddIn and UDF classes). I have 2 UDFs: Cache() and AnotherFunction(). The Cache() function inits/creates a cache on a remote server, which returns an ID that can be referenced by AnotherFunction(). The AddIn is set to find all Cache() functions and run them when a workbook is loaded (ensure that the caches are initialized on the server). Of course, any AnotherFunctions() functions that references the Cache() function will also update (when the Application.Calculation is set to Auto). But here's the scenario that's baffling me: AddIn: Finds a Cache() function, inserts it. I then do a quick test to check the values of the range (Range.Value2, Range.Text, Range.Formula), all which are correctly populated. UDF: Since an AnotherFunction() function refers to the Cache() function, the AnotherFunction's UDF is executed once the Cache() function returns. However, when I try to retrieve the Range.Value2 from the parameter, I get null. Only Range.Text and Range.Formula is populated. Am I missing a step? Why is Value2 null in the UDF, but correctly populated in the AddIn? Please help. Thanks. |
Help with Range.Value2, Range.Text and UDFs
how many worksheets do you have in the workbook? The workbookopen is checking each worksheet. You are probably seeing the CacheRange truefor the 1st worksheet. The UDF function are looking at the worksheet where the worksheet formula is located and not another worksheet. I also wouldn't declare a variable called worksheet in the following statement foreach(Worksheet worksheet in workbook.Worksheets) "JsjsLim" wrote: I think there's been a misunderstanding. I'll try to explain my situation again: I have created 2 Excel functions. 1: Cache(), which returns a cache id 2: AnotherFunction(cacheId,...), which accepts a cache id as an argument Assume: A1: =Cache() A2: =AnotherFunction(A1,...) When the value in A1 updates (eg, =Cache() returns a new id), the AnotherFunction() gets called, with the A1 Excel Range object as the argument. AnotherFunction() then proceeds to extract the value from the Range object (via Range.Value2 property). My problem: I have an Excel addin which calls the =Cache() function when the workbook is loaded. Immediately after calling it, the A1 Excel range object is in the following state: Range.Formula: "=Cache()" Range.Text: "Cache_id_1" Range.Value2: "Cache_id_1" When the AnotherFunction() is called, the Excel range object is in the following state: Range.Formula: "=Cache()" Range.Text: "Cache_id_1" Range.Value2: null AnotherFunction() is accepting an Excel Range object (not one of my custom class), which is passed in by Excel because of the =AnotherFunction(A1,...) formula. So my question is, why are 2 objects (that supposedly represent the same A1 cell) have different Value2 values? What could cause it? And I how do I overcome it? Thanks "Joel" wrote: ClassID needs to be a public variable. It is only defined inside the class cache. Even though cashe is public is doesn't mean the casheid is also public. "JsjsLim" wrote: What's happened here is: I have an addin class (deals with the office fluent ribbon bar, custom task panes, etc), and a UDF class (adds user-defined functions into Excel). The UDF class provides 2 functions, one of which returns a value that needs to be used by the other (in my case, a cache function that returns a cache id). The other function accepts the value in (either as a Excel Range object, or a string) via the parameter. If the value is an Excel Range, my code uses the range.Value2 property to retrieve the value in the range. -------------- At my addin level: when a workbook is loaded, it is scanned for these functions. If the addin finds a cache function, the function is executed again. This then sets off a chain event (due to Excel's Auto Calculation), where any dependent functions are updated as well (including the other function that I've mentioned above). The problem is, when the range (containing the cache function) is passed into the other function, the range.Value2 property returns null, even though I'm expecting it to be populated. Addin code: private void Application_WorkbookOpen(Workbook workbook) { foreach(Worksheet worksheet in workbook.Worksheets) { // simple sample of finding the first cache formula and updating its value Range cacheRange = worksheet.UsedRange.Find("=Cache", missing, missing...); // Reinsert formula cacheRange.Formula = cacheRange.Formula; // If you were to get the value of cacheRange.Value2, you'll see that the // it is NOT null, and correctly populated with the returned cache ID // Debug.Assert(cacheRange.Formula!=null); } } UDF code: public string Cache(...) { string cacheID = _client.InitializeCache(); return cacheID; } public string AnotherFunction(object cacheID,...) { string strCacheID; if (cacheID is string) { // String argument. strCacheID = (string)cacheID; } else if (cacheID is Range) { // Excel range argument // we need to extract the value from the range strCacheID = ((Range) cacheID).Value2; // note: But here's where my problem starts. Value2 always returns null, even // note: though it is clearly populated in the AddIn } } I hope my examples will make things a bit clearer. I know it's confusing, since it spans multiple classes, and I really appreciate any help. Thanks "Joel" wrote: I can't tell which classes the data structure belong to by your description and which classes are private and which classes are public. Usually excel defaults all classes as private. If a data structure is shared between two different classes it must be made public. "JsjsLim" wrote: Hi, I'm creating an Excel application level solution (using AddIn and UDF classes). I have 2 UDFs: Cache() and AnotherFunction(). The Cache() function inits/creates a cache on a remote server, which returns an ID that can be referenced by AnotherFunction(). The AddIn is set to find all Cache() functions and run them when a workbook is loaded (ensure that the caches are initialized on the server). Of course, any AnotherFunctions() functions that references the Cache() function will also update (when the Application.Calculation is set to Auto). But here's the scenario that's baffling me: AddIn: Finds a Cache() function, inserts it. I then do a quick test to check the values of the range (Range.Value2, Range.Text, Range.Formula), all which are correctly populated. UDF: Since an AnotherFunction() function refers to the Cache() function, the AnotherFunction's UDF is executed once the Cache() function returns. However, when I try to retrieve the Range.Value2 from the parameter, I get null. Only Range.Text and Range.Formula is populated. Am I missing a step? Why is Value2 null in the UDF, but correctly populated in the AddIn? Please help. Thanks. |
All times are GMT +1. The time now is 06:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com