If value = true then append 1 else 0 in VBA

I'm not sure if this is feasible, but I figured I'd ask and see if anyone had any bright solutions.

I'm creating开发者_JS百科 a VBA form that people will use to assemble an SQL string: "Tick this box to make this parameter true" kind of stuff.

The obvious way to implement this would be

If Checkbox.Value Then
    string = string + "@parameter = 1, "
Else
    string = string + "@parameter = 0, "
End If

The annoyance is that this is kind of verbose. It would be nice if I could do something like this:

string = string + "@parameter = " + Checkbox.value + ", "

But I'm not sure if that's feasible. Is it possible?

Have you considered using IIf?

string = string + "@parameter = " + IIf(Checkbox.value, "1", "0") + ", "

You can learn more about the function here.

In VBA, True is -1 and False is 0, which is different than Excel proper (True is 1).

string = string + "@parameter = " & CLng(Abs(Checkbox.Value)) & ", "

That will convert False to zero and True to positive one. The Abs probably converts it to a number, so the CLng may be overkill. But I plead readability.

An even shorter solution would be:

string = string + "@parameter = " & -1 * Checkbox.Value & ", "