Download it now!
Bug 64048 - FORMATTING: clearcontents in VBA code clears formats
Summary: FORMATTING: clearcontents in VBA code clears formats
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: All All
: high normal
Assignee: Not Assigned
URL:
Whiteboard: BSA bibisected40 target:4.1.0 target:...
Keywords: regression
Depends on:
Blocks:
 
Reported: 2013-04-29 14:09 UTC by Steve Leffler
Modified: 2015-12-22 01:31 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel file exhibiting the bug (29.00 KB, application/vnd.ms-excel)
2013-04-30 14:41 UTC, Steve Leffler
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Steve Leffler 2013-04-29 14:09:15 UTC
Problem description: 
In VBA code, the "ClearContents" method on a VBA range object clears cell formatting. It should not: in VBA, ClearContents 

Steps to reproduce:
1. Open a .xls file with macros
2. Give cell B10 some text and pretty formatting.
3. Add the following macro to the VBAProject:
   sub Test ()
	range("b10").clearcontents
   end sub
3. Run the macro. The text and formatting both disappear.

Current behavior:
In VBA code, the "ClearContents" method on a VBA range object clears cell formatting. 

Expected behavior:
In VBA, ClearContents clears values (of any type) and formulas, but not cell formats. 

This bug is frustrating because I can't find any way to clear cell contents that works in both Excel and LibreOffice. The Star Basic clearcontents(CellFlags) doesn't work on VBA Range objects, and LibreOffice does not clear cell contents if I assign "Null" to them, as Excel does.
              
Operating System: Windows XP
Version: 4.0.2.2 release
Comment 1 Joel Madero 2013-04-30 04:28:40 UTC
that doesn't work correctly on my side. Please attach a document with the macro, makes it much easier to triage.


Marking as NEEDINFO - once you attach mark as UNCONFIRMED and we will check it out.


I am 99% sure that this is a duplicate but I can't seem to find it
Comment 2 Steve Leffler 2013-04-30 14:41:30 UTC
Created attachment 78650 [details]
Excel file exhibiting the bug

The attached file, ClearContentsBug is an Excel file that exhibits the problem. Cells B10 and B11 have yellow text on a red background. Clicking the button runs a simple macro. In Excel, the macro removes the text but leaves the formatting. In LibreOffice the formatting is removed as well.
Comment 3 Joel Madero 2013-04-30 21:41:50 UTC
Thank you for reporting this issue! I have been able to confirm the issue on:
Version 4.0.0.3 release 
Platform: Bodhi Linux 2.2 x64
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
As I've been able to confirm this problem on an earlier release I am changing the version number as version is the earliest version that we can confirm the bug, we use comments to say that the bug exists in newer versions as well.

Marking as:

New (confirmed)
Normal - can prevent high quality work
High - regression

Keywords - regression

Whiteboard Status - bibisected40

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:
https://wiki.documentfoundation.org/QA/BugTriage

There are also other ways to get involved including with marketing, UX, documentation, and of course developing -  http://www.libreoffice.org/get-help/mailing-lists/. 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material







d36d5fa147a298291ec3301b558ec6dafec170a5 is the first bad commit
commit d36d5fa147a298291ec3301b558ec6dafec170a5
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Tue Dec 11 10:20:39 2012 +0000

    source-hash-2e053cf5ea4d93a2e1845e795a9c7fe1e08c84af
    
    commit 2e053cf5ea4d93a2e1845e795a9c7fe1e08c84af
    Author:     Takeshi Abe <tabe@fixedpoint.jp>
    AuthorDate: Wed Dec 5 08:33:07 2012 +0900
    Commit:     Takeshi Abe <tabe@fixedpoint.jp>
    CommitDate: Wed Dec 5 10:42:17 2012 +0900
    
        sal_Bool to bool
    
        Change-Id: Ic1cc3312e61e602c33be7444f8d4bbad9268ae77

