Caller Range in XLL Add In

Add-in Express™ Support Service
That's what is more important than anything else

Caller Range in XLL Add In
How to determine the Caller Range in a UDF in the XLL Containe 
Michael Kaden


Guest


Michael Kaden writes:
What is the Application caller in the XLL UDF called?


Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller); call it from a function defined in the XLLContainer class. The ADXExcelRef returned by that method allows determining the index (indices) of the cell(s) on the worksheet the UDF is called from. You can also call the ADXExcelRef.ConvertToA1Style (or ConvertToR1C1Style) method to get a string representing the caller's address.

I tried the above but did not succeed (Reference required). Can you please post a VB code example with Import and DIM Statements how to get the Range object of the calling cell?

Thank you

regards
Michael
Posted 19 Nov, 2017 15:44:05 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Michael,

        Public Shared Function GetCallerAddress() As String
            Dim caller As ADXExcelRef = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller)
            Return String.Format("[{0},{1},{2},{3}]", caller.ColumnFirst, caller.RowFirst, caller.ColumnLast, caller.RowLast)
        End Function


Please see http://temp.add-in-express.com/support/MyXLLAddin23-GetCallerAddressVb.zip.


Andrei Smolin
Add-in Express Team Leader
Posted 20 Nov, 2017 05:00:40 Top
Michael Kaden


Guest


Hello Andrei


Hello Michael,

Public Shared Function GetCallerAddress() As String
Dim caller As ADXExcelRef = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller)
Return String.Format("[{0},{1},{2},{3}]", caller.ColumnFirst, caller.RowFirst, caller.ColumnLast, caller.RowLast)


End Function

Please see http://temp.add-in-express.com/support/MyXLLAddin23-GetCallerAddressVb.zip.



I tried the function and also looked at your sample project. This does not help me. I need a range object representing the caller where I can extract strings.

Dim BookNow, SheetNow, AddressNow

AddressNow = caller.Address.ToString ' = cell address
SheetNow = caller.parent.Name.ToString ' = name of sheet where the caller resides
BookNow = caller.Parent.Parent.FullName.ToString '= name of Workbook where the caller resides

and use the caller Range object to refer to one clearly defined caller (Book/Sheet/Address)

with passing on these, I can refer to the correct caller Book/Sheet/Address from anywhere in my project. I believe with the caller as above, I will have problems with various Excel instances running at the same time.

In ComAddIn it works fine with

Dim xlExcel As Excel.Application
Dim xlRange As Excel.Range
Dim BookNow as String
Dim Newval as Double
xlExcel = ExcelApp
xlRange = xlExcel.Caller

Then I can, for example, extract the calling workbook (with path) as

BookNow = xlRange.Parent.Parent.FullName.ToString

And also read caller relative cells using the xlRange Caller object

Newval = xlRange.Offset(1,1).Value2


I am looking for the exact equivalent in the XLL module

Thank you & regards

Michael
Posted 20 Nov, 2017 09:15:31 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
I believe with the caller as above, I will have problems with various Excel instances running at the same time.


How do you test this scenario? What problems do you expect to have?

Also note that getting an Excel.Range object means you cannot use XLL. In XLL, you can get address information using ADXExcelRef.ConvertToA1Style() or this code:

Public Shared Function GetCallerAddress(A1Style As Boolean) As String
    Dim caller As ADXExcelRef = _
        TryCast( _
            _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller), _
            ADXExcelRef)
    Dim xlSheetNm As ADXExcelWorksheetFunction = _
        DirectCast(16389, ADXExcelWorksheetFunction)
    Dim bookAndSheet As String = _
        _Module.CallWorksheetFunction(xlSheetNm, caller).ToString()
    Dim rowFirst As Integer = caller.RowFirst + 1
    Dim rowLast As Integer = caller.RowLast + 1
    Dim columnFirst As Integer = caller.ColumnFirst + 1
    Dim columnLast As Integer = caller.ColumnLast + 1
    Dim isOneCell As Boolean = (rowFirst = rowLast AndAlso columnFirst = columnLast)
    Dim cellAddress As String = ""
    If A1Style Then
        cellAddress = "!" & GetExcelColumnName(columnFirst) & rowFirst.ToString()
        If Not isOneCell Then
            cellAddress += ":" & GetExcelColumnName(columnLast) & rowLast.ToString()
        End If
    Else
        cellAddress = "!R" & rowFirst.ToString() & "C" & columnFirst.ToString()
        If Not isOneCell Then
            cellAddress += ":R" & rowLast.ToString() & "C" & columnLast.ToString()
        End If
    End If
    Return bookAndSheet & cellAddress
End Function


The resulting value will follow the pattern "[Book1]Sheet1!A1". You can parse it and get the address information required.

Michael Kaden writes:
I am looking for the exact equivalent in the XLL module


To retrieve a value from a cell, construct an ADXExcelRef pointing to that cell (verify that the cell is correct using ADXExcelRef.ConvertToA1Style) and call ADXExcelRef.GetValue().


Andrei Smolin
Add-in Express Team Leader
Posted 20 Nov, 2017 09:48:47 Top
Michael Kaden


Guest


On you above code -I copied it into the XLL container ? I get

'GetExcelColumnName' is not declared. It may be inaccessible due to its protection level.

'GetExcelColumnName' is not declared. It may be inaccessible due to its protection level.

