Question DLL - Get address of UDF calling cell

Humar

New member
Joined
Oct 18, 2009
Messages
1
Programming Experience
1-3
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:

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
 
Back
Top