Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, UDFs and VSTO
Excel 2003, SP1, Windows XP Pro, SP1 Visual Studio 2003
Hello, I have created a set of functions in C# that are called by UDFs in Excel (VBA code below). Most of them work fine but one of them calcExpense fails at seemingly random intervals: Public objManaged As Object Public Sub RegisterCallback(o As Object) Set objManaged = o End Sub Public Function calcExpense(row As Integer, col As Integer, begcol As Integer, begqtr As String, endqtr As String, begsal As Double) As Double Application.Volatile If (IsEmpty(row) Or IsEmpty(col) Or IsEmpty(begcol)) Then Exit Function End If calcExpense = objManaged.calcExpense(row, col, begcol, begqtr, endqtr, begsal) End Function It is called from a cell like so: calcExpense(ROW(),COLUMN(),COLUMN($F14),$E14,$F14, $D14) I used a 3rd party tool Cell Audit by cubiclesoft to trace in and it appears that the intrinsic fucntions ROW and Column are failing before my code is even called. Causing the VBA function to exit and the cell to be set to #VALUE. Whenever the code does actually pass into my C# code the function executes correctly. Any help would be appreciated. Terence |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, UDFs and VSTO
Jim,
Thanks for the response. I will try your suggestions: Regarding the objmanaged object that is the codebehind (COM) object created by my VSTO project. calcExpense is a member function of that code behind object. Regards, Terence "Jim Cone" wrote: Terence, Some suggestions only, as your objManaged.calcExpense(row, col, begcol, begqtr, endqtr, begsal) makes no sense to me. (calcExpense is a property of objManaged???) 1. The first argument "row" should be a Long not an Integer - there are 65000 + rows in excel. 2. Do not use "row" as a variable, it is a key word in Excel. 3. The IsEmpty functions are useless, they will always return False. IsEmpty only returns meaningful information for Variants and range objects. Instead try something like... If row * col * begcol = 0 Then exit Function That ought to get you started. Regards, Jim Cone San Francisco, CA "Terence Craig" <Terence wrote in message ... Excel 2003, SP1, Windows XP Pro, SP1 Visual Studio 2003 Hello, I have created a set of functions in C# that are called by UDFs in Excel (VBA code below). Most of them work fine but one of them calcExpense fails at seemingly random intervals: Public objManaged As Object Public Sub RegisterCallback(o As Object) Set objManaged = o End Sub Public Function calcExpense(row As Integer, col As Integer, begcol As Integer, begqtr As String, endqtr As String, begsal As Double) As Double Application.Volatile If (IsEmpty(row) Or IsEmpty(col) Or IsEmpty(begcol)) Then Exit Function End If calcExpense = objManaged.calcExpense(row, col, begcol, begqtr, endqtr, begsal) End Function It is called from a cell like so: calcExpense(ROW(),COLUMN(),COLUMN($F14),$E14,$F14, $D14) I used a 3rd party tool Cell Audit by cubiclesoft to trace in and it appears that the intrinsic fucntions ROW and Column are failing before my code is even called. Causing the VBA function to exit and the cell to be set to #VALUE. Whenever the code does actually pass into my C# code the function executes correctly. Any help would be appreciated. Terence |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, UDFs and VSTO
I have made all of Jim's suggested changes and still have the problem.
Would appreciate any additional advice. Thanks Terence "Terence Craig" wrote: Jim, Thanks for the response. I will try your suggestions: Regarding the objmanaged object that is the codebehind (COM) object created by my VSTO project. calcExpense is a member function of that code behind object. Regards, Terence "Jim Cone" wrote: Terence, Some suggestions only, as your objManaged.calcExpense(row, col, begcol, begqtr, endqtr, begsal) makes no sense to me. (calcExpense is a property of objManaged???) 1. The first argument "row" should be a Long not an Integer - there are 65000 + rows in excel. 2. Do not use "row" as a variable, it is a key word in Excel. 3. The IsEmpty functions are useless, they will always return False. IsEmpty only returns meaningful information for Variants and range objects. Instead try something like... If row * col * begcol = 0 Then exit Function That ought to get you started. Regards, Jim Cone San Francisco, CA "Terence Craig" <Terence wrote in message ... Excel 2003, SP1, Windows XP Pro, SP1 Visual Studio 2003 Hello, I have created a set of functions in C# that are called by UDFs in Excel (VBA code below). Most of them work fine but one of them calcExpense fails at seemingly random intervals: Public objManaged As Object Public Sub RegisterCallback(o As Object) Set objManaged = o End Sub Public Function calcExpense(row As Integer, col As Integer, begcol As Integer, begqtr As String, endqtr As String, begsal As Double) As Double Application.Volatile If (IsEmpty(row) Or IsEmpty(col) Or IsEmpty(begcol)) Then Exit Function End If calcExpense = objManaged.calcExpense(row, col, begcol, begqtr, endqtr, begsal) End Function It is called from a cell like so: calcExpense(ROW(),COLUMN(),COLUMN($F14),$E14,$F14, $D14) I used a 3rd party tool Cell Audit by cubiclesoft to trace in and it appears that the intrinsic fucntions ROW and Column are failing before my code is even called. Causing the VBA function to exit and the cell to be set to #VALUE. Whenever the code does actually pass into my C# code the function executes correctly. Any help would be appreciated. Terence |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tool tip for the UDFs created using vsto se add-in. | Excel Worksheet Functions | |||
VSTO | Excel Discussion (Misc queries) | |||
'portable' UDFs??? | Excel Programming | |||
Acrobat 6.0 and UDFs | Excel Programming | |||
VSTO 2.0 | Excel Programming |