[I’m assuming here that you have limited programming experience, just from what you say…please forgive me if it sounds a little patronizing because I underestimated your experience level…]
The error is because you do not delimit arguments with “;” in BASIC, you use “,”. So the ; after …phone" is the error. But that is part of a much larger concern. Read on.
This error is really based on what mikekaganski is saying, which is that you cannot use Calc functions inside macros directly. Meanwhile Villeroy is saying that you don’t even need a macro for this, since you can just use the REGEX Calc function in the sheet itself.
To use REGEX inside a macro (there might be reasons for this), you would first create a FunctionAccess service object then call the callFunction method on that object. Yeah, if you are a beginner, it sounds like gobbledygook.
The upstart, though is that before your B1=REGEX... line, you would get rid of the dim dispatcher as Object and dispatcher createUnoService... (unless you need that for something else, which with luck you don’t); then you would add in
Dim oService As Object
oService = createUnoService("com.sun.star.sheet.FunctionAccess")
instead. In short, FunctionAccess is a service you need here, not Dispatcher.
Then instead of your B1=... line, you would have
B1 = oService.callFunction("REGEX", Array("+7913-256-5891 phone", "-", "", "g"))
What you think of as the ‘hard’ function REGEX becomes a ‘soft’ text string that is the first part of a request to use that function, and the arguments (parameters) for the REGEX function are then passed to callFunction as a single array, which is built in BASIC by using Array(…). You could stuff that array with the exact same arguments Villeroy gives you in answer, namely, the “\D”, etc. But you would separate them with commas, not semicolons, to avoid the error you are getting!
If you ever have to use Dispatcher in a macro then you should shed a tear or two. While the macro recorder uses it all the time, it is only used for tricky things that simulate user interaction in handwritten macros…like copy/paste scenarios, etc. In fact, it is often downright worthless, since, say, it will start up a dialog box for settings but then have no way to actually toggle the settings. Instead, those settings are done in a handwritten macro by directly understanding the object model (chunks of data) that the dialog box is meant to handle then setting attributes on that object model directly–or, almost directly through small data chunks called structs. More gobbledygook!