Instead of

Dim bookAndSheet As String =
_Module.CallWorksheetFunction(xlSheetNm, caller).ToString()

can I get
Bookname - can we get the FullName please, i.e. with path
and
Sheetname in separate calls

?

thank you & regards

Michael
Posted 20 Nov, 2017 11:05:48 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Ah, my fault. The code below is part of the project at https://www.add-in-express.com/creating-addins-blog/2011/10/07/thread-safe-xll-caller-address/:

         Public Shared Function GetCallerAddress(A1Style As Boolean) As String
            Dim caller As ADXExcelRef = TryCast(_Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller), ADXExcelRef)

            Dim xlSheetNm As ADXExcelWorksheetFunction = DirectCast(16389, ADXExcelWorksheetFunction)
            Dim bookAndSheet As String = _Module.CallWorksheetFunction(xlSheetNm, caller).ToString()

            Dim rowFirst As Integer = caller.RowFirst + 1
            Dim rowLast As Integer = caller.RowLast + 1
            Dim columnFirst As Integer = caller.ColumnFirst + 1
            Dim columnLast As Integer = caller.ColumnLast + 1
            Dim isOneCell As Boolean = (rowFirst = rowLast AndAlso columnFirst = columnLast)
            Dim cellAddress As String = ""
            If A1Style Then
                cellAddress = "!" & GetExcelColumnName(columnFirst) & rowFirst.ToString()
                If Not isOneCell Then
                    cellAddress += ":" & GetExcelColumnName(columnLast) & rowLast.ToString()
                End If
            Else
                cellAddress = "!R" & rowFirst.ToString() & "C" & columnFirst.ToString()
                If Not isOneCell Then
                    cellAddress += ":R" & rowLast.ToString() & "C" & columnLast.ToString()
                End If
            End If
            Return bookAndSheet & cellAddress
        End Function

        Private Shared majorVersion As Integer = 0
        Private Shared Function GetExcelColumnName(columnNumber As Integer) As String
            If majorVersion = 0 Then
                majorVersion = GetExcelMajorVersion()
            End If
            If majorVersion < 12 AndAlso columnNumber > 256 Then
                Throw New Exception("A call to GetExcelColumnName() failed. Wrong column number.")
            ElseIf majorVersion <= 14 AndAlso columnNumber > 16384 Then
                Throw New Exception("A call to GetExcelColumnName() failed. Wrong column number.")
            End If

            ' http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa

            Dim dividend As Integer = columnNumber
            Dim columnName As String = [String].Empty
            Dim modulo As Integer

            While dividend > 0
                modulo = (dividend - 1) Mod 26
                columnName = Convert.ToChar(65 + modulo).ToString() & columnName
                dividend = CInt((dividend - modulo)  26)
            End While
            Return columnName
        End Function

        Private Shared Function GetExcelMajorVersion() As System.Int32
            Dim versionString As String = _Module.ExcelApp.Version
            Dim version As System.Int32 = 0
            If versionString.StartsWith("9.") Then
                version = 9
            Else
                version = Convert.ToInt32(versionString.Substring(0, 2))
            End If
            Return version
        End Function


Michael Kaden writes:
can I get Bookname - can we get the FullName please, i.e. with path and Sheetname in separate calls


In separate calls? You can create a wrapper class that calls GetCallerAddress(), parses the resulting value, and saves the data to separate variables; you access these variables using properties.


Andrei Smolin
Add-in Express Team Leader
Posted 21 Nov, 2017 03:53:01 Top
Michael Kaden


Guest


Dear Andrei

With the above code I get 2 errors:

dividend = CInt((dividend - modulo) 26)
')' expected


Dim versionString As String = _Module.ExcelApp.Version
'ExcelApp' is not a member of 'XLLModule'.

BookandSheet does give the bookname without the path, I need the path as well

Regards
Michael
Posted 21 Nov, 2017 05:49:29 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Michael Kaden writes:
dividend = CInt((dividend - modulo) 26)


The forum has cut out the division sign; please check the code of the project. As to the ExcelApp property, we will fix this omission in a future Add-in Express build. As to now, you can add this property to your code:

Public ReadOnly Property ExcelApp() As Excel._Application 
    Get 
        Return CType(HostApplication, Excel._Application) 
    End Get 
End Property 



Andrei Smolin
Add-in Express Team Leader
Posted 21 Nov, 2017 06:01:42 Top
Michael Kaden


Guest


1.) Adding the above

gives the following error

Return CType(red, Excel._Application)

Error = Reference to a non-shared member requires an object reference.

Error 'ExcelApp' is not a member of 'XLLModule'. Error is still there

2.)

BookandSheet does give the bookname without the path, I need the path as well

regards
Michael
Posted 21 Nov, 2017 06:36:27 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Michael,

#1. This issue doesn't occur in the sample project described at https://www.add-in-express.com/creating-addins-blog/2011/10/07/thread-safe-xll-caller-address/.

#2.
         Public Shared Function GetWorkbookPath() As String
            Dim retObject As Object = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.GetDocument, 2)
            If (TypeOf retObject Is ADXExcelError) Then
                Return "the workbook isn't saved"
            Else
                Return retObject.ToString()
            End If
        End Function



Andrei Smolin
Add-in Express Team Leader
Posted 21 Nov, 2017 07:02:05 Top