Write array from vb6 dll to a specified worksheet in Excel

[meldrum_scotland]

Hi,

I have the following code:

Excel VBA Sub:

Sub CallVBDLL

VBDLL (rows,cols)

End Sub

In VB6 DLL I have the following:


Public Sub VBDLL (rows,cols)

Re Dim BugArray(rows,cols) as string
Go through rows and cols, classify and add to my BigArray(rows,cols)

*** I would like a method of writing the BigArray to a new worksheet
in the workbook without looping through array **

End Sub

Any help much appreciated.

Thank you in advance

Meldrum

 

[RB Smissaert]

You will have to work out your Workbook and Worksheet references etc.,
but basically to write an array to a range without looping you would do:

Dim lRows As Long
Dim lCols As Long

lRows = (UBound(BigArray) - LBound(BigArray)) + 1
lCols = (UBound(BigArray, 2) - LBound(BigArray, 2)) + 1

Range(Cells(1), Cells(lRows, lCols)) = BigArray


RBS

 

[meldrum_scotland]

You will have to work out your Workbook and Worksheet references etc.,
but basically to write an array to a range without looping you would do:

Dim lRows As Long
Dim lCols As Long

lRows = (UBound(BigArray) - LBound(BigArray)) + 1
lCols = (UBound(BigArray, 2) - LBound(BigArray, 2)) + 1

Range(Cells(1), Cells(lRows, lCols)) = BigArray

RBS



















- Show quoted text -

Hi RBS

Thank you for your reply - I tried your code but got no joy, I think I
might not be referencing it correct. In my DLL I add a new sheet to
the active workbook called ReportSheet. I then loop through a sheet
called TestSheet and classify each cell.

I am able to go through each item in the array and write it
individually to the ReportSheet and that works fine.

The problem is when I want to write the entire array in one go to a
range it doesn't work. I've set Option Base 1 as well.

In my code I've got:

Option Base 1

Set ReportSheet = mxlApp.ActiveWorkbook.sheets.add ---> this should
give me correct referencing as I can write array by looping.

ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).value = BigArray

Does this look okay?

Any help much appreciated.

Meldrum

 

[RB Smissaert]

ReportSheet.Range(Cells(1,1),Cells(xRow,yCol)).value = BigArray

Try this instead:

With ReportSheet
.Range(.Cells(1,1),.Cells(xRow,yCol)).value = BigArray
End With

Note the dots before Range and Cells.


RBS

 

[meldrum_scotland]

Try this instead:

With ReportSheet
.Range(.Cells(1,1),.Cells(xRow,yCol)).value = BigArray
End With

Note the dots before Range and Cells.

RBS

















- Show quoted text -

RBS,

Genius..works like a dream.

Thank you.

Meldrum

 

[Chip Pearson]

Just for variety, you can do something like the following in your VB6 code,
where Arr is the array to be dumped to the worksheet with an upper left
corner of the cell referenced by R.


R.Resize(UBound(Arr, 1) - LBound(Arr) + 1, UBound(Arr, 2) - LBound(Arr, 2) +
1) = Arr


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)