Presumably, your DLL has some reference to the Excel application. If the DLL
is a COM Add-In, the Application parameter of the OnConnection event is a
reference to the host application, in this case, Excel.Application. If it
is just an Automation Add-In, with no intrinsic reference to the
Excel.Application (but was compiled with the Excel typelib), you can get a
reference to the Excel.Application and therefore anything else in Excel,
with code such as
Function Test(RR As Excel.Range) As String
Dim XLApp As Excel.Application
Dim ActWB As Excel.Workbook
Dim ActWS As Excel.Worksheet ' ignoring other Sheet types
Dim URng As Excel.Range
Set XLApp = RR.Application
Set ActWB = XLApp.ActiveWorkbook
Set ActWS = XLApp.ActiveSheet
Set URng = ActWS.UsedRange
Test = "WB: " & ActWB.Name & " WS: " & ActWS.Name & " URng: " &
URng.Address
End Function
This will return a string like
WB: Book2.xlsm WS: Sheet1 URng: $A$1:$D$3
indicating the ActiveWorkbook, ActiveSheet, and UsedRange.
If you need to move the used range values in and out of a range, try
somthing like
Function Test(RR As Excel.Range) As String
Dim XLApp As Excel.Application
Dim ActWB As Excel.Workbook
Dim ActWS As Excel.Worksheet ' ignoring other Sheet types
Dim URng As Excel.Range
Dim Arr() As Variant
Dim R As Long
Dim C As Long
Set XLApp = RR.Application
Set ActWB = XLApp.ActiveWorkbook
Set ActWS = XLApp.ActiveSheet
Set URng = ActWS.UsedRange
'Test = "WB: " & ActWB.Name & " WS: " & ActWS.Name & " URng: " &
URng.Address
ReDim Arr(1 To URng.Rows.Count, 1 To URng.Columns.Count)
For R = 1 To UBound(Arr, 1)
For C = 1 To UBound(Arr, 2)
Arr(R, C) = URng.Cells(R, C)
Next C
Next R
' DEBUG ONLY
For R = 1 To UBound(Arr, 1)
For C = 1 To UBound(Arr, 2)
Debug.Print R, C, Arr(R, C)
Next C
Next R
End Function
You might want to include some additional details about the exact nature of
the problem you are having.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)