VBA, UDFs and VSTO

[Guest]

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

 

[Jim Cone]

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

 

[Guest]

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

 

[Guest]

I have made all of Jim's suggested changes and still have the problem.

Would appreciate any additional advice.

Thanks Terence