FUNCTIONS NOT FOUND

[Kevin Bilbee]

Office 2000

We have a set of custome macros that read from a multivalue database, jBase.
I converted the macros into a .xla file and selected the addin from the
tools menu. All is great at tehis point. When creating a new spreadsheet all
works as expected.

Now the problem come in when the addin is loaded with a spreadsheet that
already has older versions of the macros. When we remove the old modules
excel can no longer find the functions and a #NAME? cell error is in the
cells with the function. The new functions in the .xla work just fine.

When using the function browser the functions are found and all the prompts
are available for the function parameters.

How do I get Excell to see the functions in the add in and no longer
reference the functions from the old modules?


Kevin Bilbee

 

[Dave Peterson]

One way to fix this is:

#1. Open the workbook with the problem (make sure the UDFs have been deleted)
#2. Open the addin
#3. Create a range name with the same name as the function (do for each
function)
#4. Delete that name
#5. select the cell with the formula
Hit F2 and enter to recalculate that cell

But that's a lot of work if you have lots of cells/functions.

I think that this should work

#1. Open the workbook with the problem (make sure the UDFs have been deleted)
#2. Open the addin
#3. Start a new workbook
#4. Add this routine to that new workbook's project
#5. Activate the troubled workbook
#6. Run the macro and see if it fixes the problem.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myUDFNames As Variant
Dim iCtr As Long

myUDFNames = Array("TestMeNow", "test2")

With ActiveWorkbook
Set wks = .Worksheets.Add
With wks
For iCtr = LBound(myUDFNames) To UBound(myUDFNames)
.Range("a1").Name = myUDFNames(iCtr)
.Parent.Names(myUDFNames(iCtr)).Delete
Next iCtr
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

For Each wks In .Worksheets
wks.Cells.Replace what:="=", Replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next wks
End With

End Sub

You'll have to change this line to include all the UDF names that need fixing:

myUDFNames = Array("TestMeNow", "test2")

=======
Forcing a recalc (even .calculatefull) didn't help. But changing the equal sign
to equal sign caused excel to reevaluate each of the formulas.

 

[Kevin Bilbee]

Thanks, it works

I tried the manual fix by creating a named range then removing the named
range anf the function ran. I am going to add a macro to fix the sheets that
are having the issue into the xla file.

I have one question on the Macro you supplied. Why does this block need to
run?

For Each wks In .Worksheets
wks.Cells.Replace what:="=", Replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next wks

Kevin Bilbee

 

[Dave Peterson]

If I didn't include that portion, then the function evaluated as a Name error.
Recalculation wasn't enough to fix the formula.

Manually, I could hit F2|Enter to force it. So I added the replace to do it in
the code.

 

[Kevin Bilbee]

One more question

What is UDF

Kevin

One way to fix this is:

#1. Open the workbook with the problem (make sure the UDFs have been
deleted)
#2. Open the addin
#3. Create a range name with the same name as the function (do for each
function)
#4. Delete that name
#5. select the cell with the formula
Hit F2 and enter to recalculate that cell

But that's a lot of work if you have lots of cells/functions.

I think that this should work

#1. Open the workbook with the problem (make sure the UDFs have been
deleted)
#2. Open the addin
#3. Start a new workbook
#4. Add this routine to that new workbook's project
#5. Activate the troubled workbook
#6. Run the macro and see if it fixes the problem.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myUDFNames As Variant
Dim iCtr As Long

myUDFNames = Array("TestMeNow", "test2")

With ActiveWorkbook
Set wks = .Worksheets.Add
With wks
For iCtr = LBound(myUDFNames) To UBound(myUDFNames)
.Range("a1").Name = myUDFNames(iCtr)
.Parent.Names(myUDFNames(iCtr)).Delete
Next iCtr
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

For Each wks In .Worksheets
wks.Cells.Replace what:="=", Replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next wks
End With

End Sub

You'll have to change this line to include all the UDF names that need
fixing:

myUDFNames = Array("TestMeNow", "test2")

=======
Forcing a recalc (even .calculatefull) didn't help. But changing the
equal sign
to equal sign caused excel to reevaluate each of the formulas.

 

[aaron.kempf]

excel is crap; i would reccomend learning a real reporting tool.

you can't leverage a dozen spreadsheets into anything useful; other
than a waste of harddrive space.

aren't you tired of emailing around 20mb spreadsheets?

 

[Dave Peterson]

UDF = User Defined Function

These are the functions that you created in your addin (or your workbook).

 

[Kevin Bilbee]

Dave thanks. I have it working and our users are happy. Using the xla
instead of having to import the Macro files is a great benefit and time
saver to all at my company that use these functions.

Kevin Bilbee