Page 1 of 1

[Solved] ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 3:47 pm
by mpcengineering
Hi guys,

I have a bit of a wierd problem with a spreadsheet I'm building. i am saving it in excel 97/2000/XP format for compatibility with my colleagues, and whenever I re-open it, many of the cells with any calculations occurring are displayed as ### or #MACRO?. These cells typically contain something similar to this:

=$'Bay 101'.$F$48+IF(CURRENT()>0;STYLE(VLOOKUP(CURRENT();$G$4:$H$11;2;1));STYLE("Red0"))

The strange thing is, if you alter this line in anyway, and then undo it. so it is exactly as the original line was and ok it, suddenly it comes good and the value is again correct (selecting the cell displays the contents correctly in the formula box). The page also displays 'Error: Macro not found' in the bottom right corner but there are no macros referred to on the page, only styles.

Saving the file is the .ods format results in the spreadsheet being opened correctly every time, does anyone know if there is a way around this problem?

Thanks in advance


Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 4:02 pm
by Hagar Delest
Can you upload here a sample file? It will spare time to those who want to test your situation.
NB: thanks to have created another thread instead of posting in your previous one!

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 5:44 pm
by mpcengineering
Hi Hagar,

Thanks for the reply, I considered attaching the spreadsheet but it is nearly 100 sheets in total and therefore a fairly large file size. However I have created a cut down version with less pages that still demonstrates the same thing. You will hopefully see that as you open the file, many of the cells on the first sheet are populated with ### or #MACRO?, until you make and change at which point the correct result is again entered. As you enter values on the other sheets in the 'power' and 'weight' columns, this in turn populates the first sheet with a summary of the results for those entries. The columns with the more complex entries seem to fail to resolve on opening the spreadsheet, but for the most part the rest seem fine.

I thought it was better to start a new thread as the issue from my last post was resolved, better to start a new thread per issue don't ya think?

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 5:58 pm
by Villeroy
As far as I know, Excel does not support functions STYLE nor CURRENT so it makes no sense to save as xls.
=$'Bay 101'.$F$48+IF(CURRENT()>0;STYLE(VLOOKUP(CURRENT();$G$4:$H$11;2;1));STYLE("Red0"))
can be rewritten as
Code: Select all   Expand viewCollapse view
=$'Bay 101'.$F$48+STYLE(IF(CURRENT()>0;VLOOKUP(CURRENT();$G$4:$H$11;2;1));"Red0"))
=$'Bay 101'.$F$48+STYLE(IF($'Bay 101'.$F$48>0;VLOOKUP($'Bay 101'.$F$48;$G$4:$H$11;2;1));"Red0"))

It would be easier to include a row
0 Red0
into the lookup range and use the simplier
Code: Select all   Expand viewCollapse view
=$'Bay 101'.$F$48+STYLE(VLOOKUP(CURRENT();$G$4:$H$12;2;1))
=$'Bay 101'.$F$48+STYLE(VLOOKUP($'Bay 101'.$F$48;$G$4:$H$12;2;1))

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 6:38 pm
by mpcengineering
Ah ok, thats explains that then. Is there any way in which I can make the spreadsheet compatible with both excel and openoffice? The majority of my colleagues use excel and will definitely need to have access to the spreadsheet, so I guess the question is, does openoffice support the excel equivalent of these functions thus allowing me to save in .xls format?

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 6:58 pm
by Villeroy
Excel falls short when it comes to conditional formatting. There is not much we can do about it. Normally it is the other way round, and we need to find a workaround in Calc for things that use to work in Excel.
You may try the procedure I described in another thread about a unusual macro of mine:
[Calc] More than 3 conditional formats
The trick is: You create a temporary named range "readCF" as substitute for dialog "Conditional formatting", then you select the cells to be formatted and run the installed macro to read the conditional formats from "readCF" and apply them to the selected cells. After this procedure, neither the helper range nor the macro is required anymore as long as you do not touch the conditional formattings through the built-in dialog (Excel '97 crashes, Calc resets to 3 conditions).
Your colleagues will be very impressed ;)

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 7:50 pm
by mpcengineering
I think it's time for me to educate my colleagues in the error of their ways using excel over openoffice ;)

