Iterate over and check the byte value of every character in a string - VBA

Question

Iterate over and check the byte value of every character in a string - VBA

Code I have:

        cell_val = CStr(Nz(fld.value, ""))
        Dim iter As Long
        For iter = 0 To Len(cell_val) - 1 Step 1
         If Asc(Mid(cell_val, iter, 1)) > 127 Then
           addlog "Export contains ascii character > 127"
         End If
        Next iter

This code doesn't work. Anyone know how to do this? I've simply got no idea with VB or VBA.

Answer 1

I believe your problem is that in VBA string indexes start at 1 and not at 0. Try the following:

For iter = 1 To Len(cell_val) 
    If Asc(Mid(cell_val, iter, 1)) > 127 Then
        addlog "Export contains ascii character > 127"
    End If
Next

Answer 2

Did you debug it? ;) Are you sure the cell_val is not empty? Also you don't need the 'Step 1' in the For loop since it's default. Also what do you expect to acomplish with your code? It logs if any ascii values are above 127? But that's it - there is no branching depending on the result?

Answer 3

Try AscW()

Answer 4

VBA strings are based from 1 so you should probably use "For iter = 1 To Len(cell_val)" (step 1 is the default).

Answer 5

Your example should be modfied so it does not have external dependencies, it now depends on Nz and addLog.

Anyway, the problem here seems to be that you are looping from 0 to len()-1. In VBA this would be 1 to n.

 Dim cell_val As String
 cell_val = "øabcdæøå~!#%&/()"
 Dim iter As Long
 For iter = 1 To Len(cell_val)
    If Asc(Mid(cell_val, iter, 1)) > 127 Then
       'addlog "Export contains ascii character > 127"
       Debug.Print iter, "Export contains ascii character > 127"
    End If
 Next iter

Answer 6

Did you debug it? ;) Are you sure the cell_val is not empty? Also you don't need the 'Step 1' in the For loop since it's default. Also what do you expect to acomplish with your code? It logs if any ascii values are above 127? But that's it - there is no branching depending on the result?

I didn't debug it, I have no idea how to use vba or any of the tools that go along with it. Yes I am sure cell_val is not empty. The code was representative, I was ensuring the branch condition works before writing the branch itself.

I believe your problem is that in VBA string indexes start at 1 and not at 0.

Ah, the exact kind of thing that goes along with vba that I was bound to miss, thank you.

Answer 7

With VBA, VB6 you can just declare a byte array and assign a string value to it and it will be converted for you. Then you can just iterate through it like a regular array.

e.g.

Dim b() as byte
Dim iter As Long
b = CStr(Nz(fld.value, ""))

For iter = 0 To UBound(b)
    if b(iter) > 127 then
        addlog "Export contains ascii character > 127"
    end if
next