transferring code from VBA to a VB dll

[PM]

Greetings and reverence to all gurus ! (with a special notice for Stephen
Bullen ;-))


Nothing more simple than copy a range ? This code works normally in VBA:

Sheets(1).Select
ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select
Selection.Copy
Sheets(2).Select
Cells(2, 1).Select
Sheets(2).Paste

In fact, this code is generated by the macro recorder.

The following is the translation in my VB dll :

Set mySheet = XLS.Sheets(1) 'XLS represents my Workbook
mySheet.Activate 'this line is correctly
implemented
With mySheet
.Range(Cells(l1, 1), Cells(l2, 2)).copy

This last line fails. It is Range that fails :
set r = .Range(Cells(l1, 1), Cells(l2, 2)) also fails (method '~'
failed)

Anyone know why ? And how to get around this ?

 

[Bernie Deitrick]

PM,

I expect that you need to be more specific with your cells object, since
your VBA depends on defaults. Cells defaults to the cells collection of the
activesheet, but more specific would be:
VBA:
Old: ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select

New: ActiveSheet.Range(ActiveSheet.Cells(l1, 1), ActiveSheet.Cells(l2,
2)).Select

So try:

With mySheet
.Range(.Cells(l1, 1), .Cells(l2, 2)).copy

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Try

With mySheet
.Range(.Cells(l1, 1), .Cells(l2, 2)).copy


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[PM]

YES !!

Your perfectly right. I am still infused with VBA style writing.
Thanks a million ! I just couldn't see it before you said it !

Pat

 

[keepITcool]

.Range(Cells(l1, 1), Cells(l2, 2)).copy

note that Cells refers to the activesheet! as it is not qualified,
try like :

.Range(.Cells(l1, 1), .Cells(l2, 2)).copy


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >