Add-In Public Function Recalc Problem

[John Hutcins]

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

 

[Niek Otten]

Hi John,

What do the functions look like and how are they called? Is Calculation set
to Automatic?

 

[John Hutcins]

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

 

[John Hutcins]

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