Bug 122684 - Copying range using VBA doesn't work correctly
Summary: Copying range using VBA doesn't work correctly
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.4.0 target:7.3.1
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
 
Reported: 2019-01-13 07:30 UTC by Gunnar
Modified: 2022-01-27 12:34 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Workbook with macros (14.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-01-13 09:58 UTC, Gunnar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gunnar 2019-01-13 07:30:09 UTC
Description:
When copying cell formulas using VBA, the normal method is:
Worksheets("Sheet1").Range(D4:E6).Copy
Worksheets("Sheet2")Range("D4:E6").PasteSpecial Paste:=xlPasteFormats
What is copied is whatever range that is selected!
To make it work, you must select the range you want to copy:
Add this line first:
Worksheets("Sheet1").Range(D4:E6).Select
 

Steps to Reproduce:
1.
Worksheets("Sheet1").Range(D4:E6).Copy
Worksheets("Sheet2")Range("D4:E6").PasteSpecial Paste:=xlPasteFormats

2.
Worksheets("Sheet1").Range(D4:E6).Select
Worksheets("Sheet1").Range(D4:E6).Copy
Worksheets("Sheet2")Range("D4:E6").PasteSpecial Paste:=xlPasteFormats


Actual Results:
1. Formatting copied from range that is selected, not the range specified!
2. This solves the problem but isn't the correct way to do format copy

Expected Results:
See Actual Results


Reproducible: Always


User Profile Reset: No



Additional Info:
[Information automatically included from LibreOffice]
Version: 6.1.4.1 (x64)
Build ID: 25073d18caee244880112e52c4a7e71f6081b3a9
CPU threads: 4; OS: Windows 10.0; UI render: GL; 
Locale: nb-NO (nb_NO); Calc: CL
Comment 2 raal 2019-01-13 08:50:42 UTC
Please attach test file. Thank you
Comment 3 Gunnar 2019-01-13 09:58:38 UTC
Created attachment 148284 [details]
Workbook with macros

This file contains macros that explain the problem.
See content for further descriptions
Comment 4 Oliver Brinzing 2019-01-13 14:52:02 UTC
i can confirm the problem:
format from selected cell is copied instead of format from source range

possible workaround:

Worksheets("Sheet1").Range(rSrc).Copy   
  destination:=Worksheets("Sheet2").Range(rDSt)
Worksheets("Sheet2").Range(rSrc).ClearContents
Comment 5 Gunnar 2019-01-13 23:20:04 UTC
Thanks!
But the code fails to remove the contents of the copied range.

	rSrc = "B2:C5"
	rDst = "E2"
	Worksheets("Sheet1").Range(rSrc).Copy destination:=Worksheets("Sheet2").Range(rDSt)
	Worksheets("Sheet2").Range(rDst).ClearContents

rDst points to upper left corner in the destination sheet, which is cleared.
rScr and rDst are not equal!
To make it work, you need to set rDst = "E2:F5" (same size)
But anyway, it's close to a solution.

This bug cost me a lot of trouble because what was copied was changing all the time since the selected range was random, and I couldn't get hold of what was going on. I was close to giving up until, by luck, the selected cell had a different background color. Viola!
Comment 6 Julien Nabet 2019-01-17 09:12:01 UTC
On pc Debian x86-64 with master sources updated yesterday, I could reproduce this.

Reverting https://cgit.freedesktop.org/libreoffice/core/commit/?id=fd64103319a28fedb44cdc0438887914dba396cb, the pb doesn't appear.

Tamas/Eike: any thoughts here?
Comment 7 Xisco Faulí 2019-01-18 13:12:47 UTC
(In reply to Julien Nabet from comment #6)
> On pc Debian x86-64 with master sources updated yesterday, I could reproduce
> this.
> 
> Reverting
> https://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=fd64103319a28fedb44cdc0438887914dba396cb, the pb doesn't appear.

I do confirm it with bibisect-linux64-6.0

Adding Cc: to Tamas Bunth
Comment 8 Hossein 2022-01-27 11:30:05 UTC
A fix is provided by Tomaž in Jan 21 2022:

commit	  0c6ee963e1f089cb73e0c68a28af29d0f8d9e0df
author    Tomaž Vajngerl <tomaz.vajngerl@collabora.co.uk> Tue Jan 18 15:34:49 2022 +0900
committer Tomaž Vajngerl <quikee@gmail.com>	          Fri Jan 21 10:42:02 2022 +0100

This is fixed by reverting the changed part in 'core/sc/source/ui/vba/vbarange.cxx'
Comment 9 Xisco Faulí 2022-01-27 12:24:02 UTC
(In reply to Hossein from comment #8)
> A fix is provided by Tomaž in Jan 21 2022:
> 
> commit	  0c6ee963e1f089cb73e0c68a28af29d0f8d9e0df
> author    Tomaž Vajngerl <tomaz.vajngerl@collabora.co.uk> Tue Jan 18
> 15:34:49 2022 +0900
> committer Tomaž Vajngerl <quikee@gmail.com>	          Fri Jan 21 10:42:02
> 2022 +0100
> 
> This is fixed by reverting the changed part in
> 'core/sc/source/ui/vba/vbarange.cxx'

Just for the record, the commit was backported to libreoffice-7-3 branch. the fix will be available in LibreOffice 7.3.1
Comment 10 Xisco Faulí 2022-01-27 12:34:15 UTC
Verified in

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 2f4f4cbeb8e50081d607b86b0475b93971c40ab8
CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: es-ES (es_ES.UTF-8); UI: en-US
Calc: threaded

@Tomaz, Thanks for fixing this issue!!