Bug 101631 - VBA function parameter is passed by reference by default
Summary: VBA function parameter is passed by reference by default
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
5.1.4.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: interoperability
Keywords: needsDevAdvice
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
 
Reported: 2016-08-21 03:42 UTC by Laurie Lang
Modified: 2022-09-16 18:48 UTC (History)
6 users (show)

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


Attachments
An Excel file which causes strange behaviourin Calc. (37.50 KB, application/vnd.ms-excel)
2016-08-21 03:42 UTC, Laurie Lang
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Laurie Lang 2016-08-21 03:42:56 UTC
Created attachment 126927 [details]
An Excel file which causes strange behaviourin Calc.

When editing an Excel spreadsheet, some entries, for no apparent reason, modify themselves. For example, the following sequence of numbers was entered in a row of cells, and as they were entered they changed to the numbers shown in the second line:
1 2 3 4 5 6 7 8 9
1 2 3 4 4 6 6 8 9
Then, just for fun, the same sequence (1..9) was entered into the line below the first:
1 2 3 4 4 6 6 8 9
1 2 3 4 5 6 7 8 9
and the numbers became:
1 2 3 4 3 6 5 8 9
1 2 3 4 3 5 5 8 8 
(Note the the first line changed also.)
I have attached a modified version of the spreadsheet, which behaves normally in Excel, but not in Calc.

I am using version 5.1.4.2
Comment 1 Aron Budea 2016-08-21 04:45:32 UTC
The direct reason for the self-modifying behavior is that function "stableford" modifies the "score" parameter, which is apparently passed by reference.

I can't tell if it's the correct behavior or not, got confused by different VB-related MS resources that state contradicting things:
https://msdn.microsoft.com/en-us/library/aa263527(v=vs.60).aspx "Passing by reference is the default in Visual Basic."
https://msdn.microsoft.com/en-us/library/ddck1z30.aspx "The default in Visual Basic is to pass arguments by value."
They're for different VB versions, and not VBA, either, but it's still weird.

Until this is clarified, as a workaround you can copy the parameter to a local variable, and modify that.
Comment 2 Cor Nouws 2016-08-22 09:20:50 UTC
(In reply to Aron Budea from comment #1)

> I can't tell if it's the correct behavior or not, got confused by different
> VB-related MS resources that state contradicting things:
> ...

;)

> They're for different VB versions, and not VBA, either, but it's still weird.

From what I remember, passing as value is default in VBA.
Comment 3 Aron Budea 2016-08-22 13:23:26 UTC
(In reply to Cor Nouws from comment #2)
> From what I remember, passing as value is default in VBA.

This page also says arguments are passed by reference by default.
https://msdn.microsoft.com/en-us/library/office/gg264721.aspx

I wonder if this got changed at some point...
Though Laurie wrote it was working fine in Excel, which would indicate the arguments are indeed passed by value there.
Comment 4 Xisco Faulí 2017-11-14 09:38:44 UTC
Hi Laurie Lang,
Should the numbers be added in cell I21:Q23 ?
Comment 5 Xisco Faulí 2018-01-17 15:03:57 UTC
@Eike, any insight here?
Comment 6 sukamin 2020-05-08 06:52:22 UTC Comment hidden (spam)
Comment 7 Eike Rathke 2020-05-08 10:14:21 UTC
Fwiw, arguments passed from Calc to BASIC are passed by value, the stableford() macro function can not modify cell values that way.
Comment 8 Aron Budea 2021-09-13 22:41:42 UTC
(In reply to Eike Rathke from comment #7)
> Fwiw, arguments passed from Calc to BASIC are passed by value, the
> stableford() macro function can not modify cell values that way.
Does that mean VBA functions are using a different way of passing arguments in Calc compared to its own BASIC?
Comment 9 Eike Rathke 2021-09-14 09:46:20 UTC
Normally (...) for user defined functions that are invoked as spreadsheet functions, yes.

Now it gets complicated: for whatever reason (I don't know the history behind that specific VBA implementation) if there is
Option VBASupport 1
which here is the case then a XCellRange object is passed for cell references. That indeed allows BASIC to modify the cell. It should not and can result in undesired side effects, like here even Err:522 circular references being detected because the cell value modified triggers another recalc that runs into a currently being calculated cell.

Changing stableford() to not modify its score argument gets rid of all side effects.
Comment 10 Joshua Coppersmith 2021-11-14 02:20:16 UTC
As usual, Eike not only gives an explanation, but also a workaround. And kudo's for the bug...I've never seen anything quite as dynamic as constant number entries changing before my eyes!

However, I think I have something to add here.

In VBA Variants act a lot like reference names (i.e. wrappers). Even if you pass a Variant using the ByVal keyword that may not give expected results. However, if you pass Integers you will get pass-by-value behavior (I seem to remember, I've moved on to native LO :) ). So instead of

Function stableford(score, hcp, ...

use

Function stableford(score As Integer, hcp As Integer, ...

and to be really sure, use

Function stableford(ByVal score As Integer, ByVal hcp As Integer, ...

I modified the example Excel workbook only by changing the stableford signature  to this "really sure" version and then could not reproduce any peculiarities, neither Err:522 nor magic changing numbers.

There used to be a trick of putting caller parameters in parentheses when calling from code to code in VB, to force a calculation before the caller parameters were put on the VB stack, sort of defeating ByRef behavior in the called function. (If I remember correctly...) You can't do that directly from a spreadsheet, of course, so you have to armor-plate the function signature itself.