:100644 100644 42f18f15fb3370ca3f85d6a637b17ae05b10996f 4e4564a1257d79f9fd86b264270d348bf06d23e3 M	autogen.log
:100644 100644 b4973c1d86cfd5c0d550e84e7e483802d979829e 1224d320eac5e10b39e8481456547368fe09c748 M	ccache.log
:100644 100644 a0e5b02e554537a0bed70a3203ca3995bb6972bd ab62cc407b67c0ca8a1a96f877dc86a92f01e1ef M	commitmsg
:100644 100644 6ba1538977f3fb5b8f0ee54bbd04be28afdd85e3 575f5b911fb36490ff12ae85452ce424b34a3382 M	dev-install.log
:100644 100644 dc4842a6e74513bbe562bb0d78a1ed545ead53e7 3f76084c0057251a1332a9be8b51e31a06e5590e M	make.log
:040000 040000 e70233c1668fc1da017a420ac2ed5ee366e253ad 9457873d1fe1b1108dc20777c8f0180cefe48cdd M	opt



# bad: [5b4b36d87517a6ea96ff8c84c46b12f462fc9a1a] source-hash-8450a99c744e9005f19173e4df35d65640bcf5c4
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [16b0b88cbd4ef0f51816e97277e40c5cf78f7bf9] source-hash-099198a4224778fe6e43f5dc13b5b9b1b4dc828c
git bisect good 16b0b88cbd4ef0f51816e97277e40c5cf78f7bf9
# good: [f28b8f9a6c47fa59bf98fffe937a2f2db7a2445a] source-hash-a581d31b227623e09d2970a91214fda398f98eda
git bisect good f28b8f9a6c47fa59bf98fffe937a2f2db7a2445a
# good: [114fd3b76bcba890e6d702d00cef910f1493c262] source-hash-64ab96cd15e52da88781e720d6f031dbcd0ba902
git bisect good 114fd3b76bcba890e6d702d00cef910f1493c262
# good: [47498a36f7af8f54e6e3dda89cd4708802a409e6] source-hash-19f4ebd8a54da0ae03b9cc8481613e5cd20ee1e7
git bisect good 47498a36f7af8f54e6e3dda89cd4708802a409e6
# good: [66083171ba534818399937597388d325091fffab] source-hash-9d83ad0e99ab182506be99f7d6a2bec7f6fbe8c6
git bisect good 66083171ba534818399937597388d325091fffab
# good: [8407d6926d9fa83ebbd8e03de319ea63df131753] source-hash-3d4288c1c0b593421c7f6619c88584bdb7c53337
git bisect good 8407d6926d9fa83ebbd8e03de319ea63df131753
# good: [eb01a04acbb7373331312a2ee3d9d90d20548fca] source-hash-ce90f99a2d66c2b998ad3f9f028e2ea623a757f5
git bisect good eb01a04acbb7373331312a2ee3d9d90d20548fca
# bad: [8990f02131a55bce7a0b0e9fb6614b2a972306ff] source-hash-9d7c1dcbbbce69bebec54d85004a0da5074775cd
git bisect bad 8990f02131a55bce7a0b0e9fb6614b2a972306ff
# bad: [d36d5fa147a298291ec3301b558ec6dafec170a5] source-hash-2e053cf5ea4d93a2e1845e795a9c7fe1e08c84af
git bisect bad d36d5fa147a298291ec3301b558ec6dafec170a5
Comment 4 Joel Madero 2013-04-30 21:42:05 UTC
Also confirmed on 4.1 master built just a couple days ago
Comment 5 Michael Meeks 2013-05-02 15:25:57 UTC
Introduced while re-basing; pushed a fix to master, and (I hope) Noel will review it for 4.0 (I'll push it to gerrit).

Thanks for the report Steve ! As you can see the fix was really trivial - it's ~always well worth doing a 'git grep' or opengrok search for the method in the code to see if you can find out what's up there - if you have VBA programming skills and can make nice test cases like this, it shouldn't be hard to help out code-wise :-).
Comment 6 Commit Notification 2013-05-02 15:28:06 UTC
Michael Meeks committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b701650c9257e82e8e5ec46451653b661aa65d28

fdo#64048 - fix calc/VBA clearcontents regression.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2013-05-03 09:06:11 UTC
Michael Meeks committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0849bd9c5ef5ac885527cb98dafb9cea6200d8e5&h=libreoffice-4-0

fdo#64048 - fix calc/VBA clearcontents regression.


It will be available in LibreOffice 4.0.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 8 Robinson Tryon (qubit) 2015-12-22 01:31:47 UTC
Removing comma from Whiteboard (please use a space to delimit values in this field)
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Whiteboard#Getting_Started
[NinjaEdit]