Can this Sub Macro be re-written as a Function?

Question

I'm using this macro--which works fine in Sub format:

Sub replaceStringInCells()
Dim wTxt As String
Dim rTxt As String
Dim rNum As Integer
rNum = 0
For Each Row In Range("swapvalues").Rows  '<== change the wordlist Name here as required
    wTxt = Row.Cells(1).Value
    rTxt = Row.Cells(2).Value
        Selection.Replace What:=wTxt, Replacement:=rTxt, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            rNum = rNum + 1
Next
End Sub

I've tried to change it to a function--by changing Sub to Function, updating the Function name, and adding string parameters:

Function replaceaccents(thestring As String)
Dim wTxt As String
Dim rTxt As String
Dim rNum As Integer
rNum = 0
For Each Row In Range("swapvalues").Rows  '<== change the wordlist Name here as required
    wTxt = Row.Cells(1).Value
    rTxt = Row.Cells(2).Value
        Selection.Replace What:=wTxt, Replacement:=rTxt, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            rNum = rNum + 1
Next
replaceaccents = thestring
End Function

But, this just outputs the original cell.

Is there a way I can make this work as a function?

Thanks!

How are you using the Function, from a sub or the worksheet? — Scott Craner, Sep 18 '18 at 17:17
@Scott-Craner - I'm trying to run the Function from a worksheet. — user2258740, Sep 18 '18 at 17:22
@Comintern - Do you know of a workaround for Selection.Replace that would work in a UDF? — user2258740, Sep 18 '18 at 17:24
Possible duplicate of [Excel 2007 VBA Converting Accented Characters to Regular](https://stackoverflow.com/questions/10032322/excel-2007-vba-converting-accented-characters-to-regular). Also see [this page](https://www.extendoffice.com/documents/excel/707-excel-replace-accented-characters.html), and for fun a [CodeReview thread](https://codereview.stackexchange.com/q/123820/92133). — BruceWayne, Sep 18 '18 at 17:29
A function called from the work sheet can not change the value of any cell but the one calling the function. — Scott Craner, Sep 18 '18 at 17:37
@BruceWayne I've seen the code in the link you provided. It's actually the one with which I started. But, it doesn't work for certain accented characters--like Ĭ or Ĩ for example. (When you paste those into a module, they appear as ? or I.) If you know a way around that, I'd be curious. — user2258740, Sep 18 '18 at 17:42
@user2258740 - You're looking to convert the strings to ASCII Characters. Does [this thread](https://stackoverflow.com/q/37024107/4650297) help? — BruceWayne, Sep 18 '18 at 18:06
@BruceWayne - In theory, the top answer on the link you provided should work for me. But, in practice, it just returns the same value as the original cell. I tried changing it from a private function to a public one, but that made no difference. Is there anything else that might keep it from working as is? — user2258740, Sep 18 '18 at 18:51
Step through the code with `F8` (put a break on one of the lines so when the function fires, it stops at that line). You should see the string actually get edited, I would suspect the issue is near the end where it tries to output the new text. As you go along with `F8`, do the variables correctly update (you can hover the mouse over a variable, and it'll show what it is currently set to). Also, do you have some sample data you can show so we can try too? — BruceWayne, Sep 18 '18 at 18:56

score 1 · Answer 1 · answered Sep 18 '18 at 17:28

1

That happens because the function returns exactly the input value. What do you want the input 'thestring' for?

A function returns a value, it's not designed to act as a procedure, so it will only change the cell where it's invoked.

If you want to replace accents (as the name of your function suggests), you should put a reference to a range as an argument (thestring as Range) and then iterate the letters of the string in order to replace á with a, ü with u and so on. This is a more common use of a function. Then, in the spreadsheet you would use it as a regular function just referencing the cell you want to take out the accents from (if that's the purpose).

Hope this helps in any way.

answered Sep 18 '18 at 17:28

Sama Tori

121
1
5

Thanks for the suggestion. If I'm correct, you're suggesting [something like this.](https://codereview.stackexchange.com/questions/123820/replacing-accented-letters-with-regular-letters-in-a-spreadsheet) But, it doesn't work for certain accented characters--like Ĭ or Ĩ for example. (When you paste those into a module, they appear as ? or I.) If you know a way around that, I'd be curious. – user2258740 Sep 18 '18 at 17:47
That's because in the function you mention they are not included. Just make sure all the accented characters you need to be substituted are included in AccChars, and add the substitution in RegChars, making sure you follow the same order. – Sama Tori Sep 19 '18 at 07:55
Sorry, I didn't get your point. I see the editor is not showing the characters you mention. I think you could re-write the function by using vba functions ascw and charw. Ascw gets the number of the character you provide (ex: "ÿ" yields 255) and charw does the opposite. So you can loop through the text and assign the letters by reading with ascw and returning with charw. Check the [UTF-8 list](https://en.wikipedia.org/wiki/List_of_Unicode_characters) – Sama Tori Sep 19 '18 at 08:26
For example, you can do a select case stating that if 191 – Sama Tori Sep 19 '18 at 08:35
Thanks for circling back to this. I've created a variation of the code that replaces the most common accented characters--as the ones that I can't replace don't appear often enough to warrant creating an entirely new macro. – user2258740 Sep 21 '18 at 01:56

Can this Sub Macro be re-written as a Function?

1 Answers1