EUREKA!!!! Thank you for Your quick responses. All your comments helped me a lot. In the end I need to go to bed and come back at this with a fresh head. Data Entry tasks are not for me and I was at this document for hours doing other things.
From the top
Zizi64 wrote:Please upload a real ODF tyípe sample file here with the embedded macro code, and with some input parameters and a calling of the function. Please comment your sample in the file: what is the expected result.
Did not think it was need, but looking back that might helped a lot. I will see about uploading a simpler ODF then this one. Here is an example of what I mean.
Word banks EX: I would be passing in ranges for these variables
LongHandWordBank: property, installation, description, control, information
shortHandWordBank: PROP, INSTI, DESC, CONTR, INFO
EX of Conversion:
Target => Output
Property information => PROP_INFO
EX function call: =CONVERTTOSHORTHAND(B2;" ";"_";WordBank.A2:A369;WordBank.F2:F369)
RPG wrote:Copy your code and paste it in Notepad. Then you can see some characters who give the error. Copy it back from Notepad to the IDE. Maybe you can type in the line again. I see the characters between Next and Longhandword.
Romke
A simple debugging trick, provided your using notepad is not known for converting your text to something no other editor can read.
As I was reading your comment I realized that the editor that I used before had a case of adding characters that were not supported in other editors. My bad.
I followed JeJe's steps and found out that problem
I tried deleting and retyping Next LongHandWord and that solved that problem - whatever it was.
Both JeJe & Romke Nailed it.
Continuing on with JeJe's example. Am not entirely sure on what "Sub Main" is for, but the logic still works in a function. (am more used to being in a function) However, because am passing in two, "2D Arrays" I needed a nested loop. Much hammering, debugging, and testing to under stand the API. It lives!!!!!!
Things to note for who come after me:
* The Range that is passed in is
1 INDEXED not zero.
*
For me: I needed to have both Row and Column to access the passed in Array, even if I have selected one column
* If you get an unexpected Symbol error and is shows " . " try deleting the line and retyping it. And replace the Notepad that your using while your at it
The last problem I have is calling LOWER with in my Macro. After following other examples, am not sure what am missing.
Code: Select all
t = w(A)
srv = createUnoService("com.sun.star.sheet.FunctionAccess")
t = srv.CallFunction("Lower", t)
New Code:
Code: Select all
function ConvertToShortHand(target As String, delimiter As String, replace As String, LongHandWordBank as variant, shortHandWordBank as variant)
Dim w as Variant
srv = createUnoService("com.sun.star.sheet.FunctionAccess")
w = Split(target, delimiter)
on error resume next
For A = 1 To ubound(w)
t = w(A)
t = srv.CallFunction("Lower", t)
lhw = ""
shw = ""
For B = 1 To ubound(LongHandWordBank)
lhw = LongHandWordBank(B,1)
if lhw = t then
shw = shortHandWordBank(B,1)
Exit for
end if
Next B
if shw <> "" then
result = result & shw
else
result = result & t
end if
if A < ubound(w) then result = result & "_"
Next A
ConvertToShortHand = result
End Function