Convert Excel .xla to Libre Office Calc Add-in

English
,
#1

I do a lot of electronics and needed a viable engineering notation. I understand Calc can do powers of 1000.

Never did figure out how to allow Excel to read back the formatted value, though

But, I want more. mine allows for appending a string. Example calculated value for a capacitor in a tuned circuit requires 100.000 pFd or 100.000 pico Farad = Denum2EngrStr(value/formula, # Decimal places, n/str, type)

=Denum2EngrStr(100, 3, “S”, “Farad”) → 100.000 pFarad

When Win 10 dies I will be moving to Linux and don’t want to re-buy all my MS Office. It would be really nice to make the transition with my own code in a format that works in Calc

#2

Show the your code of: Denum2EngrStr

#3

Can’t you do it only with cell format.
#.##0".000 pFarad"
so you don’t need to modify the value.
For values lower than 1 Farad, or other cases, you can use conditions on the format.
Conditions on cell format

#4

What version of LibreOffice are you using? Bug tdf#153993 has been fixed for 24.2, does it meet your needs?

#5

The total package is over six hundred lines, probably looks like spaghetti to a “real” programmer.

It has a lot of added stuff to over come the limitation Excel has for converting double numbers to HEX or Binary and back.

Mariosv pointed out there may be an internal formatting that will work.

I have two modules, and it’s been so long I don’t honestly remember all the ins and outs.

https://houseofmyrrh.org/Module1.bas & https://houseofmyrrh.org/Module11.bas

#6

Currently I have installed 7.5.0.3 Win 10 is still getting updates so I haven’t been forced to learn new things, I will look at the formatting options you indicated.

Just trying to lay ground work for the changeover.

#7

In my case the assignment of nano pico milli Kilo Mega and Tera are automatic based on the double value, which is why it takes so much code.

The advantage of both Excel and Calc format code is that once created the cell value is reuseable in my code it is not as it appears as pure text.

#11

Faraday.ods (31.0 KB)

#9

Your functions will work in Calc, perhaps with minimal modifications.
Look at the attached example.
A Module1 module has been created in Standard library of the document, in which your code (VBA) is written. Removed Attribute statements and added a statement at the beginning

Option VbaSupport 1

In cell A1 there is a formula using your function.
If you want to use your functions in the formulas of several documents, then you need to put macros to the application’s Standard library (My macros…)

TestDnumEng.ods (9.9 KB)

#10

… another solutuion would make more use of the already existing capabilities in terms of formatting:


function FormatMega(value as Double, nDecimals as long) as String
	oNF = createUnoService( "com.sun.star.util.NumberFormatter")
	oNF.attachNumberFormatsSupplier(ThisComponent)

	oLocale = new com.sun.star.lang.Locale

	nKey = oNF.NumberFormatsSupplier.NumberFormats.getStandardFormat(com.sun.star.util.NumberFormat.SCIENTIFIC, oLocale) 
	sFormat = oNF.NumberFormatsSupplier.NumberFormats.generateFormat (nKey, oLocale, true, false, nDecimals, 1)
	sText1 = oNF.convertNumberToPreviewString( sFormat, value, oLocale, true ) 
	
	nPosE = InStr(sText1, "E")
	if nPosE = 0 then
		FormatMega = "E not found"
		exit function
	endif
	
	sFirstPart = Mid(sText1, 1, nPosE-1)
	ePwr = CInt(Mid(sText1, nPosE+1, 255))
	
	Select Case ePwr
        Case -18
            enPwr = "atto": enSym = "a"
        Case -15
            enPwr = "femto": enSym = "f"
        Case -12
            enPwr = "pico": enSym = "p"
        Case -9
            enPwr = "nano": enSym = "n"
        Case -6
            enPwr = "micro": enSym = "µ"
        Case -3
            enPwr = "milli": enSym = "m"
        'Case -2
        '    enPwr = "centi": enSym = "c"
        'Case 2
        '    If sa = 0 Then enPwr = "Hecto": enSym = "H"
        Case 3
            enPwr = "Kilo": enSym = "K"
        Case 6
            enPwr = "Mega": enSym = "M"
        Case 9
            enPwr = "Giga": enSym = "G"
        Case 12
            enPwr = "Tera": enSym = "T"
        Case 15
            enPwr = "Peta": enSym = "P"
        Case 18
            enPwr = "Exa": enSym = "E"
        Case Else
            If ePwr >= 0 Then
                enPwr = "E+" + LTrim$(Str$(ePwr))
            Else
                enPwr = "E" + LTrim$(Str$(ePwr))
            End If
    End Select	

	FormatMega = sFirstPart & " " & enPwr
End function

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