Reference to Add-In for VBA code?

[Don Wiss]

We have a pricing add-in that several of our pricing models use. For most
workbooks the functions simply sit in cells. In one the VBA also calls the
functions. So for that one I set up a Reference. The problem is this
spreadsheet can get large, and when the user opens more than one of them
they often get a Catastrophic Failure. Despite its scary sound, when I've
been checking this out I've found that if I keep ending the errors it seems
to end up working okay. But the users don't like this. Searching on
Catastrophic Error finds things that we aren't doing, like using .NET. Now
I'm not sure that the Reference is the cause of this, but it is the only
thing different between this workbook and others. I haven't found much on
References. With two workbooks open there are then two References. Should I
remove the Reference and call the add-in functions in VBA using
Application.Run? Would this solve our problem?

I tried calling it this way. I've only used Application.Run for macros with
no arguments. Trying with a function got me an error.

The current way, using the Reference:
HL = GetHazardLevel(Range("PremDist").Cells(j, 1).Value, HL)

Trying Run:
HL = Application.Run("'CalcILF Add-In.xla'!GetHazardLevel(Range(""PremDist"").Cells(j, 1).Value, HL)")

I gather I'm getting my quotes all messed up?

Is it more efficient to use a Reference?

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Bob Phillips]

I can't say what you should do as I have never encountered that error, but
the syntax for Application.Run is

HL = Application.Run("'CalcILF
Add-In.xla'!GetHazardLevel",Range("PremDist").Cells(j, 1).Value, HL)

that is

res = Application.Run("'some book.xls'!macro", 4, "input")

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

We have a pricing add-in that several of our pricing models use. For most
workbooks the functions simply sit in cells. In one the VBA also calls the
functions. So for that one I set up a Reference. The problem is this
spreadsheet can get large, and when the user opens more than one of them
they often get a Catastrophic Failure. Despite its scary sound, when I've
been checking this out I've found that if I keep ending the errors it seems
to end up working okay. But the users don't like this. Searching on
Catastrophic Error finds things that we aren't doing, like using .NET. Now
I'm not sure that the Reference is the cause of this, but it is the only
thing different between this workbook and others. I haven't found much on
References. With two workbooks open there are then two References. Should I
remove the Reference and call the add-in functions in VBA using
Application.Run? Would this solve our problem?

I tried calling it this way. I've only used Application.Run for macros with
no arguments. Trying with a function got me an error.

The current way, using the Reference:
HL = GetHazardLevel(Range("PremDist").Cells(j, 1).Value, HL)

Trying Run:
HL = Application.Run("'CalcILF

Add-In.xla'!GetHazardLevel(Range(""PremDist"").Cells(j, 1).Value, HL)")

 

[Don Wiss]

I can't say what you should do as I have never encountered that error, but
the syntax for Application.Run is

HL = Application.Run("'CalcILF
Add-In.xla'!GetHazardLevel",Range("PremDist").Cells(j, 1).Value, HL)

that is

res = Application.Run("'some book.xls'!macro", 4, "input")

Hi Bob,

Thanks for the correct syntax. I converted all to using Run and removed the
Reference. Then for some strange reason the add-in functions that resided
in spreadsheet cells went to #NAME?. Restoring the Reference cleared that
up. Removing it and they went back to #NAME?. Now as we all know functions
in add-ins should work in cells. So I tried giving the name of the add-in
in front of the call to the add-in's function. Lo and behold, not only did
that work but the add-in name disappeared from in front.

I tried some examples of large workbooks to test the Catastrophic Failure.
I transferred over the input data to have the same cases. I was not able to
get the Catastrophic Failure with the Reference removed. But using the
current version I was able to get it. So it would appear that I have fixed
the problem.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Bob Phillips]

Weird eh? Although it is not fully satisfactory to walk away not knowing the
cause, sometimes you just have to move eon .

Glad you are sorted.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)