Please try pressing the button in this spreadsheet - sample spreadsheet with macro.ods (40.5 KB)
I took the liberty of changing your search string a bit - now it’s just “find everything in parentheses and replace with whatever you find with a minus sign”. Hope this doesn’t corrupt your data.
Ok, this is super wierd
1/ The “Clear Data” button that you introduced doesn’t seem to work
2/ If I go Tools>Macros>Run Macros and try to run your macro then that doesn’t work either
3/ If I go Tools>Macros>Organise Macros>Basic and try to run your macro then it DOES work!
I don’t understand
The macro clearly functions, but will only run properly if I do (3) above, not if I do (1) or (2)
Any ideas?
Macro Security Level?
Choose Tools - Options - LibreOffice - Security
Check macro security level or add folder with this file to Trusted file locations
No, that didn’t help. I changed the security level to “low” and I added the folder with this file to Trusted file locations.
Same thing. (1) and (2) don’t work, but (3) works just fine
Do you have any other ideas how I can fix this?
I feel that we are so very close to a solution
thank you in advance for all of your help
Okay, now we have to get back to the question we should have figured out from the very beginning: what is your operating system, LibreOffice version?
System:
Kernel: 5.15.0-52-generic x86_64
bits: 64
compiler: N/A
Desktop: Cinnamon 5.2.7
Distro: Linux Mint 20.3 Una
base: Ubuntu 20.04 focal
LibreOffice: Version: 6.4.7.2
Build ID: 1:6.4.7-0ubuntu0.20.04.6
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3;
Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB
Calc: threaded
Understood. We need to call for help from colleagues who know the nuances of “Linux - macros” (I don’t know, sorry)
@JohnSUN , thank you for your help so far.
Could I ask one more favour please?
If I wanted to add some functionality to the macro that you have written for me above, would you be able to help me with that please?
Other functionality is quite straight forward:
Thank you in advance
(Stepping in late: )
To move forward, I need a meaningful example of the actual source of the data (where the Copy step of Copy/Paste is done). If the type of source varies, then a good example for each type. The source can be an html page from the web (given by the URL), an html file, or some other kind of file… It must be original, not just described in some way.
BTW: The misuse of parentheses in the notation of negative numbers has a tradition “somewhere”, and I have encountered it before. I even suspect that this sin is behind the nonsense of falling back on the amount of the number to be represented when giving number formats that contain an extra alternative (after the first semicolon) for negative numbers.
The source of the data is a subscription service (for which I am a subscriber). suffice it to say that the formatting is always the same on the input data being cut and pasted into Calc.
The Macro that @JohnSUN wrote works fine, but it only works if run in one out of three methods:
1/ The “Clear Data” button that he introduced doesn’t seem to work for me. I can press it, and its properties are linked to the execution of the macro, but it doesn’t do anything when pressed.
2/ If I go Tools>Macros>Run Macros and try to run your macro then that doesn’t work either
3/ If I go Tools>Macros>Organise Macros>Basic and try to run your macro then it DOES work!
So nothing wrong with the Macro, we are just trying to work out why methods (1) and (2) don’t work when (3) does work
Any ideas?
No.
@JohnSUN is surely perfect in things like this one, and if there is a flaw in what he told you or designed for you, he will be the best one to find it.
I stepped in to try an independent approach, and for that I would need independent input. My motivation in this special case isn’t exclusively to help you, but also to better understand what’s going on in the networked world.
OK, won’t work.
Update, now the Macro isn’t running in any of the three methods described above. It did yesterday. But not today. No idea what changed.
I get “BASIC runtime error, Argument is not optional.” with this line of code highlighted:
oRDescriptor = aRange.createReplaceDescriptor()Yes, these are not complex transformations at all, the size of the macro has increased slightly, mainly due to the quote from Pitonyak’s book, which I mentioned in the very first comment. The whole module might look like this:
REM ***** BASIC *****
Option Explicit
Dim moneyFormat As Integer, percentFormat As Integer
Sub replaceTextWithNumbers()
Const SHEET_NAME = "Extended TIKR"
Dim oSheets As Variant, oSheet As Variant
oSheets = ThisComponent.getSheets()
If Not oSheets.hasByName(SHEET_NAME) Then Exit Sub
oSheet = oSheets.getByName(SHEET_NAME)
moneyFormat = FindCreateNumberFormatStyle("0.00")
percentFormat = FindCreateNumberFormatStyle("0.00%")
replaceInRange(oSheet.getCellRangeByName("B3:N71"))
replaceInRange(oSheet.getCellRangeByName("Q3:AC71"))
replaceInRange(oSheet.getCellRangeByName("AF3:AR71"))
replaceInRange(oSheet.getCellRangeByName("AU3:CQ71"))
trimWhitespace(oSheet.getCellRangeByName("A1:A71"))
trimWhitespace(oSheet.getCellRangeByName("P1:P71"))
trimWhitespace(oSheet.getCellRangeByName("AE1:AE71"))
trimWhitespace(oSheet.getCellRangeByName("AT1:AT71"))
End Sub
Sub replaceInRange(aRange As Variant)
Dim oRDescriptor As Variant, percentCells As Variant
Dim oldBkColor As Long, i As Long
oldBkColor = aRange.getCellByPosition(0, 0).CellBackColor
aRange.clearContents(com.sun.star.sheet.CellFlags.HARDATTR)
oRDescriptor = aRange.createReplaceDescriptor()
oRDescriptor.SearchRegularExpression=True
oRDescriptor.SearchWords = False
oRDescriptor.setSearchString("\((.*)\)")
oRDescriptor.setReplaceString("-$1")
aRange.replaceAll(oRDescriptor)
oRDescriptor.setSearchString(".+")
oRDescriptor.setReplaceString("&")
aRange.replaceAll(oRDescriptor)
oRDescriptor.setSearchString("%")
percentCells = aRange.findAll(oRDescriptor)
aRange.NumberFormat = moneyFormat
If Not IsNull(percentCells) Then
For i = 0 To percentCells.getCount()-1
percentCells.getByIndex(i).NumberFormat = percentFormat
Next i
EndIf
aRange.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
aRange.CellBackColor = oldBkColor
End Sub
Sub trimWhitespace(aRange As Variant)
Dim oRDescriptor As Variant
oRDescriptor = aRange.createReplaceDescriptor()
oRDescriptor.SearchRegularExpression=True
oRDescriptor.SearchWords = False
oRDescriptor.setSearchString("^\s*")
oRDescriptor.setReplaceString("")
aRange.replaceAll(oRDescriptor)
oRDescriptor.setSearchString("\s*$")
aRange.replaceAll(oRDescriptor)
End Sub
Rem @Author: Andrew Pitonyak from Listing 5.38: Create a number format style.
Function FindCreateNumberFormatStyle (_
sFormat As String, Optional doc, Optional locale)
Dim oDoc As Object
Dim aLocale As New com.sun.star.lang.Locale
Dim oFormats As Object
Dim formatNum As Integer
oDoc = IIf(IsMissing(doc), ThisComponent, doc)
oFormats = oDoc.getNumberFormats()
'If you choose to query on types, you need to use the type
'com.sun.star.util.NumberFormat.DATE
'I could set the locale from values stored at
'http://www.ics.uci.edu/pub/ietf/http/related/iso639.txt
'http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html
'I use a NULL locale and let it use what ever it likes.
'First, see if the number format exists
If ( Not IsMissing(locale)) Then
aLocale = locale
End If
formatNum = oFormats.queryKey (sFormat, aLocale, TRUE)
' MsgBox "Current Format number is" & formatNum
'If the number format does not exist then add it
If (formatNum = -1) Then
formatNum = oFormats.addNew(sFormat, aLocale)
If (formatNum = -1) Then formatNum = 0
' MsgBox "new Format number is " & formatNum
End If
FindCreateNumberFormatStyle = formatNum
End Function
I thought it would be better if we removed spaces and line breaks (it turns out there were cells with such values!) not only at the beginning of the line, but also at the end. I hope your VLOOKUP() does not contain search strings with spaces at the end?
But in any case, the behavior of your Calc is surprising - the fact that the macro is still run in the third case is confusing. Honestly, this is the first time I’ve encountered such an error!
Update. O! So it stopped running! Well, that’s more natural behavior for the program… Try restarting it in safe mode.
Oh dear!
I thought I ought to update my LibreOffice. So I uninstalled the prior older version that I was running and I have installed version 7.4.2 (latest version).
I hoped that would fix the issue.
But now when I run the Macro I get this error on method (2):
“A scripting framework error occured while running Basic script Standard.Module1.FormatDataInSelectedFields. Message: wrong number of parameters! at /build/libreoffice-clbuE0/-libreoffice-7.4.2~rc3/scripting/source/basprov/basscript.cxx:192”
Whatever that means
I get the same error as before on method (1)
InSelectedFields? We did not have procedures with that name!
This is what I now get with the new Macro
Can you take a screenshot of this window along with the error message? I have to see it with my own eyes
Ok, back to where we were yesterday… I lowered the security again and it only works with this method:
Tools > Macros > Organise Macros > Basic > {select Macro} > Run
When I do:
Tools > Macros > Run Macro …
This is what I get
Ha! Ha ha! I think I’m starting to guess the reason… Is this for the code you copied from the forum post and pasted into the module via the clipboard?
yes. Was that wrong?