Vis Basic does not recognise Substitute function

[Bordonbert]

I’m a very experienced C++ coder so principles are fine, but I’m just starting to try to get into Vis Basic in Calc and fine details are not clear by a long chalk. I’m sure this is an easy fix for those who know VB but I have searched for hours to find out how to sort it out and can’t find anything relevant.

I’m writing my first function to simply explore how things work for a more ambitious project later. At the moment I’m exploring searching within a string and replacing characters. I have a problem very early on in the code where I call Calc’s Substitute( , , , ) function. It reacts as though SUBSTITUTE doesn’t exist and I don’t know where to go from there. The current code up to the error point is:

Function TestSetFuncs()

	Dim TestValue, NewValue As String
	Dim Position As Integer
	
	TestValue = "4R7"
	
	Position = Instr(TestValue, "R")
	
	Substitute(TestValue, "R", ".")

End Function

Everything up to just after the Instr call is fine, the “R” is found and Position is passed the value 2, but Substitute blows up every time I try to step into it with error: “BASIC runtime error. Sub-procedure or function procedure not defined.” I must be doing something ridiculously simply wrong but I can’t for the life of me find it. Can anyone enlighten me please?

[Steph1]

Maybe because Substitute doesn’t exist in Ooo Basic. Try replace:
https://help.libreoffice.org/latest/en-US/text/sbasic/shared/replace.html

[Zizi64]

@Bordonbert

Vis Basic in Calc

Do you mean the StarBasic? The SUBSTITUTE() is a Calc Cell function, but not a Basic function. It is more difficult to call the Cell functions from StarBasic, but is possible.

[Bordonbert]

As they say where I come from, “Oh buggier!” It was as simple as that.

I assumed that because the background Calc recognised Substitute its own VB would too. Yes, Replace worked a treat. Embarrassingly, I actually had tried Replace before but had used it incorrectly assuming it would change its first parameter directly. When I passed its result back to another variable it works fine.

Thanks so much Steph. I knew it was a silly newbie error. It’s good to keep a “beginner’s mind”, and a little humility to match. I’m sure I’ll be back!