Macro for Arrays

[HydraheadHunter]

So, I have this macro repeatedCharactersRemoved(string) which was crafted by Lupp

Function repeatedCharactersRemoved(pString As String) As String
Dim out As String, w_char As String
out     = ""
Do While Len(pString)>0
 w_char  = Left(pString, 1)
 pString = Join(Split(pString, w_char), "")
 out     = out & w_char
Loop
repeatedCharactersRemoved = out
End Function

Works a charm, but don’t wanna keep click-dragging it down the whole column. but, I wanna just put it in one cell with an array for its reference, and ctrl+shift+enter it to run the whole array. Doing that currently gets me

BASIC runtime error. Object variable not set.

I looked for but couldn’t find any tutorials on how to make a macro ‘array-friendly’. Is there one I missed?

[HydraheadHunter]

Obviously, I need to change the string argument to an array argument, do a for loop over the data writing each result to a new array, and then output that array, but how syntax? I don’t speak basic.

[karolus]

enter for example directly right of A1 into B1 the Formula:

=TEXTJOIN("";0;UNIQUE(MID(A1;SEQUENCE(LEN(A1));1)))

and double-click the tiny square on the right-bottom-corner of B1 … voila!

keep in mind UNIQUE and SEQUENCE exists since LO24.8

[fpy]

@HydraheadHunter

missed

ti’s actually twofold :

parameter

• Macros/Basic/Calc/Ranges - The Document Foundation Wiki
• How to iterate through a range of cells in a Basic macro?

result

• Function that returns an array values

---

so for example, with the (strong) assumption of 1-colum range input :

Function iterRepCharRm(col As range)
  Dim result(1 to UBound(col,1), 0) as String

  For Each v In col
  	i = i + 1
    result(i, 0) = repeatedCharactersRemoved(v)
    Next v
    iterRepCharRm = result
End function

---

[HydraheadHunter]

Thank you very much. I’ll have a read through these after I’ve finished my mac and cheese.

[Lupp]

BTW: Avoid formulas with output to an array (locking a range) where single-cell formuilas can do the job.

[HydraheadHunter]

Single cell formulas technically could do the job, but like: at 60k items in the spreedsheet, I ain’t wanna spend twenty minutes draggin’ my cursor all the way down there, ya.

[karolus]

@HydraheadHunter

I ain’t wanna spend twenty minutes draggin’ my cursor

did you already read and understand what I wrote 2 hours ago?
https://ask.libreoffice.org/t/macro-for-arrays/125610/3?u=karolus

[HydraheadHunter]

I read it. Didn’t understand it because I was running a verson of LO so old it didn’t have the features you were talking about. Updated. Thanks for pointing the fancy new stuff out to me.