Cycle for to populate word template - data type error-CodePudding

I am trying to do a simple code to populate a word file. If i run the script with the For cycle below, VBA return me a runtime -13 error: data type not matched correctly about this code line If CB & i = True Then. Any idea how I could solve to get CB1-CB2-CB3 and so on correctly to claim the different variables in the cycle? Many thanks

Dim CB1 as integer
Dim CB2 as integer
Dim CB3 as integer
Dim CB4 as integer
If Data_entry.CheckBoxTomato.Value = True Then
CB1 = 0
else
CB1 = 1
end if
'the code repeats for CB2,CB3 and CB4
Dim i As Integer
For i = 1 To 4
If CB & i = True Then
.Text = "MARK" & i
.Replacement.Text = "X"
.Execute Replace:=wdReplaceAll
'other sustitution
else
'other substitution
end if
next i

CodePudding user response：

VBA (and most other languages) don't work like this. When you write If CB & i = True Then, VBA will do the following:

Look for a variable CB. If it doesn't exist and you don´t have Option Explicit set, VBA will create it on the fly as an empty variable.
Concatenate the content of the variable with i. Assuming that you didn't declare CB, the result will be a string, eg the string 1 (which is not the number 1).
Now it will try to compare that string with the value True. One is a String, one is a Boolean, and you can't compare those - you will get a type mismatch error.

Your idea is to have 4 variables and you want to check those variables in your loop. Best to do this is not to declare them as 4 single variables but to use an Array. You can access the members of the variable using the index (the "number"), with is added to the variable name in parentheses.

Dim ch(1 to 4) As Boolean  ' Consider to choose a more meaningfull name!

Now you can use them like this:

If Data_entry.CheckBoxTomato.Value = True Then
  ch(1) = True
Else
  ch(1) = False
End IF

However, that can be written shorter, no need for the If:

ch(1) = Data_entry.CheckBoxTomato.Value

And when you check the content, you would use

For i = 1 To 4  
    If cb(i) Then ' use the i´th member of the array.
    ' Note: Some prefer to write If cb(i) = True Then
        .Text = "MARK" & i
        .Replacement.Text = "X"
        .Execute Replace:=wdReplaceAll
    End If
Next i