It appears to me that you are creating a brand new Excel workbook
and running your function code on it... "wb = New Excel.Workbook "

You should be running your function code against the current open workbook/worksheet.
--
Jim Cone
Portland, Oregon USA




"FrankJO"
wrote in message
Hello,
I am converting a custom function from VBA to VB.net in Visual Studio 2005.
I have included the source code from both the original VBA function, as well
as my VB.net translation. I have reviewed various articles for correcting
syntax related issues, and applied changes, but to no avail. The function
does not generate an error when I build the overall project, nor is any error
thrown during deployment/installation. When I try to use the custom function
in a worksheet, however, it always generates a #VALUE error. The VBA function
never does this, and I have exhausted my resources towards solving this on my
own. Please review the code below, and I would most appreciate any solutions!

Original VBA:

Public Function VAMI(Returns As Range) As Double
Dim RNGCELL As Range
Set Subsetreturns = Intersect(Returns.Parent.UsedRange, Returns)

VAMI = 1000
For Each RNGCELL In Subsetreturns
If Application.IsNumber(RNGCELL) Then
VAMI = VAMI * (1 + RNGCELL.value)
Else: VAMI = VAMI
End If
Next RNGCELL

VAMI = VAMI
End Function

(attempted) VB.net translation:

Public Function VAMI(ByVal Returns As Excel.Range) As Double
Dim RNGCELL As Excel.Range
Dim Subsetreturns As Excel.Range
Dim XL As Excel.Application
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook

wb = New Excel.Workbook
ws = New Excel.Worksheet
RNGCELL = CType(ws.UsedRange, Excel.Range)
XL = ws.Application

Subsetreturns = XL.Intersect(Returns.Parent.UsedRange, Returns)

VAMI = CType(1000, Double)
For Each RNGCELL In Subsetreturns
If XL.WorksheetFunction.IsNumber(CType(RNGCELL.Value2 , Double)) = True Then
VAMI = VAMI * (CType(1, Double) + CType(RNGCELL.Value2, Double))
Else : VAMI = VAMI
End If
Next RNGCELL
VAMI = VAMI
End Function

Thanks,
Frank