[Solved] Long Hand To Short Hand Converter Macro - UDF

[Endrophes]

Well I got myself in to a pickle with no idea how to get out. I been trying to write a Long hand to Short Hand converter. The primary purpose is to generate names for over 430+ variables. The problem, am still fairly new to OpenOffice (Am primarily a windows user) and I can't seem to get my head around my errors in OpenOffice Basic. I have altered and change my function multiple times that, there maybe more errors that are not showing, or the program gets stuck at one spot. I prefer using For loops and not For Each in this case.

I find this case funny because I have done this before in Microsoft Excel - VBA. So some of what I'm remembering might be seeping through into code.

Overview: It takes in a "target" and Splits it by the "delimiter"; Normally a space. Splitting the target to individual words and searches for them in LongHandWordBank, then replaces them with its shorthand version from shortHandWordBank. Coming to the result, It rebuilds the String by adding a "_" in-between each word.

Here Is what I have:

Code: Select all

Sub Main

End Sub

function ConvertToShortHand(target As String, delimiter As String, replace As String, LongHandWordBank as variant, shortHandWordBank as variant)

	Dim splut as Variant
	splut = Split(target, delimiter)
	SplutIndex = 0
	lengthLongHand = LEN(longHandIndex)
	

	For Each i in Splut()
		longHandIndex = -1
		longHandStepper = 0
		
		i = LOWER(i)
		
		For Each LongHandWord In LongHandWordBank()
			
			If LongHandWordBank(longHandStepper) = i Then
				longHandIndex = longHandStepper
				Exit For
			End If
			
			longHandStepper = longHandStepper + 1
			
		Next LongHandWord
		
		If longHandIndex <> -1 Then
			i = shortHandWordBank(longHandIndex)
		End If
		
		SplutIndex++
	Next i
	
	result = ""
	
	For Each i in Splut()
		result = result + i + "_"
	Next i
	
	result = Left(result, LEN(result)-1)
	
	ConvertToShortHand = result
	
End Function

The current error am getting is when it hits the "Next" of the inner For-Each Loop and Claims that it is "BASIC syntax error. Unexpected symbol: ." I have gone through the forms and none have seem to be applying to me.

Any pointers in the right direction will be helpful.

[Zizi64]

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.

[RPG]

The current error am getting is when it hits the "Next" of the inner For-Each Loop and Claims that it is "BASIC syntax error. Unexpected symbol: ." I have gone through the forms and none have seem to be applying to me.

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

[JeJe]

Replace is a reserved word you shouldn't be using for a variable name - its not used in the function anyway. I tried deleting and retyping Next LongHandWord and that solved that problem - whatever it was. I think you're mixing up languages with SplutIndex++, that's not basic, and was the next line to fail.

I'd do it something like this:

Code: Select all

Sub Main
	dim wds as new collection
	wds.add "fish","fishing"
	wds.add "abandon","abandonment"

	target = "abandonment fishing"

	w = split(target," ")
	on error resume next
	for i =0 to ubound(w)
		n = ""
		n= wds.item(w(i))
		if n<> "" then result = result & n
		if i < ubound(w) then result = result & "_"
	next
	msgbox result
End Sub

Edit: slightly modified to preserve any words that aren't in the longhand word list:

Code: Select all

Sub Main
   dim wds as new collection
   wds.add "fish","fishing"
   wds.add "abandon","abandonment"

   target = "abandonment fishing fancy apartment"

   w = split(target," ")
   on error resume next
   for i =0 to ubound(w)
      n = ""
      n= wds.item(w(i))
      if n<> "" then 
      result = result & n 
       else 
       result =result & w(i)
       end if
      if i < ubound(w) then result = result & "_"
      
   next
   
 
   msgbox result
End Sub

[Endrophes]

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

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)

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

[JeJe]

t = LCase(t)

[Zizi64]

Am not entirely sure on what "Sub Main" is for,

The "Sub Main" is the name of the default subroutine in the Module. When you create new StarBasic Module, the empty subroutine will be created automatically with this content:

Code: Select all

REM  *****  BASIC  *****

Sub Main

End Sub

.

You can write your code between the "Sub Main" and "End Sub" lines, or you can write another subroutine or function.