VB 2005 User Defined Function (via Automation Add-in) Generates #V
 

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

VB 2005 User Defined Function (via Automation Add-in) Generates #V
 

If Application.IsNumber(RNGCELL) Then

What does Application refer to

(that's as far as I read)

Regards,
Peter T


"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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

Hi Peter,

In VBA, "Application" refers to the entire Microsoft Excel application. MSDN
definition: http://msdn.microsoft.com/en-us/library/aa213696.aspx . IsNumber,
then, is a function of the Excel application.

Thanks,

Frank

"Peter T" wrote:

If Application.IsNumber(RNGCELL) Then

What does Application refer to

(that's as far as I read)

Regards,
Peter T


"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

VB 2005 User Defined Function (via Automation Add-in) Generates #V
 

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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

In VBA, "Application" refers to the entire Microsoft Excel

in VBA, exactly, but what about in VB.net

I don't know .Net but in other app's you'd use (say) xlApp where xlApp is a
reference to the Excel.Application.

In passing there are probably more efficient ways to validate the cell
contains a number rather than Excel's IsNumber worksheet function.

Regards,
Peter T


"FrankJO" wrote in message
...
Hi Peter,

In VBA, "Application" refers to the entire Microsoft Excel application.
MSDN
definition: http://msdn.microsoft.com/en-us/library/aa213696.aspx .
IsNumber,
then, is a function of the Excel application.

Thanks,

Frank

"Peter T" wrote:

If Application.IsNumber(RNGCELL) Then

What does Application refer to

(that's as far as I read)

Regards,
Peter T


"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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

Apologies, I misunderstood.

I see now that "Application" was in your original VBA code, not in VB.Net.

Ignore my previous comments!

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
In VBA, "Application" refers to the entire Microsoft Excel

in VBA, exactly, but what about in VB.net

I don't know .Net but in other app's you'd use (say) xlApp where xlApp is
a reference to the Excel.Application.

In passing there are probably more efficient ways to validate the cell
contains a number rather than Excel's IsNumber worksheet function.

Regards,
Peter T


"FrankJO" wrote in message
...
Hi Peter,

In VBA, "Application" refers to the entire Microsoft Excel application.
MSDN
definition: http://msdn.microsoft.com/en-us/library/aa213696.aspx .
IsNumber,
then, is a function of the Excel application.

Thanks,

Frank

"Peter T" wrote:

If Application.IsNumber(RNGCELL) Then

What does Application refer to

(that's as far as I read)

Regards,
Peter T


"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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

Thanks, Jim. That makes sense. How would that look like in code? I keep
running into problems when trying to declare or instantiate Excel.

"Jim Cone" wrote:

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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

in VBA, exactly, but what about in VB.net

I don't know

In pure NET, such as a Windows Forms exe application, there is no such
thing as an Application object (which in VBA means the application
hosting the VBA implementation) or the App object (which in VB6 means
the exe program). In NET, you can go through the
Microsoft.VisualBasic.Information namespace to get much of the info
provided by Application or App, or you can use the System.Reflection
namespace to get tons of information about any part of an application.
You can also use the My namespace to create your own application
extensions.

In a Shared Add-In written in NET, Application is an Object parameter
passed in at startup that references the host application.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 6 Nov 2008 18:33:41 -0000, "Peter T" <peter_t@discussions
wrote:

In VBA, "Application" refers to the entire Microsoft Excel

in VBA, exactly, but what about in VB.net

I don't know .Net but in other app's you'd use (say) xlApp where xlApp is a
reference to the Excel.Application.

In passing there are probably more efficient ways to validate the cell
contains a number rather than Excel's IsNumber worksheet function.

Regards,
Peter T


"FrankJO" wrote in message
...
Hi Peter,

In VBA, "Application" refers to the entire Microsoft Excel application.
MSDN
definition: http://msdn.microsoft.com/en-us/library/aa213696.aspx .
IsNumber,
then, is a function of the Excel application.

Thanks,

Frank

"Peter T" wrote:

If Application.IsNumber(RNGCELL) Then

What does Application refer to

(that's as far as I read)

Regards,
Peter T


"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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

I don't have VB.Net, have never used it and don't intend to.
With that understanding here is my guess...
'--
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

XL = Returns.Parent.Parent.Parent 'sheet|workbook|application

Subsetreturns = XL.Intersect(Returns.Parent.UsedRange, Returns)
VAMI = CType(1000, Double) '??? already declared in the function line.

'In an Excel.VBA For Each loop the RNGCELL object would be
'automatically "Set" or determined. I don't know how VB.Net
'handles that issue.

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
--
Jim Cone
Portland, Oregon USA


"FrankJO"
wrote in message
Thanks, Jim. That makes sense. How would that look like in code? I keep
running into problems when trying to declare or instantiate Excel.

"Jim Cone" wrote:
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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

Please see Chip Pearson's post.
--
Jim Cone
Portland, Oregon USA

VB 2005 User Defined Function (via Automation Add-in) Generate
 

Please see Chip Pearson's post.

In particular this bit
"
In a Shared Add-In written in NET, Application is an Object parameter
passed in at startup that references the host application.
"

Having not paid attention first time I've since had a better look at your
VBA and NET

In a UDF you cannot change the interface as you are attempting to do in a
big way just to get the Application object. Not sure but to get you started
you might be able to do this instead -

XL = Returns.Parent.Application

where "Returns" is the excel.range object passed to the UDF

Even if that works better to go with Chip's pointer and maintain a global
ref to the application that was passed at startup (looks very simlar to a
how it would be done in a VB6 Com addin)

Regards,
Peter T

VB 2005 User Defined Function (via Automation Add-in) Generate
 

That did the trick, thanks Jim. I was not referencing the Excel application
properly. Here is the revised and corrected code:

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

XL = Returns.Parent.Parent.Parent 'This sets XL equal to the Excel
application.

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

VAMI = 1000

For Each RNGCELL In Subsetreturns
If XL.WorksheetFunction.IsNumber(RNGCELL) = True Then
VAMI = VAMI * (1 + RNGCELL.Value)
Else : VAMI = VAMI

End If
Next RNGCELL

VAMI = VAMI

End Function



"Jim Cone" wrote:

I don't have VB.Net, have never used it and don't intend to.
With that understanding here is my guess...
'--
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

XL = Returns.Parent.Parent.Parent 'sheet|workbook|application

Subsetreturns = XL.Intersect(Returns.Parent.UsedRange, Returns)
VAMI = CType(1000, Double) '??? already declared in the function line.

'In an Excel.VBA For Each loop the RNGCELL object would be
'automatically "Set" or determined. I don't know how VB.Net
'handles that issue.

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
--
Jim Cone
Portland, Oregon USA


"FrankJO"
wrote in message
Thanks, Jim. That makes sense. How would that look like in code? I keep
running into problems when trying to declare or instantiate Excel.

"Jim Cone" wrote:
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

VB 2005 User Defined Function (via Automation Add-in) Generate
 

Thanks, Peter. The "XL = Returns.Parent" bit, which was also suggested by Jim
above, did the trick.

"Peter T" wrote:

Please see Chip Pearson's post.

In particular this bit
"
In a Shared Add-In written in NET, Application is an Object parameter
passed in at startup that references the host application.
"

Having not paid attention first time I've since had a better look at your
VBA and NET

In a UDF you cannot change the interface as you are attempting to do in a
big way just to get the Application object. Not sure but to get you started
you might be able to do this instead -

XL = Returns.Parent.Application

where "Returns" is the excel.range object passed to the UDF

Even if that works better to go with Chip's pointer and maintain a global
ref to the application that was passed at startup (looks very simlar to a
how it would be done in a VB6 Com addin)

Regards,
Peter T