Need a little help with Excel VBA

Not Games
21

No problem, happy to help. I had to teach myself VBA for a summer project 15 or so years ago and only occasionally manage to find opportunities to use it these days.

It runs really slowly, is a pain to debug and the documentation is often unhelpful… but it can be incredibly useful in the right circumstances and I like it for how readable the code is for an inexperienced user even gasp without many/any comments.

2 Likes
22

Something about my code to convert the ℃ symbol is causing trouble. When I have a cell that is (℃), the desired output is (°C) but I get (°C with the second bracket dropped. This is probably happening because of the hacked-together way I did it, advancing the charPos and overwriting the character after the ℃, but what should I be doing to insert the second character instead of overwriting?

                Call ConvertCelsius(cell, &H2103, &HB0, &H43)             ' Double Celsius

Sub ConvertCelsius(ByRef thisCell As Variant, ByVal inputCharCode As Long, ByVal outputCharCode As Long, ByVal outputCharCode2 As Long)

    Dim charPos As Long
    charPos = InStr(thisCell.Value, ChrW(inputCharCode))
    
    Do While charPos > 0
        thisCell.Characters(charPos, 1).Text = ChrW(outputCharCode)
        thisCell.Characters(charPos, 1).Font.Name = "Symbol"
        charPos = charPos + 1
        thisCell.Characters(charPos, 1).Text = ChrW(outputCharCode2)
        thisCell.Characters(charPos, 1).Font.Name = "Times New Roman"
        charPos = InStr(charPos + 1, thisCell.Value, ChrW(inputCharCode))
    Loop

End Sub

EDIT: resolved it myself, yay =)

Call ConvertCelsius(cell, &H2103, "°C")            ' Double Celsius


Sub ConvertCelsius(ByRef thisCell As Variant, ByVal inputCharCode As Long, ByVal outputChar As String)
    
    Dim charPos As Long
    charPos = InStr(thisCell.Value, ChrW(inputCharCode))
    
    Do While charPos > 0
        thisCell.Characters(charPos, 1).Text = outputChar
        thisCell.Characters(charPos, 1).Font.Name = "Symbol"
        charPos = charPos + 1
        thisCell.Characters(charPos, 1).Font.Name = "Times New Roman"
        charPos = InStr(charPos + 1, thisCell.Value, ChrW(inputCharCode))
    Loop

End Sub
2 Likes