VBA Function To Transform Boolean into Integer Not working

Bryce Bassett
Bryce Bassett used Ask the Experts™
In VBA, I'm having trouble with a very simple piece of code.  I've been staring at it for an hour but can't figure out what I'm doing wrong.

I'm working in Word (365 perpetual) on Windows 10.  At one point I want to write the value of a series of userform checkboxes out to an Excel file.  I need to write them as numeric values instead of "True" and "False."  So I added a function that's supposed to turn a True into -1 and False into 0.  It has worked fine before, but tonight for some reason it always returns 0.

xlsheet.Cells(nextrow, 34) = booltodigit(Me.CheckBox1.Value)
xlsheet.Cells(nextrow, 35) = booltodigit(Me.CheckBox2.Value)
xlsheet.Cells(nextrow, 36) = booltodigit(Me.CheckBox3.Value)
xlsheet.Cells(nextrow, 37) = booltodigit(Me.CheckBox4.Value)
xlsheet.Cells(nextrow, 38) = booltodigit(Me.CheckBox5.Value)
xlsheet.Cells(nextrow, 39) = booltodigit(Me.CheckBox6.Value)
xlsheet.Cells(nextrow, 40) = booltodigit(Me.CheckBox7.Value)
xlsheet.Cells(nextrow, 41) = booltodigit(Me.CheckBox8.Value)
xlsheet.Cells(nextrow, 47) = booltodigit(Me.CheckBox9.Value)

Private Function booltodigit (ByRef bool As Boolean) As Integer
MsgBox bool
If bool = True Then
    booltodigit = -1
    booltodigit = 0
End If
MsgBox booltodigit
End Function

Open in new window

As you can see I've put a few MsgBox commands in my function to verify what's going on, and no message pops up, so it appears it's not even calling the function.

Can you spot what I'm doing wrong?  It must be so simple but I just can't see it.  Most frustrating is that this worked in the past but not now.   Thanks!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try getting rid of both message boxes from the function. There are a few things which work in a sub but not in a function.
Analyst Assistant
Do you really need a function?

Why not something like this?
xlsheet.Cells(nextrow, 34).Value =Iif(Me.CheckBox1.Value, -1,0)

Open in new window

Bryce BassettFreelance VBA programmer


Thanks, Norie.  I must admit I've never used the Iif function, but it did the trick!   Still confused at how the other worked sometime, but I'm not going to worry about that.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark EdwardsChief Technology Officer

When working with Boolean logic, where you assume that you are always working with True (-1) or False (0) control value inputs, make sure you cover the possibility of encountering a Null.  i.e. Nz(Me.Checkbox.Value,0)

Since your function is expecting a Boolean value input parameter, a Null may cause the function to not even run because of a Type mismatch.

Your IIF() function, on the other hand, does not have an input parameter and will run with a Null checkbox, causing the function to always return a 0 if the checkbox is Null (or False), and -1 if True.
You can further shorten it to

xlsheet.Cells(nextrow, 34) = --Me.CheckBox1.Value
Bryce BassettFreelance VBA programmer


Thanks, all!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial