Reputation: 1
There is a slight problem I am having with Libre office Calc macro.
I used code from libre office calc macro tutorial, and modified for my needs.
However, it worked the first time I ran it, but the next day it didn't work.
I tried changing lines, enabling Option VBAsupport 1
and Option Compatible
on and off, but it with enabled support all I got was #NULL?
back, while with disabled support I would get back "BASIC error: Sub-function not defined" with a pointer to line skaicius = Round(y)
- understandably, because I disabled VBAsupport.
When commenting line If x(iRow, iCol) >= 0 Then TheSum = TheSum + "," + CStr(normalRound(x(iRow, iCol)))
, then the code works, but without the commented line the code isn't useful.
After an hour of CTRL-X and CTRL-V, disabling/enabling different lines - it works! But I don't add nor delete any lines, variables etc.
But once I restart my computer, the code that worked before doesn't work anymore
The code that somehow works now:
REM ***** BASIC *****
'Option VBASupport 1
'Option Compatible
Sub Main
end sub
Function PositiveSum(Optional x)
Dim TheSum As String
Dim iRow As Double
Dim iCol As Double
TheSum = "{"
If NOT IsMissing(x) Then
If NOT IsArray(x) Then
' If x > 0 Then TheSum = CStr(Round(x))
Else
For iRow = LBound(x, 1) To UBound(x, 1)
TheSum = TheSum +(x(iRow, LBound(x,2)))*100 +":["
For iCol = LBound(x, 2)+1 To UBound(x, 2)
If x(iRow, iCol) >= 0 Then TheSum = TheSum + "," + CStr(normalRound(x(iRow, iCol)))
Next
TheSum = TheSum + "],"
Next
End If
End If
PositiveSum = TheSum + "}.get(aukstis, )"
End Function
Function normalRound(Optional y)
Dim skaicius as Double
Dim skaicius2 as Double
skaicius = Round(y)
skaicius2 = y
If skaicius < skaicius2 and skaicius+0.5 < skaicius2 Then skaicius = skaicius+1
If skaicius > skaicius2 and skaicius-0.5 > skaicius2 Then skaicius = skaicius-1
normalRound = skaicius
End Function
Question: Why does the code behave in that way? Why does it work now (while looking at it - it shouldn't work at all), but after computer restart it won't work, even though I will not change anything until that time?
The code is used to take a range of cells, and append them to one long string, so I could copy to other code.
EDIT:
I'm calling function PositiveSum
as =PositiveSum(A1:C3)
where A1:C3 is my array.
I have no idea what's not working, this is my question. Because constantly to make code work, i have to copy-paste it multiple times, comment and uncomment for it to work again. Surely, I am missing some specific reason why it breaks every time, but after weeks I admit that I failed to find the cause/reason
Upvotes: 0
Views: 157