Reading a Range from a VB6 DLL

[meldrum_scotland]

Hi,

I have a visual basic 6 DLL sub that is called from a vba sub in
Excel. I can't get the DLL to find the used range in the activesheet
and loop through each cell, categorize it and put it into an array.

Any suggestions much appreciated.

Thank you in advance.

Meldrum

 

[RB Smissaert]

Simplest might be to assign the range to a variant array, pass the array to
the dll
as an argument and return the new array.
Another option is to pass the Excel application as an object to the dll and
in the
dll drill down to the used range etc.
What code have you got now?

RBS

 

[Chip Pearson]

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)

 

[meldrum_scotland]

Simplest might be to assign the range to a variant array, pass the array to
the dll
as an argument and return the new array.
Another option is to pass the Excel application as an object to the dll and
in the
dll drill down to the used range etc.
What code have you got now?

RBS










- Show quoted text -

RBS / Chip

Thank you for you quick replies. I have managed to read the range by
passing the no of rows and columns as a long/iinteger respectively.

Cheers

Meldrum