Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference to Add-In for VBA code?
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference to Add-In for VBA code?
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) "Don Wiss" wrote in message ... 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference to Add-In for VBA code?
On Tue, 13 Feb 2007, Bob Phillips wrote:
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference to Add-In for VBA code?
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) "Don Wiss" wrote in message ... On Tue, 13 Feb 2007, Bob Phillips wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to set a reference | Excel Programming | |||
How to make a button VBA code reference other VBA code subroutines??? | Excel Programming | |||
how to code variable reference? | Excel Programming | |||
remove reference via code | Excel Programming | |||
Adding reference in code | Excel Programming |