Bug 131562 - Cannot concatenate number and space+dot from VBA macro
Summary: Cannot concatenate number and space+dot from VBA macro
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.1.0 target:7.0.0.2
Keywords:
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
 
Reported: 2020-03-25 11:41 UTC by NISZ LibreOffice Team
Modified: 2020-07-09 21:22 UTC (History)
5 users (show)

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


Attachments
Example file from Excel (12.89 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2020-03-25 11:41 UTC, NISZ LibreOffice Team
Details
Screenshot of the original document side by side in Excel and Calc after running the macro (79.92 KB, image/png)
2020-03-25 11:41 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2020-03-25 11:41:21 UTC
Created attachment 158973 [details]
Example file from Excel

Attached file contains a simple macro to concatenate a number in cell plus a “ .” (space dot) sequence.

This call (with m1 being a loop variable) does not work in Calc:

Cells(3, m1) = m1 & " ."

However if some more characters are added to the concatenation, it works:

Cells(4, m1) = m1 & " .cat"

Using only a dot does not work in Excel either, and using only spaces works in Calc too.

Steps to reproduce:
1.	Open attached xlsm file
2.	Run the macro in Excel and Calc

Actual results:
In the third row Excel concatenates the number and the space-dot sequence, Calc does not.
In the fourth row the concatenation works as expected.

Expected results:
Calc concatenates a space and dot to the numbers in row 3.

LibreOffice details:
Version: 7.0.0.0.alpha0+ (x64)
Build ID: bc898e2c2784e36ad4d4cdf6d962e39069d2c82d
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win; 
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: CL

Also in:
LibreOffice 3.5.0rc3 
Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Comment 1 NISZ LibreOffice Team 2020-03-25 11:41:50 UTC
Created attachment 158974 [details]
Screenshot of the original document side by side in Excel and Calc after running the macro
Comment 2 himajin100000 2020-03-25 14:43:03 UTC
* "1." is valid string that can be converted to number

* assigning to cells evaluates the string "1.", and if the string is a number, then the cell value is set to that number. In this case, namely 1

* if the cell formats for the cells are set to Text instead of Number before the script is run, you will see the expected results when your code is run.
Comment 3 Eike Rathke 2020-06-16 11:49:20 UTC
The resulting string is not "1." but "1 ." with a space between digit and dot, which is not a number. Also note the hu-HU locale that uses comma decimal separator (unless cells are formatted differently). However, the same (recognizing a number) happens in an en-GB locale with dot decimal separator, but only when running the macro, not with manual input. It seems as if VBA is trying to convert the string badly.
Comment 4 Mike Kaganski 2020-06-16 11:54:07 UTC
(In reply to himajin100000 from comment #2)
> * "1." is valid string that can be converted to number
> 
> * assigning to cells evaluates the string "1.", and if the string is a
> number, then the cell value is set to that number. In this case, namely 1
> 
> * if the cell formats for the cells are set to Text instead of Number before
> the script is run, you will see the expected results when your code is run.

The question is, if digit + space + decimal separator is a number, just as digit plus decimal separator. By the way, in case of e.g. ru-RU locale, it's space plus comma that wouldn't work here.

Entering the "1 .1" (assuming dot as decimal separator) in the Calc's formula bar, it's obvious that Calc ignores the space here. The question is: is this expected? Does Calc consider the space thousand separator maybe (and allows in in wrong place)?
Comment 5 Eike Rathke 2020-06-18 12:14:59 UTC
(In reply to Mike Kaganski from comment #4)
> The question is, if digit + space + decimal separator is a number

No, it's not a number and shouldn't be.

> Entering the "1 .1" (assuming dot as decimal separator) in the Calc's
> formula bar, it's obvious that Calc ignores the space here.

wtf.. I'd consider that a bug.

> The question is:
> is this expected? Does Calc consider the space thousand separator maybe (and
> allows in in wrong place)?

No, if the locale doesn't say the group separator is space then a space is not ignored as such (and a group separator followed by a decimal separator is invalid). But apparently the number parser tries to skip spaces at one too many place.
Comment 6 Eike Rathke 2020-07-03 18:59:25 UTC
@Mike: does Excel accept any of these inputs as numbers?

1 .2
1. 2
1 . 2
 . 2
. 2
Comment 7 Eike Rathke 2020-07-03 19:22:22 UTC
Solution with all of those not numbers in https://gerrit.libreoffice.org/c/core/+/97895
Comment 8 Mike Kaganski 2020-07-03 20:31:56 UTC
(In reply to Eike Rathke from comment #6)
> @Mike: does Excel accept any of these inputs as numbers?
> 
> 1 .2
> 1. 2
> 1 . 2
>  . 2
> . 2

No, none of them.
Comment 9 Eike Rathke 2020-07-03 23:22:10 UTC
Great, thanks Mike.
Comment 10 Commit Notification 2020-07-06 07:01:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/7186541219599e1b51ad35601c2cd015a329f360

Resolves: tdf#131562 decimal separator may not be surrounded by blanks

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Eike Rathke 2020-07-06 07:07:18 UTC
Pending https://gerrit.libreoffice.org/c/core/+/98074 for 7-0
Comment 12 Commit Notification 2020-07-06 11:12:09 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/91ccd9fda9c953dbe88b99673491aabeb64ff330

Resolves: tdf#131562 decimal separator may not be surrounded by blanks

It will be available in 7.0.0.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Xisco Faulí 2020-07-06 21:27:05 UTC
Verified in

Version: 7.1.0.0.alpha0+
Build ID: 358674b87b8d9cd78079fb105aa81b50f4b5029b
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

@Eike, thanks for fixing this issue!!
Comment 14 Commit Notification 2020-07-09 21:22:57 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b8ff69726959ee4d148c12866d64601d86635bcc

tdf131562: sc_macros_test: Add unittest

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.