Function test_conv(va As Range)
Dim s, st, tmp As String
Dim i, cd, ln As Integer
Dim samp As String

Application.Volatile　'Subでは使わない。

samp = "１２３４５６７８９０ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸ
ＹＺａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ"　　'ここで変換した
いものを列記した
tmp = ""

st = va.Value
ln = Len(st)

For i = 1 To ln
s = Mid(st, i, 1)

If InStr(samp, s) > 0 Then
tmp = tmp + StrConv(s, vbNarrow)
Else
tmp = tmp + s
End If
Next i

test_conv = tmp

End Function

Functionにしましたが，Subでもよいとおもいます。

方法２

Public Function ZenToHanAlnum( _
                    ByVal str As String _
                ) As String
    Dim i As Integer
    Dim ch1 As String
    Dim ch2 As String
    ZenToHanAlnum = ""
    For i = 1 To Len(str)
        ch1 = Mid(str, i, 1)
        ch2 = StrConv(ch1, vbNarrow)
        Select Case ch2
        Case "0" To "9", _
             "A" To "Z", _
             "a" To "z"
            ZenToHanAlnum = ZenToHanAlnum & ch2
        Case Else
            ZenToHanAlnum = ZenToHanAlnum & ch1
        End Select
    Next i
End Function