Thanks for the pointer to the workaround villeroy, I will give that a go if I have time, but I only have a week to finish this off now and there is a significant amount of work still to do on it.


Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Wed Jun 25, 2008 8:21 pm
by Villeroy
Just extract the Python code in the zip archive to the suggested python-directory in your user profile (max 2 minutes).
You deal with 9 conditional formats (including the "red0"). Get a free sheet, select a range of 9rows x 4 columns and type "readCF" into the name box left of the formula bar.
First column gets the style names, second column gets operators "<=", 3rd column the threashold (the first column of your current lookup range), 4th column remains empty (2 minutes).
Named range readCF with 4 columns and 9 rows. I assumed style names "style1" to "style9" and simple integer threasholds:
Code: Select all   Expand viewCollapse view
style1   <=   0   <empty>
style2   <=   1   <empty>
style3   <=   2   <empty>
style4   <=   3   <empty>
style5   <=   4   <empty>
style6   <=   5   <empty>
style7   <=   6   <empty>
style8   <=   7   <empty>
style9   <=   8   <empty>

Now your cell simply refers to =$'Bay 101'.$F$48 without STYLE function. Select it and call
menu:Tools>Macros>Organize>Python... MyMacros>ConditionalFormats>read_ConditionalFormats

This will apply 9 conditional formattings to the selected cell(s). Both applications Excel and Calc support that many conditional formats, although their dialogs (menu:Format>Conditional...) are limited to three of them.
You can apply the settings to many selected ranges at once. Once the cell(s) have their extended conditional formatting, the conditional formatting is permanently set. You are free to remove the named range. I would keep it hidden for later reference.
I tested this with Excel files in Excel'97 and someone else confirmed 10 conditional formats working with Excel2000. BUT. Excel users should not call menu:Format>Conditional... while one of the special cells is selected since this would crash Excel. In Calc you can call that dialog without crash, but you should cancel it, otherwise you lose exceeding conditional formats.

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Thu Jun 26, 2008 10:28 am
by mpcengineering
Ok you twisted my arm :) It works great (opens fine in excel as a .xls) and colours the cells correctly, with only one further problem remaining...I hope. I need to have 2 different ranges with 2 different thresholds (ie a 2nd readCF range), one is applied to the 'cooling' column in my example spreadsheet, and the other to the 'weight' column. Is this possible?

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Thu Jun 26, 2008 11:45 am
by Villeroy
:) Thank you for testing! Apart from my own tests I had no feedback until now. Make a backup copy of your current "readCF" (if you like) and overwrite "readCF". Or relocate "readCF" to another set of 4 columns and X rows. "readCF" can have more rows than needed. The macro skips all rows without operator in second column.
How to relocate a named range:
Insert>Names>Define...(or Ctrl+F3)
Pick the name from the list
Change it's address (my macros need absolute addresses $Sheet.$A$1:$D$9 with all 5$)
Now you may change other names and [Modify]

Then select your cell(s) and call macro "read_ConditionalFormats". Just in case you deleted all your helper ranges: There is another macro "write_ConditionalFormats" which writes the active cell's conditional formattings to a named cell "writeCF". Another helper prints a list of all defined style names to the active cell.

To avoid any trouble with Excel's and Calc's built-in dialog, you can protect your conditional formattings:
Remove the "Protected" flag from all required input cells (Format>Cells... tab:Protection) that need to remain editable, keep the protection flag for all conditionally formatted cells. Then protect the sheet (Tools>Protect>Sheet with optional password). This disables the conditional format dialog in Calc and should do the same in Excel. Oh, don't forget to remove the protection when you need to change something. With protection the macro will fail silently, simply doing nothing without message.

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Thu Jun 26, 2008 12:41 pm
by mpcengineering
Hi Villeroy, no problems, its actually a very good solution to this and more elegant than having a box with a massive great line of code in it. A couple of minor thoughts on the method: You might want to make a note than once a match is found within the readCF range, none of the other cells are processed in the range for a match. This became apparent because of the way I originally set up the range , with the thresholds increasing in value from 0 as the rows went down, so something like =0, >0, >=75, >=150. In this case, any value above 0 results in the style relating to the >0 threshold being applied, even if it is >= 75 or >= 150, because the >0 criteria is fulfilled first. I think the code is right here, and the solution is just to reverse the order, (so >=150, >=75, >0, =0) but it might be worth making a note of. Also the between option <=>, how does this look at the values? So if it was <=> 75 150 for instance, does this look for > 75 and < 150 in which case, if there was <=> 150 225, then a value of exactly 150 would not result in a match. Or is it something like >75 and <=150?

