I have a spreadsheet that has macros to calculate sunrise/set/twilights/noon which came as an Excel file that is freely downloadable. It’s called twighlight.xls by Greg Pelletier.
I use it as a backend to an itinerary spreadsheet with dates and coordinates of locations to determine sunrise/set at those dates and places.
Latitude, longitude, year, month, day, timezone and daylight savings status are parsed to macros to determine the times for those events.
Everything worked fine for years until I had to create an itenerary for February. The month value is defined by the formula =MONTH(cell_id), extracting the month from the full date in another cell, and February would evaluate to 2.
When this cell is parsed to any of the macros, the formula is replaced with a static value of 2 so wouldn’t update if the date was changed to a different month in the parent cell. The cell above B7, containing the formula to extract the year, also changes to a static value, so wouldn’t update if the year changes. The same occurs if the formula evaluates to 1, meaning January.
I’ve tried it running as a .xls file and converted to a .ods file with the same results. I dragged up an old XP machine with Office2003 and it works fine in that. It’s only in Libreoffice that formulas evaluating to 1 or 2 are replaced with static values.
To replicate, download and open the twighlight.xls file and on the sunrise page go to cell B7. Replace the value (should be 6) with =6/2 and hit TAB to evaluate the formula to 3. The cells B13-B21 will update. Look at the contents of cell B7 which will still be =6/2. Change it to =6/3 and hit TAB. The contents of cell B7 is now 2.
If you also change the contents of cell B6 to a formula, this will also change to a static value when cell B7 evaluates to 1 or 2.
Is this a genuine bug or am I missing something? Do I need to install JRE to get reliable running of macros? The XP machine has JRE 1.6.0.20 installed.
twilight.xls (152.5 KB)
Please upload your sample file/s here.
Uploaded twilight.xls. Hope someone can help. I don’t really want to use MS Office on XP.
JRE not needed for running Basic code.
But the MS VBA is not compatible with the StarBasic (the Basic of the LibreOffice) and the API of the LibreOffice. (API: Application Programming Interface)
Some VBA codes can run in the compatibility mode, but always better to rewrite your VBA macros based on the Starbasic+API combo.
This makes it difficult for me as I’ve never learnt any of the Basic languages. I’ve looked at the macros and it looks to me that the calcJD function might be to blame as it is mucking about with months when it is <=2 but I’m not confident of what changes to make.
It’s probably not fair to ask someone to fix this for me unless they consider this spreadsheet to be useful in the public domain as LibreOffice compatible and want to make it a pet project.
- What do you mean by “parsed”. Should I read “passed”?
- If you claim something to be “freely downloadable”, please post a link to the source location and by that to the license conditions.
- Concerning the example: Which one of the many UDF or Sub did not work as you expected (hoped).
I have no means to check the results, but I can’t confirm the problems you described. The code produces roughly plausible results with LibO V 7.2.1.2.
Nonetheless I would suggest to
- Show the cell grid! After all you need to find the cells you want to enter something in.
- Use a (the) reasonable
NumberFormat HH:MMfor the respective cells in place of the outdated format the sheet comes with. - To Module1 add
Option Compatiblebelow theOption VBAsupport 1. - Save the document using ODF (as
.ods). - Relying on VBA compatibility use most recent versions of LibO. The compatibility layer is under ongoing development.
I don’t know what calculation time is needed after a change of arguments in the original environment (with Excel). Excel-VBA may be faster. On my old computer LibO 7.2.1.2 under Win 10 / Basic in compatible mode needed up to 30 s, but this only for dates in january or february. For all the other months -including december- it was much faster. I would not suspect LibO to be the culprit, but assume a flaw in the code supplied by the author of the tool.
The mentioned configuration does not rely on any Java for the task.
This is not about a replacement of a formula. The Basic code doesn’t work with formulas probably contained in cells of the passed range. However, it may rely on access to the the passed argument as a range object (A-Range) without need. LibO Basic without the compatibility options always passed only data. Of course, VBA also needs to accept data because parameter positions may get expressions (sub-formulas) as arguments. This “both-as-well-concept” isn’t supported by native LibO Basic for plausible reasons.
=== Editing ===
Just had a hunch and got back to this topic.
Sorry! I missed to take your quetion the literal way. Yes tzhe formula in B7 is replaced by its result if that’s 1 or 2. No clue so far.
The excuse I’m ready with: There are so many question hre not making any sense if taken literally,
Next night I may find the time to investigate the problem to a bit more depth.
=== Editing again. The part below was intended as an answer (solution/suggestion), but not accepted by this funny Q&A software. Therefore now appended to my comment: ===
Eventually having seen the issue clearly, I studied the code in a new light.
Since I have no Excel, I can’t tell if formulas in cells of the range B4:B10 of your example would persist a calculation there.
For the current treatment of VBA code by LibO, however, assignments made to parameters which passed a reference go to the referenced cells. The given code makes such assignments, in specific when running the body of the function calcJD() which is called in every case, and makes the striking distinction between the months 1 or 2 and all the others.
Remedy?
- You can omit the usage of formulas in the afflicted cells. Not actually recommended (partly for efficiency reasons.
- You can explicitly make the passed arguments in the Calc formulas values instead of references by replacing every reference with an expression actually requiring calculation. Writing
0+B4e.g in place ofB4and respectively would do the trick. Ugly, Q&D. Needing to be regarded on every change in the usage of the sheet. - You can explicitly make the code work with values in place of objects like demonstrated below for the first few lines of
calcJD. To do it only for this function may already suffice,but I didn’t analyze every possible case. Basically recommended. - You may trust in the astronomical expertise of the author, and give your credits, but rewrite the code completely for LibO. Clean and serious.
The announced piece of code:
Function calcJD(pYear, pMonth, pDay)
Dim year As Double, month As Double, day As Double
If IsObject(pYear) Then
year = pYear.Value
Else
year = pYear
End If
If IsObject(pMonth) Then
month = pMonth.Value
Else
month = pMonth
End If
If IsObject(pDay) Then
day = pDay.Value
Else
day = pDay
End If
REM Now following the main part of the body...You can download the the file from:
https://ecology.wa.gov/Research-Data/Data-resources/Models-spreadsheets/Modeling-the-environment/Models-tools-for-TMDLs
Expand the “Sunrise/Sunset” title to be able to see the download link. The page says all files on the page are downloadable for public use.
I also noticed that for January & February, the macro takes much longer to run. There is an if statement in the calcJD function that runs if the month is <=2 and further down there is another 2 if statements that run if month is either 13 or 14.
When the month is 1 or 2 the first if statement will run changing the values of the month and year while the second and third ifs will reverse this and eventually overwrite the contents of the month and year cells, which in the newly downloaded twiilight.xls would be B7 & B6. If the cells contain just simple numbers, then there is no problem but if the cells contain a formula or references to other cells, then the formulas and references are lost.
If I delete these 3 if statements in the calcJD, everything seems to run fine and the B6 & B7 cells remain untouched. I don’t know if the sunrise and sunset dates are accurate though. There is probably a reason why the author added those pieces of code but I don’t know what it is. I don’t understand the code between but looks like doing something with compensating for leap years.
I could run various dates on both the edited and unedited files with both Calc and Excel and see if sunrise and sunset times differ.
Thanks for your help Lupp, I pasted the code into the function and it works! As I suspected, my removing the if statements from the function resulted in inaccurate times for January and February. I have tested it with a variety of dates including leap days and I get identical results with Excel to the nearest millisecond.
I’ve copied the modified .xls file to .ods format and all is fine. Calc works with both the .xls and the .ods formats. Excel will work with the .xls file with your modifications as well. We even have a slight improvement: if you accidentally forget to enter the = before the formula, Excel will fail by displaying a full date and keying in the = or entering a plain number won’t recover and you need to exit without saving to recover. Calc will recover from this error if you enter a plain number and then you can enter the formula.
All up, satisfied is an understatement and I’d like to thank you for your help. If you wish, I can upload the .ods file here. You can then check that I’ve added the code exactly as you expected me to. You can even contact the Department of Ecology at the above link to suggest they add this newer version of the file to their website.
If I understood your question correctly, then this behavior is provided in Calc. But you can use Excel ranges in VBA support mode (Option VBASupport 1).
E.g.:
Option VBASupport 1
' Reference: Reference to the range. The range of VBA <ScVbaRange> used on a worksheet when called in code.
Function UDF(Reference [, ...])
If Reference.supportsService("ooo.vba.excel.Range") Then
oRange = Reference.CellRange
' Now you can work with the range, not with its value.
If it helps… I didn’t look at your example, but tried to answer the question as it is posed.