Cell values not being correctly created in a macro

English
#1

I am trying to convert an EXCEL spreadsheet to LibraOffice. The spreadsheet actually creates another spreadsheet consisting of 12 sheets. One for each month with 2 graphs on each sheet. I am working through the changes, and create the first sheet, up to the point of creating the graph.
Everything is done through code. This includes putting the formulas in the correct cells.
This is the code for a couple of the cells:
With Worksheets(MonthData(monthno).Mtext)
.Range(“C3”).Value = “=IF(B3>0,SUM($B$3)/COUNTIF($B$3,”">0""),"""")"
.Range(“N3”).Value = “=SUM(B3:B” & MonthData(monthno).Mlength + 2 & “)”
end with

Now when I see the result Cell C3 shows error 508. Cell N3 shows 0, as it should. All cells with formulas with more formula than cell N3 display error 508.
When I select cell C3 it shows
=IF(B3>0,SUM($B$3)/COUNTIF($B$3,">0"),"")
This is correct.there are no unbalanced parentheses. But just in case I added a parenthesis. When I hit Enter I got a message that there was an error in the formula. It showed that the correction was the same as before. When I accepted the correction, the error Disappeared.
This is just one of about 200 error codes. I do not know why it will not accept the correct values. I don’t want to have to correct these manually. Any guidance would be appreciated.
Thank you
John Crawford

#2

Please start with

  • Post the/a complete macro, so one can test it on his/her own computer.
  • As we can’t see it. How are your settings of VBA-compatibility? Do you just try to get VBA-code running or do you wish to transfer to “native” LibreOffice?
  • Surround your code with code tags ( the symbol </> when editing, because we can copy s="Text" much easier than your s=“Text” )

Maybe types? The first piece I see: I’d use setFormula instead of setValue as described in help:
https://help.libreoffice.org/latest/en-US/text/sbasic/guide/read_write_values.html

1 Like
#4

Sorry, I don’t understand the Calc formulas that well. What is SUM() of one cell?

#5

OK, I will try to explain.
Do not think of this as a macro that contains a few lines. Think of it as an application. The function under discussion is 537 lines. Also it does not stand alone. There is a lot of setup that is done, and other functions called. All together it is over 1900 lines! Now, I do not expect anyone to take the time/effort to understand the application and then try to figure out why it is not working. If someone is brave enough to do that then let me know how to get it to you and I will send the entire thing to you.
I basically just wanted someone to tell me whether this was a possible problem with LibraOffice, a problem with the statement itself, or it should work.
I did not set the VBA compatability. That seemed to be added by LibraOffice. There was a VBASupport option set to 1, and an option specifying a VBAModule type.
I referenced the help you suggested, and got the same result when I tried it.
This whole exercise is to get this application working in LibraOffice as easily as possible. I am surprised how easily I got to this point. There were very few changes that I have had to make. Mostly they were to colour names, and parameters that were either different or not in LO. None of this was a problem. I think that the charts will cause some problems. Currently the calls to the graph creation is commented out.

#6

It is equivalent to =$B$3/COUNTIF($B$3…
I probably copied a line from somewhere else and just removed the other part of the Range.

#7

When I read something like SUM($B$3) I assume the creator wished to get a value (0), even if the cell contains text, as text is ignored by SUM(). But I have not tested this for single cells.

Here it is more funny, as B3 is tested first to be greater than 0, so we can assume to have a Number in B3, wich gives $B$3 as result of the SUM()
.
Then we have a COUNTIF wich counts how often the contents are greater than 0. For a “one cell range” I expect possible results 0 and 1. Luckily it was tested before B3 is >0 so we will have 1, and a division of $B$3/1 gives $B$3. The only other option is NaN for a division by 0, as the first test is not an absolute address.
.
So the formula seems an Excel equivalent of =IF(B3>0;$B$3;"") but this variant would not try a division by 0, if dragged down…

#8

SUM() returns the total of all arguments. If there is one argument it returns that value. In the example yes, if the cell contains a value > 0 it will return 1. The IF verifies the cell contents. Is there anything wrong with the statement? NO. You may not agree with the code, which is your right. However this has nothing to do with the original problem. BTW I did reduce it to the simplest terms, and got the same result.
I probably could have chosen another of the approx. 200 other formulas, some of which are much more complex. But I did not. All but the very simplest have the same problem. The second formula example does produce the correct result.

#9

When not all your formulas habe problems, I’d think you found/met a bug.
Reading your code I see you use WITH
Maybe check the following bug-report then, don’t know if it is related (btw: wich version of LO do you use?):
https://bugs.documentfoundation.org/show_bug.cgi?id=132064

#10

I’m not sure what MonthData() does and what its .Mtext and .Mlength properties contain. But the code snippet you showed in your question could be rewritten as

	With ThisComponent.getSheets().getByName(MonthData(monthno))
		.getCellRangeByName("C3").setFormula("=IF(B3>0;$B$3;"""")")
		.getCellRangeByName("N3").setFormula("=SUM(B3:B" & MonthData(monthno) + 2 & ")")
	End With
#11

First, the name is LibreOffice, not Libra.

In my opinion, you must rewrite all of the VBA macros, because the VBA and the API of the LO are not compatible. The LO can execute some of the VBA macros, but the Excel can not execute the LO API functions.
.
.Range(“C3”).Value = “=IF(B3>0,SUM($B$3)/COUNTIF($B$3,”">0""),"""")"
.
If you want to set a working FORMULA in a LO Calc cell by a macro, then you need use the .formula command, and not the .value. The numerical value of a string is always equals zero.

1 Like
#12

I looked at the report you mentioned. Thanks, but I do not think that it is related to my problem.
Sorry, I should have included the version in my original post. It is:
24.8.3.2
Win 10 X86_64

#13

MonthData is a structure containing 2 members. Mtext = month name (e.g. Jan, Feb etc). Mlength = month length (e.g. 31, 28 etc). If it is a leap year, the Feb value is altered to 29. Both of these are used throughout the app to reference a sheet, create titles, determine how many times an action is taken, among other things. I tried a variation of your suggestion, and the result was the same. I will try your variation. Thanks.
John

#14

Sorry about the name.
I will not rewrite the entire application, It is just too much effort, when I can create 2 versions in EXCEL, one for leap year and one for not. It will require me to go in and change year information on all sheets, but it can be done.
I had hoped to be able to port the application to LO, and was very encouraged when all the sheet formatting worked perfectly. The thing that keeps bothering me is that a few of the assignments work. Since all assignments use basically the same format, all should work, or none should work.
It does appear, however, that no one else has seen this problem. Meaning that it is something within the application that is causing the problem.

#15

We can not help you without seeing the full code of the macro… Or at least the full code (from “Sub” to “end sub”) of that one subroutine.

#16

You can calculate this by simple cell functions without any macro. Just combine the functions
isleapyear
month
eomonth
day

#17

I did not use the function for a few of reasons.
Firstly the cell function to me is stupid. All dates in a leapyear are the leapyear. All dates not in a leapyear are not the leapyear. Why supply a complete date when only the year is imporatant?! Also cell functions cause errors in a macro, unless you specify “Worksheetfunction” before So for this to work in a macro I would need:

Worksheetfunction.isLeapYear(WorksheetFunction.Date(year, month,day))`

Blockquote
Doesn’t make much sense does it?

Secondly it is a nostalgia thing. I wrote the function in about 1983. It has stood up in various languages, various levels, and it is comfortable to me.

#18

As I mentioned in a previous reply it is basically all or nothing. The routine cannot stand alone. There is previous code that sets up information for this routine. For me to modify the code to make it standalone may cause other issues, or cover up an existing issue. The only other routine that could be physically removed is that that creates the charts. It is currently commented out because it causes errors. I think that at this point the best thing to do is to close this thread. I I am not sure how to do that.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.