Transforming a sub to a function in v.b.a that converts html from a cell to rich text

Question

I have copied a code which runs a string in html format into the I.E. web browser, which converts it to rich text, then copies it back into the cell that the code was run on. I've included the code below.

However, I want the ability to run this sub as a function for other users so they will never have to access the developer tab. I'm stuck on how to convert this sub into a function so it would be great if someone could help me out.

Sub Sample()

    Dim Ie As Object
    
    Set Ie = CreateObject("InternetExplorer.Application")
    
    With Ie
        .Visible = False
        
        .Navigate "about:blank"
        
        .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value
        
        .ExecWB 17, 0: .ExecWB 12, 2
        ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("A1")
        
        .Quit
    End With
End Sub

Thanks for all the responses.

for example, in cell A1 I have '< html > this is < b > a custom text < / b>'. I want to be able to write in cell A2 '=sample(A1)', and for the text in A1 to be converted into formatted text, ie. 'this is a custom text' by the code I've included. The cell A2 would have the converted text in.

I assume this html returns a table? If so depending on one's version the user would need to highlight enough cells to handle the entire table and use Ctrl-Shift-Enter to array enter the function. It would be better if you used a button, or assign it to a shortcut and just use the sub. — Scott Craner, Apr 30 '21 at 14:40
This code just takes a string in html format that is in a single cell, for example ' this is a custom text ', and passes it into a web browers, which converts it into rich text. It then copies the text which has been formatted, and pastes it back into that cell as formatted rich text. I'm trying to turn this into a function which selects a cell that has html formatted text in, and then converts it using this method. The cell with the function in would theoretically then contains the formatted text. — Jamesk112, Apr 30 '21 at 14:52
"pastes it back into that cell as formatted rich text" ... I don't think this is doable as a UDF. — BigBen, Apr 30 '21 at 14:53
just remember that it can only return a text value, without any type of formatting. UDF will not format the cell like copy paste will and you can't copy paste with a UDF. — Scott Craner, Apr 30 '21 at 14:54
You can use [Application.Caller](https://learn.microsoft.com/en-us/office/vba/api/excel.application.caller) from within the VBA function to reference the calling cell. Application.Caller.Value = Sheets("Sheet1").Range("A1").Value will copy whatever is in Sheet1!A1 to the cell containitn the UDF. — Nicholas Hunter, Apr 30 '21 at 14:56
@NicholasHunter I believe `Application.Caller` is irrelevant, as the UDF can't modify the cell it's called from, but can only return a value (w/o format) to said cell. — BigBen, Apr 30 '21 at 14:57
@NicholasHunter no `Application.Caller.Value = Sheets("Sheet1").Range("A1").Value` will not work as Scott already pointed out in a comment to your deleted answer. The function should return the value, i.e. `Sample = Sheets("Sheet1").Range("A1").Value`, but that's also irrelevant as a UDF can't `Paste`. — BigBen, Apr 30 '21 at 14:58
If there is a way past using copy paste that would be great as well - I dont need the UDF to modify the cell that its in — Jamesk112, Apr 30 '21 at 14:59
@Jamesk112 no a UDF can't copy/paste, and it can't return formatted text like you're trying. You need a `Sub` for that. — BigBen, Apr 30 '21 at 14:59
A UDF cannot change the value or format of any cell, it can only return a value. The format of the output cell is set separately. This will need to remain a sub, but like I said you can attach a shortcut to the sub so the user only needs to do something like ctrl-h to run it from the sheet. — Scott Craner, Apr 30 '21 at 15:02
Now that I said that, there is a work around if you want to try it: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet but it is a little hacky and not 100% reliable. — Scott Craner, Apr 30 '21 at 15:03
Thanks all. Scott, that's great thank you - very helpful information. — Jamesk112, Apr 30 '21 at 15:08
@ScottCraner I was not aware that a UDF cannot change the value or format of any cell, makes sense though. It's disappointing that the attempt to do so fails silently instead of throwing an error. Anyway, lesson learned. It occurred to me that even if that were not true and one was able to paste a value into the calling cell, that value would be immediately overwritten by the return value of the UDF. — Nicholas Hunter, Apr 30 '21 at 15:28
While you're a bit stuffed if you wanted to use a UDF for this, I would take a look at the accepted answer here https://stackoverflow.com/questions/63716400/injecting-rtf-code-in-the-clipboard-to-paste-into-ms-word-as-rtf-text-via-a-vba which has a nice solution for putting different formats of text on the clipboard. — Tim Williams, Apr 30 '21 at 17:13

Nicholas Hunter · Answer 1 · 2021-04-30T16:37:12.040

This UDF addresses the use case given in the question. The function uses a regular expression to parse out the text to be bolded and then leverages Mark.R's code from a few years ago to translate the character values to the equivalent unicode bold character values. This code only handles the <b> tag but it could easily be extended to handle the <i> tag.

Option Explicit

Function EmboldenText(ByVal source As Range) As String

    Dim re As VBScript_RegExp_55.RegExp
    Dim m As VBScript_RegExp_55.Match
    Dim strIn As String
    Dim strout As String
    Dim start As Integer
    
    Set re = New VBScript_RegExp_55.RegExp
    re.Pattern = "<b>(.+?)</b>"
    re.Global = True
    
    strIn = source.Value
    strout = ""
    start = 1

    For Each m In re.Execute(strIn)
        strout = strout & Mid(strIn, start, m.FirstIndex - start + 1) & _
            Bold(m.SubMatches(0))
        start = m.FirstIndex + m.Length + 1
    Next m
    
    EmboldenText = strout & Mid(strIn, start)

End Function

You will need to add this library to your references if you want to try this solution.

Transforming a sub to a function in v.b.a that converts html from a cell to rich text

1 Answers1