Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB 2005 User Defined Function (via Automation Add-in) Generate
Please see Chip Pearson's post.
-- Jim Cone Portland, Oregon USA |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
Copy generates an automation error. | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |