Posts 1 - 10 of 33
First | Prev. | 1 2 3 4 | Next | Last
|
|
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
|
|
Posts 1 - 10 of 33
First | Prev. | 1 2 3 4 | Next | Last
|