I don't want to go back to M$ Office !!!!!!!
Villeroy wrote:I don't want to go back to M$ Office !!!!!!!
Why not? If this praticular formatting feature is that important you should definitively go back to MS Office.
Edit: There are a couple of problems with Excel's "solution" to providing Engineering notation. The first is that it's undocumented. I don't know about the latest version of Excel, but in Excel 2003 there was no "Engineering" in the Format, Cells dialog. One had to know that it was possible to specify a user-defined format like ##0.00E+00 and that this meant that the user wanted engineering notation. But the Excel help doesn't say that anywhere that I can find. And using an undocumented feature always raises a red flag for me because if it's undocumented the vendor can change the behavior at any time. However one could argue that this particular vendor also changes documented features without warning, so what's the difference? The other problem with this "solution" is that it provides variable precision. In the format shown, numbers whose exponent is a multiple of three are displayed with three significant digits. But numbers with exponents of the form 3N+1 and 3N+2 are shown with four and five significant digits. (Try it and see.) I would think that most engineers and scientists would not think that using variable precision to display numbers is a real solution. The OOo technique using styles and conditional formatting provides the same number of significant digits for all exponents. |
- Code: Select all Expand viewCollapse view
MOD(INT(LOG10(CELL_TO_FORMAT));3)=1
- Code: Select all Expand viewCollapse view
MOD(INT(LOG10(ABS(CELL_TO_FORMAT)));3)=1
Sub createEngNotationConditionalFormatting
Dim oSelectionRange as Object, oSelectionConditions as Object
' Xray ThisComponent
oSelectionRange = ThisComponent.CurrentSelection ' Store Selection Range
oSelectionConditions = oSelectionRange.getPropertyValue("ConditionalFormat") ' Store Selection Conditions
oSelectionConditions.clear() 'Clear out all other conditional formating
' Xray oSelectionRange
Dim oNewCondition(2) as new com.sun.star.beans.PropertyValue ' Create New Property Value
' Add 1st Condition
oNewCondition(0).Name = "Operator"
oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA 'A formula will set this condition
oNewCondition(1).Name = "Formula1"
oNewCondition(1).Value = "MOD(INT(LOG10(ABS(A1)+(A1=0))),3)=0" 'Custom Formula
oNewCondition(2).Name = "StyleName"
oNewCondition(2).Value = "Eng5M0" 'Change to your Style name
oSelectionConditions.addNew(oNewCondition()) 'adds the conditional formatting
' Add 2nd Condition
oNewCondition(0).Name = "Operator"
oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA 'A formula will set this condition
oNewCondition(1).Name = "Formula1"
oNewCondition(1).Value = "MOD(INT(LOG10(ABS(A1)+(A1=0))),3)=1" 'Custom Formula
oNewCondition(2).Name = "StyleName"
oNewCondition(2).Value = "Eng5M1" 'Change to your Style name
oSelectionConditions.addNew(oNewCondition()) 'adds the conditional formatting
' Add 3rd Condition
oNewCondition(0).Name = "Operator"
oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA 'A formulat will set this condition
oNewCondition(1).Name = "Formula1"
oNewCondition(1).Value = "MOD(INT(LOG10(ABS(A1)+(A1=0))),3)=2" 'Custom Formula
oNewCondition(2).Name = "StyleName"
oNewCondition(2).Value = "Eng5M2" 'Change to your Style name
oSelectionConditions.addNew(oNewCondition()) 'adds the conditional formatting
' Update conditional formating
oSelectionRange.setPropertyValue("ConditionalFormat", oSelectionConditions)
oSelectionRange.setPropertyValue("ConditionalFormatLocal", oSelectionConditions)
End Sub
Edit: ... and it might fail when applied to a sheet other than sheet #1 (index 0) because the anchor of the relative reference is the first cell on the first sheet unless you adjust pseudo-property SourcePosition [c.s.s.sheet.CellAddress]. |
Users browsing this forum: No registered users and 24 guests