VBA and Boolean data types


New Member
Jun 6, 2017
I used a data source that used a True/False variable. I replaced True with 1 and False with 0.001. I wanted to do regression which does not accept zeroes. This has worked well for several years and regression has worked with this variable. All of a sudden I am trying to use this variable with VBA and finding out that when trying to do calculations or filters to select specific rows that the underlying values are -1. I've researched and found that when replacing Booleans variables with numbers that the value is replaced with a -1. I don't understand why this issue has not been noticed previously, but it may be the first time I was using the variable in a "If(ppool = " (I've tried 0, 1, 001, True, False) but am finding that the underlying value is always -1. I've tried different formats but nothing has changed this condition. As noted, I've tried going back to True/False but VBA still looks at the variable as -1. If I could return to True/False I've seen where you can change to 1 and 0 (which I could use) but apparently once you have added a number to the variable you get -1 regardless. I can change the "displayed" value but cannot do anything that used the basic value (except regression accepts the 1 and 0.001 displayed values as 1 and 0.001. I can copy and paste/display the 0.001. value but when doing a PPool = ActiveCell.Select it is -1.

Retreating to the original data source would be a major re-structuring to the project.

Any suggestions? Thanks in advance.


Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Legacy 456155

It would be helpful if you can post your functions. In VBA a numeric of zero is FALSE, and everything greater or less than zero will implicitly cast to TRUE. From what you have posted, I don't see any reason why you cannot use an alternative integral type or create your own type if necessary. Please post your code.

Watch MrExcel Video

Forum statistics

Latest member