New problem in versions 6 with VBAsupport using sourceRange.Copy and targetRange.PasteSpecial

Rabbit1 · August 17, 2018, 5:10am

Hello, I have a macro that i have been using for a couple of years. It is written as an Excel macro, but worked well with LO 5.4. When I went to LO 6.0, it quit working correctly. This is the code I used:

    Rem Attribute VBA_ModuleType=VBAModule  

    Option VBASupport 1

    Sub Monday()

    ' Print Monday's Assignments
    '
    ' Copy and paste
Set rngCopy = Sheets(ActiveSheet.Name).Range("CA4:CE38")
Set rngPaste = Sheets(ActiveSheet.Name).Range("AW4")
rngCopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats
Set rngPaste = Sheets(ActiveSheet.Name).Range("BC4")
rngPaste.PasteSpecial xlPasteValues

rngPaste.PasteSpecial xlPasteFormats
Set rngPaste = Sheets(ActiveSheet.Name).Range("BI4")
rngCopy.Copy
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats
    ' Make as many copies as specified in F1
Set MyRange = Range("AW4:BT42")
iNumCopies = Range("E1").Value
	If iNumCopies < 1 Then iNumCopies = 1
MyRange.PrintOut Copies:=iNumCopies, Collate:=True
    ' Go to A4
Range("A4").Select 
ThisWorkbook.Save
    End Sub

For some reason it doesn’t copy and paste the correct range. If I run it multiple times, it sometimes pastes in different things each time. It works if I switch back to LO 5.4. Do I need to rewrite it to work with LO 6.0?
Thanks!

Lupp · August 17, 2018, 9:11am

To give someone a chance to verify the issue, you need to post the complete Sub, and also the options set for the respective module. In addition I would like to know if it was a module of one of the document’s libraries or of a local library.
From what you posted I would not expect that it ever worked in a local module. I also had doubted it anyway as it uses a lot of vbaexcel-speak. However it should not be difficulöt to translate it to correct API usage for LibO.

Rabbit1 · August 18, 2018, 1:55pm

I edited my original post to include the complete Sub. This is the options setting:
Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1
It was a module of one of the document’s libraries.

drewjensen · August 18, 2018, 2:43pm

Well, I tried this routine with both LibreOffice 6.0.5 and 6.1.0.3 running on Ubuntu 18.04 and although the data I have doesn’t meet what it’s looking for the routine runs to completion without any error and seems to select the ranges of cells expected.

Are you running Windows? I am not setup to check it under that OS at the moment.

Lupp · August 22, 2018, 10:17am

I edited the subject and (slightly) also the question trying to get the topic even more clear.

Lupp · August 18, 2018, 8:17pm

I ran the Sub in LibO V 5.4.4.2 and in AOO V 4.1.5 as well without any problems and producing the expected (by me) results.
The macro also ran under LibO versions 6.0.4 and 6.1.0.1RC without an error message, but the results looked arbitrary and were surely bad.
You may report a regression bug to bugs.documentfoundation.org.

(Edit1:)

I tried to report the bug but had to give in interim. My problem is that I (of course) have no Excel to my disposition, and that it seems to be impossible to get reliable information about how Excel-VBA should work. All I can find is silly videos (I cannot afford the time to watch), also silly and primitive examples, and even more silly explanations. But I don’t want any “tutorial” by somebody I don’t know of if he knows himself, and the help system of MS itself is incredibly bad. No specification and no useful explanation at all. The central question insofar is: If the target of a range.PasteSpecial statement is a single cell’s range or, more generally, a range differently dimensioned as compared with the source range, how shall the range getting pasted something in be dimensioned? As described by the source range’s dimensions? As described by the target range’s dimensions? In a more complicated way depending on…?

Who can tell me reliably?

Rabbit1 · August 19, 2018, 9:27pm

It must be something not working in rngCopy.Copy; when I changed it to rngCut.Cut, it worked. Then I just pasted back into the original range. It is a little different way to do it, but now it is all doing what I want it to. It looks like this:

     Sub Monday()

    ' Print Monday's Assignments
    '
    ' Cut and paste
Set rngCut = Sheets(ActiveSheet.Name).Range("CA4:CE38")
Set rngPaste = Sheets(ActiveSheet.Name).Range("CA4")
rngCut.Cut
rngPaste.PasteSpecial xlPasteAll
Set rngPaste = Sheets(ActiveSheet.Name).Range("AW4")
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats
Set rngPaste = Sheets(ActiveSheet.Name).Range("BC4")
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats
Set rngPaste = Sheets(ActiveSheet.Name).Range("BI4")
rngPaste.PasteSpecial xlPasteValues
rngPaste.PasteSpecial xlPasteFormats
    ' Make as many copies as specified in F1
Set MyRange = Range("AW4:BT42")
iNumCopies = Range("E1").Value
	If iNumCopies < 1 Then iNumCopies = 1
MyRange.PrintOut Copies:=iNumCopies, Collate:=True
    ' Go to A4
Range("A4").Select 
ThisWorkbook.Save
    End Sub

Thanks for your input!

Lupp · August 22, 2018, 10:29am

This seems to make clear that the bug is specifically afflicting the range.Copy statement of Excel-VBA, but I would like to know the details I asked for editing my own answer for a bug report. Can you supply the information?