Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Public Function Recalc Problem
I have written an add-in with several public functions that work fine when
used in a formula in a cell. But, when I save the workbook, close Excel, reopen Excel and open the workbook the formulas evaluate to #VALUE!. If I hit recalc it does nothing. If I reenter a formula or enter a formula in a new cell it works fine. How do I get the add-in to run my functions and show the result when it Excel opens or when a new workbook opens? Thanks, John Hutchins |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Public Function Recalc Problem
Hi John,
What do the functions look like and how are they called? Is Calculation set to Automatic? -- Kind regards, Niek Otten "John Hutcins" wrote in message ... I have written an add-in with several public functions that work fine when used in a formula in a cell. But, when I save the workbook, close Excel, reopen Excel and open the workbook the formulas evaluate to #VALUE!. If I hit recalc it does nothing. If I reenter a formula or enter a formula in a new cell it works fine. How do I get the add-in to run my functions and show the result when it Excel opens or when a new workbook opens? Thanks, John Hutchins |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Public Function Recalc Problem
Niek,
Here is an example of one of the functions. Public Function GpBr2Dept(Group, Branch) FindName 'this is a sub that identifies the addin name in case it is renamed when installed Test2GpBr2Dept 'this is a sub that calls a private sub that loads and sorts an array if it is not already loaded Dim i As Long Dim UB As Long Dim GpBr As String GpBr = UCase(Branch) If Len(GpBr) = 1 Then GpBr = "0" & GpBr GpBr = UCase(Group) & GpBr If Len(GpBr) = 3 Then GpBr = "0" & GpBr UB = UBound(BrData) Dim Y As Long Dim X As Long Y = UB i = 1 Do Until Y - i <= 10 X = Round((Y - i) / 2, 0) If BrData(i + X, 1) GpBr Then Y = Y - X Else If BrData(i + X, 1) < GpBr Then i = i + X Else Y = Y - X + 1 End If End If Loop For ii = i To Y If BrData(ii, 1) = GpBr Then GpBr2Dept = BrData(ii, 2) ii = Y End If Next ii End Function The function is looking up a translation value in a table stored in the addin. I'm using an array because I can get it to run faster than a VLookUp. If I use VLookup and copy down 1,000 rows it takes forever. Once the array loads and sorts for the 1st formula copying down 1,000 times runs very fast. I didn't copy in all of the other code called from the function. I can do that if it will help. But, there is a lot of it. Thanks for looking into this, John "Niek Otten" wrote: Hi John, What do the functions look like and how are they called? Is Calculation set to Automatic? -- Kind regards, Niek Otten "John Hutcins" wrote in message ... I have written an add-in with several public functions that work fine when used in a formula in a cell. But, when I save the workbook, close Excel, reopen Excel and open the workbook the formulas evaluate to #VALUE!. If I hit recalc it does nothing. If I reenter a formula or enter a formula in a new cell it works fine. How do I get the add-in to run my functions and show the result when it Excel opens or when a new workbook opens? Thanks, John Hutchins |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Public Function Recalc Problem
I've looked at this a little more and here is the specific problem. I want
to have a lot of people use this macro and I have found in the past they will tend to rename the addin file. I wrote a sub to read through the available addins to determine the name. It is then used in all of the code that looks up data in that .xla file. Here is the code Sub FindName() If ThisAddIn = "" Then Dim vaCnt As Integer vaCnt = Application.AddIns.Count For X = 1 To vaCnt On Error Resume Next If Workbooks(AddIns(X).Name).Worksheets.Count = 3 Then If Workbooks(AddIns(X).Name).Worksheets(1).Name = "Name" _ And Workbooks(AddIns(X).Name).Worksheets(2).Name = "Branches" _ And Workbooks(AddIns(X).Name).Worksheets(3).Name = "PT101P" _ And Workbooks(AddIns(X).Name).Worksheets(1).Cells(1, 1) = "ERAC_Universal_Translate" Then If Err = 0 Then ThisAddIn = AddIns(X).Name X = vaCnt End If End If End If Next X End If On Error GoTo 0 End Sub When the applicaton is opened and a file with a cell formula using one of the public functions is opened the function code runs but usually this line "vaCnt = Application.AddIns.Count" returns 0 and the name doesn't get set. if I enter ?Application.AddIns.Count in the Immediate Window it returns a number and the code then works properly. Is there any way to force this to work or do I just need to hard code the addin name and leave this alone? Thanks, John "John Hutcins" wrote: Niek, Here is an example of one of the functions. Public Function GpBr2Dept(Group, Branch) FindName 'this is a sub that identifies the addin name in case it is renamed when installed Test2GpBr2Dept 'this is a sub that calls a private sub that loads and sorts an array if it is not already loaded Dim i As Long Dim UB As Long Dim GpBr As String GpBr = UCase(Branch) If Len(GpBr) = 1 Then GpBr = "0" & GpBr GpBr = UCase(Group) & GpBr If Len(GpBr) = 3 Then GpBr = "0" & GpBr UB = UBound(BrData) Dim Y As Long Dim X As Long Y = UB i = 1 Do Until Y - i <= 10 X = Round((Y - i) / 2, 0) If BrData(i + X, 1) GpBr Then Y = Y - X Else If BrData(i + X, 1) < GpBr Then i = i + X Else Y = Y - X + 1 End If End If Loop For ii = i To Y If BrData(ii, 1) = GpBr Then GpBr2Dept = BrData(ii, 2) ii = Y End If Next ii End Function The function is looking up a translation value in a table stored in the addin. I'm using an array because I can get it to run faster than a VLookUp. If I use VLookup and copy down 1,000 rows it takes forever. Once the array loads and sorts for the 1st formula copying down 1,000 times runs very fast. I didn't copy in all of the other code called from the function. I can do that if it will help. But, there is a lot of it. Thanks for looking into this, John "Niek Otten" wrote: Hi John, What do the functions look like and how are they called? Is Calculation set to Automatic? -- Kind regards, Niek Otten "John Hutcins" wrote in message ... I have written an add-in with several public functions that work fine when used in a formula in a cell. But, when I save the workbook, close Excel, reopen Excel and open the workbook the formulas evaluate to #VALUE!. If I hit recalc it does nothing. If I reenter a formula or enter a formula in a new cell it works fine. How do I get the add-in to run my functions and show the result when it Excel opens or when a new workbook opens? Thanks, John Hutchins |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell function not working w/o recalc | Excel Worksheet Functions | |||
can you use a function to recalc a pivot table report | Excel Worksheet Functions | |||
Excel 2000 formula recalc problem | Excel Programming | |||
UDF recalc problem between sheets | Excel Programming | |||
Not recalc in custom function | Excel Programming |