I may have the wrong end of the stick, but it seems to me that you are suggesting I extend the current readCF range and add more values into it? I should have been clearer in what the problem is I think! Basically, the 2 ranges have thresholds that overlap in terms of value, so the first has a range of 0 to 525 with 8 styles spread evenly over that range, and other has a range of 0 to 35000, with with the 8 styles spread evenly over this range. So depending on how the readCF range is built, values in either column would only be subject to one of the ranges, never reaching the second range i.e.

Code: Select all   Expand viewCollapse view
Red8      >=    525
Red7      >=    450
Red6      >=    375
Red5      >=    300
Red4      >=    225
Red3      >=    150
Red2      >=    75
Red1      >    0
Red0      =    0
Red8      >=    35000
Red7      >=    30000
Red6      >=    25000
Red5      >=    20000
Red4      >=    15000
Red3      >=    10000
Red2      >=    5000
Red1      >    0
Red0      =    0

In this case, the 2nd range would never be reached, an option would be to simply have another script that looked for 'readCF2' or similar thus a completely separate range could be created. I could do a simple find a replace of readCF for readCF2 in your python script, but I dont want to fiddle your code without permission :o

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Thu Jun 26, 2008 1:32 pm
by Villeroy
Thank you for the suggestion about the evaluation order of conditions. This works exactly like in the original dialog. Nevertheless, I'll add a note to the macro's topic.
The between and not_between operators allow for disjunct or overlapping thresholds:
Cell value between 1 and 2 -> Style1
Cell value between 3 and 4 -> Style2
Cell value between 3.5 and 5 -> Style3
and also formula expressions like these simple ones ...
Cell value between MIN($A$1:$A$99) and MAX($A$1:$A$99) -> "Style3"

You can always use formulas instead of constant values. The additional f(x) operator allows for conditions that are unrelated to the active cell's value (in the dialog you switch from "Cell value is" to "Formula is"). The active cell takes all the settings when you hit "OK" in the built-in dialog or when you run the macro. Relative references expand to the other selected cells in the same way as normal formulas do.
StyleX f(x) $A1>0 (with relative row) applied to active cell B1 means: "Apply StyleX if the value in this row's column A is greater than 0".
The following settings are equivalent when applied to active cell A1:
StyleX f(x) A1>0 [A1 is identical with the active cell]
StyleX > 0 [this cell's value > 0]
Again, all this applies to the built-in dialog in Calc and Excel as well. Should I write tutorial on conditional formatting?

No, I don't suggest to extend the current "readCF" range. Erase it's content, which is not at all linked to the previously formatted cells. Before erasing you may want to copy the content to another place. Copy rather than cut, since cut&paste would move the named range itself. If the current "readCF" is too small, then insert cells (select rows in "readCF", menu:Insert Cells...move:Down) or relocate "readCF", let's say from A1:D9 to A1:D12, to G5:J16 or to another sheet. It's completely up to you how and where you create a named range "readCF", 4 columns wide and tall enough to take the conditional format settings for the next run of macro "read_ConditionalFormats". If your current "readCF" is larger than needed, it's also OK to leave some empty rows.
Then fill "readCF" with new content, select the cells to be formatted and call the macro again. The macro reads settings from "readCF" just like OOo reads from the dialog when you push the dialog's OK-button.

Oh, I forgot another feature. If you have some extended conditional formatting in use and you want to apply the same settings elsewhere, simply copy the cell. No macro, nor "readCF" required. Should work in Excel too.

Re: ### and #MACRO? on opening .xls spreadsheet

PostPosted: Thu Jun 26, 2008 1:53 pm
by mpcengineering
Thanks for the info on how the between and not between operators work.

Ok, got it now, I was stumbling on that fact that the conditional formatting was saved in the cell to which it is applied and therefore the readCF range is no longer required on the separate sheet (yes I know, you already said that, but I'm a bit slow :P ). So I have now created a new range, moved readCF to cover this new range and reapplied the macro over the other cells, works very well. I will mark this thread as solved, thanks for your assistance once again.