mandagusis
mandagusis

Reputation: 1

Libre calc macro works until computer restart

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

Answers (0)

Related Questions