Microsoft Office Forums - View Single Post

Guessed · 07-14-2021, 06:36 PM

I don't know how to correctly handle the minus sign (is b-a the same as a-b in hebrew LTR and english RTL) or the spaces but this should be enough to get you started

Code:

Sub MyCalculator()
  Dim aChar As Variant, aRng As Range, iTotal As Long
  Set aRng = Selection.Range
  For Each aChar In aRng.Characters
    Select Case AscW(aChar)
      Case 1488 To 1497: iTotal = iTotal + AscW(aChar) - 1487
      Case 1499: iTotal = iTotal + 20
      Case 1500: iTotal = iTotal + 30
      Case 1502: iTotal = iTotal + 40
      Case 1504: iTotal = iTotal + 50
      Case 1505: iTotal = iTotal + 60
      Case 1506: iTotal = iTotal + 70
      Case 1508: iTotal = iTotal + 80
      Case 1510: iTotal = iTotal + 90
      Case 1511: iTotal = iTotal + 100
      Case 1512: iTotal = iTotal + 200
      Case 1513: iTotal = iTotal + 300
      Case 1514: iTotal = iTotal + 400
      Case 45: iTotal = iTotal * -1     'a minus sign
    End Select
  Next aChar
  MsgBox iTotal
End Sub

I needed to convert the characters to their AscW ids to do this. This is the code I used to build that list when I selected your legend code.

Code:

Sub GetAscWFromLegend()
  Dim arr() As String, i As Integer, aRng As Range
  Set aRng = Selection.Range
  arr = Split(aRng.Text, " ")
  For i = LBound(arr) To UBound(arr)
    Debug.Print AscW(arr(i)), Mid(arr(i), 3)
  Next i
End Sub