LibreOffice Calc macro to select specific cells, find regular expressions and replace

I cut and pasted the macro code into my macros. It records both parts as separate modules (maybe because there are two “Sub” / “End Sub” parts? (I am guessing because I have no idea).

How do I combine into a single module?

I just tried to run the replaceTextWithNumbers() procedure and I still get “BASIC runtime error. Argument is not optional” with this line highlighted:
oRDescriptor = aRange.createReplaceDescriptor()

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:

  1. On the same selection as above, align centre
  2. On the same selection as above, format as a number to 2 decimal places
  3. For selection ( a1:a71), (p1:p71), (ae1:ae71) and (at1:at71), which are all text fields, remove any spacing before the text begins (for some strange reason, when I copy from HTML source and paste into Calc some of the fields have 3 or 4 spaces before the text which messes up my vlookups

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
image

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?