Hi,
I have several Excel UDFs in a DLL created with VB (VB2008 Express) and .NET 3.5 SP1
My existing UDFs work fine in Excel (2003).
I am now trying to add a new UDF that requires the name of the workbook and worksheet of the cell calling the UDF.
I cannot get the ActiveCell or the Application.Caller functions to work. I have searched for some time and tried various methods, but Intellisense always shows that they are wrong and when built, re-registered, and accessed from my test function =XTest(A1) they return an error.
I have :
Option Infer On
Option Strict On
Option Explicit On
and the following Imports:
Imports System
Imports System.Math
Imports System.Runtime.InteropServices
Imports XL = Microsoft.Office.Interop.Excel
The following references are used (possibly more than I need, but I have tried various routes to get this to work):
Microsoft Excel 11.0 Object Library
Microsoft Office 11.0 Object Library
Microsoft Windows Common Controls 6.0 (SP6)
OLE Automation
System.dll
System.Core.dll
System.Data.dll
System.Data.DataSetExtensions.dll
System.Runtime.Remoting.dll
System.Xml.dll
System.Xml.Linq.dll
Microsoft Visual Basic for Applications Extensibility 5.3
I have an Interface section:
and this is the I/f for a test UDF I am using to try and access the calling cell's address, workbook and worksheet's names:
and the public class with the function code:
Any advice on how to access the active cell or the calling cell, or pointers to suitable articles or posts would be much appreciated.
Regards
I have several Excel UDFs in a DLL created with VB (VB2008 Express) and .NET 3.5 SP1
My existing UDFs work fine in Excel (2003).
I am now trying to add a new UDF that requires the name of the workbook and worksheet of the cell calling the UDF.
I cannot get the ActiveCell or the Application.Caller functions to work. I have searched for some time and tried various methods, but Intellisense always shows that they are wrong and when built, re-registered, and accessed from my test function =XTest(A1) they return an error.
I have :
Option Infer On
Option Strict On
Option Explicit On
and the following Imports:
Imports System
Imports System.Math
Imports System.Runtime.InteropServices
Imports XL = Microsoft.Office.Interop.Excel
The following references are used (possibly more than I need, but I have tried various routes to get this to work):
Microsoft Excel 11.0 Object Library
Microsoft Office 11.0 Object Library
Microsoft Windows Common Controls 6.0 (SP6)
OLE Automation
System.dll
System.Core.dll
System.Data.dll
System.Data.DataSetExtensions.dll
System.Runtime.Remoting.dll
System.Xml.dll
System.Xml.Linq.dll
Microsoft Visual Basic for Applications Extensibility 5.3
I have an Interface section:
VB.NET:
<InterfaceType(ComInterfaceType.InterfaceIsDual)> _
Public Interface ComIF
and this is the I/f for a test UDF I am using to try and access the calling cell's address, workbook and worksheet's names:
VB.NET:
'test function
Function XTest(ByRef XTRange As XL.Range) As Object
and the public class with the function code:
VB.NET:
' Class that implements the Interface
<Guid("9FEB08E8-B561-4e9e-9990-C73D02B206C5"), _
ClassInterface(ClassInterfaceType.None), ProgId("HumarFunctions.Functions1")> _
Public Class Functions1 : Implements ComIF
Public Function XTest(ByRef XTRange As XL.Range) As Object Implements ComIF.XTest
On Error GoTo MyErr
<various bits of code to try and return the calling cell address
none of which work !>
Exit Function
MyErr:
XTest = CVErr(CVErrEnum.ErrNA)
End Function
End Class
Any advice on how to access the active cell or the calling cell, or pointers to suitable articles or posts would be much appreciated.
Regards