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

F

FrankJO

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
 
P

Peter T

If Application.IsNumber(RNGCELL) Then

What does Application refer to

(that's as far as I read)

Regards,
Peter T
 
J

Jim Cone

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
 
P

Peter T

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
 
P

Peter T

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
 
F

FrankJO

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

Chip Pearson

in VBA, exactly, but what about in VB.net
I don't know
Click to expand...

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)
 
J

Jim Cone

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.
 
P

Peter T

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
 
F

FrankJO

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
 
F

FrankJO

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

debug automation add in written in VS2005 for excel 2003 2
Probs w/Displaying Graph from Excel Interop Add-In Function 0
VB.Net DLL Using Excel Named Ranges 2
Automation error -2147417848 on windows 98 while using excel in VB ( works fine in XP) 1
Error 1004 ""Method 'Range' of object '_Global' Failed 3
Hyperlink jpg files in Dir via VBA 1
User defined function returns imprecise value when used in worksheet 3
Error 1004 Method 'Range' of object 'Global' failed 3

Top