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
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.
(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.
(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.
Hi Laurie Lang, Should the numbers be added in cell I21:Q23 ?
@Eike, any insight here?
I have read your article, it's very informative and helpful for me. I admire the valuable information you provide in your articles. Thank you for posting it. [url=https://shell-shockers.online]shell shockers[/url]
Fwiw, arguments passed from Calc to BASIC are passed by value, the stableford() macro function can not modify cell values that way.
(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?
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.
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.
(In reply to Aron Budea from comment #1) > 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. Just for note, the 'Visual Basic" referred to in the .NET MS resource "The default in Visual Basic is to pass arguments by value", is VB.Net VB.net is to VBA as c# is to c, only less so.