Excel Hangs when using Offset in formula. A bug?

[Bart]

Hi everybody,

Has anybody ever experienced Excel hanging when using the function
Offset in a formula?

I've a UserForm to fill out an Excel sheet. I use ControlSources to
copy the entered data to the sheet. In some cells I keep flags to
determine what the user filled in. I use the worksheet to do some
calculations on these flags. That works just fine.. until I use the
function Offset in the calculation. While running my VBA App when I
enter anywhere some data in the UserForm Excel stops responding. To be
precise: on the moment I'm leaving a TextBox by clicking somewhere
else.
I tried several things solving this problem: VBA cleaner and even
rebuilding the whole UserForm. I tried the VBA App on different
computers: same problem. I made a test program. Strange enough every
thing works fine with the test program.
Do you have any idea? Maybe a hint where I should delve into this
problem.

Many thanks in advance,

Bart
XPSP2
Office 2003SP2

 

[Guest]

Do you have any On Calculate event code in your program? Offset is a volatile
function maning that you could be geting into an infinite loop if you have
event code triggered on calculation...

 

[Bart]

Thanks Jim Thomlinson for replying,

No I don't use the event "Private Sub object_Calculate()".
It looks suspiciously like an endless loop: Excel uses 99% of my CPU
when it hangs. I have to use the Task Manager to break of Excel.

Bart

 

[Guest]

I was wondering if you had
Private Sub Worksheet_Calculate()

End Sub
or
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Depending on what you are up to you could end up with a recursive call of
these procedures. That being said have you tried stepping through your code
with F8 to see what the actual flow of execution is?

 

[Bart]

I was wondering if you had
Private Sub Worksheet_Calculate()

End Sub
or
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Depending on what you are up to you could end up with a recursive call of
these procedures. That being said have you tried stepping through your code
with F8 to see what the actual flow of execution is?
--
HTH...

Jim Thomlinson







- Show quoted text -

I tried using step by step. I can start the application. So there
aren't any errors in the Initialize Event. Though I put a Breakpoint
on the last statement of this Event and Stepped (F8) from this point.
I did this on purpose to keep the Debug Mode when using the UI.
I tested several things. In whatever kind of way I write data to the
Worksheet, Excel starts to hang. An example is:

Activesheet.Range("A1").Value = Me.TextBox1.Value

After executing this statement the next line is marked yellow, but
it's the end of the Excel execution session as well: I've to shut down
Excel manually.
When I remove the formula containing the Offset function, no errors
occur!
The App only reads from the Sheet on Events initiated by the user
using the User Interface and at Initializing the UserForm.

Bart

 

[Bart]

Step by step I've tried.
I can start the application. So there aren't any errors in the
Initialize Event. Though I put a Breakpoint on the last statement of
this Event and Stepped (F8) from this point. I did this on purpose to
keep the Debug Mode when using the UI.
I tested several things. In whatever kind of way I write data to the
Worksheet, Excel starts to hang. An example is:

Activesheet.Range("A1").Value = Me.TextBox1.Value

After executing this statement the next line is marked yellow, but
it's the end of the Excel execution session as well: I've to shut down
Excel manually.
When I remove the formula containing the Offset function, no errors
occur!
The App only reads from the sheet on Events initiated by the user
using the User Interface and at initializing the UserForm.

Bart

 

[Guest]

Try turning calculation off while the procedure is running and then turn it
back on at the end perhaps...

Application.Calculation = xlCalculationManual
'your code here
Application.Calculation = xlCalculationAutomatic

 

[Bart]

That solved the problem! Many thanks Jim Thomlinson for your time! I
didn't know about this statement. (Probably because I'm still a bit
new with VBA).

When the App initializes it turns of automated calculation by:

Application.Calculation = xlCalculationManual

When the App has to read Cells containing a Formula I put a statement:

Worksheet("Instructions").Calculate

(Like the Constant implies: handling Sheet Calculations manually)
Though, still it's a weird phenomenon this problem. What I forgot is
to show you the troublemaker:

G10: =IF(H10;AND(I10:J10;OFFSET(I10:J10;17;0));I10:J10)

Thanks again,

Bart

 

[Bart]

Now this.. This is very particular!!
When I have two versions of my App: one WITH the Troublemaker (see my
previous message) and one WITHOUT, the one WITHOUT the Troublemaker
crashes in the same manner as that would happen WITH the Troublemaker.
This can't be something else than a BUG!!

Bart

 

[NickHK]

I've not followed the whole thread closely, but what is your formula trying
to achieve ?

<From help>
IF(logical_test,value_if_true,value_if_false)
</From help>

NickHK

 

[Bart]

NickHK,
On a UserForm the user enters Address Information. On the same
UserForm are checkboxes. Others check the information and check these
boxes when it's correct. All this information is written to the sheet.
When everything is checked I want certain things to make visible to
the user on the UserForm. Depending on what the user filled in, more
flags (checks) have to be checked. And this is where the formula (the
one you noted as well) comes in. One flag (H10) becomes true if the
user filled in 'Notices To' (for example). If this one is true, not
only I10 and J10 have to be checked, but as well I17 and J17:

G10: =IF(H10;AND(I10:J10;OFFSET(I10:J10;17;0));I10:J10)

In G10 is the overall flag where I can scan if everything is checked.
It might be a strange way to solve this. I could do flag checking
directly on the UserForm. I could start making a Collection of the
CheckBoxes etc. But I think it's easier to let Excel calculate the sum
of all flags. This formula is just a tryout. There're much more flags
to check.
Why I'm using Offset is because this formula will in some cases be
copied and inserted down the same column. This happens when a user
wants to fill in more addresses.

I just checked some new things out. I tried to simulate the situation
and tried to get the same error. But that didn't work out. I couldn't
replicate any error, everything worked just fine. Then I tried to
track the error in my App by ripping some pieces of code. I ended up
with the following 'cause' of the flow:
-when I stop using ControlSources of the multiple line Textboxes
everything works without any problem. One such a TextBox is allowed. A
second one will cause the problem again. Then I tried to raise the
DrawBuffer, but that didn't help. I deleted two textboxes and put two
new ones and tried it with these two new once to use the ControlSource
again. Bingo (I thought) that did the job. BUT then when I used a
CheckBox (also with a ControlSource) within the same Frame I got the
same hanging again.

Actually I'm eager to build up from the ground the whole project
again. I main especially the UserForm. But it's too much of work. To
give you an indication: the ScrollHeight of the UserForm is 4800.
(I've tried to copy all the controls to a new form: same problem
again.)
But thanks to ... I've a workaround. But still I'm fascinated. By
editing different versions during my quest for the cause, something
spooky started to happen. Every time I run my App. another version of
my App. was opened automatically. I didn't write any code for that!

Bart

 

[Bart]

NickHK,
On a UserForm the user enters Address Information. On the same
UserForm are checkboxes. Others check the information and check these
boxes when it's correct. All this information is written to the sheet.
When everything is checked I want certain things to make visible to
the user on the UserForm. Depending on what the user filled in, more
flags (checks) have to be checked. And this is where the formula (the
one you noted as well) comes in. One flag (H10) becomes true if the
user filled in 'Notices To' (for example). If this one is true, not
only I10 and J10 have to be checked, but as well I17 and J17:

G10: =IF(H10;AND(I10:J10;OFFSET(I10:J10;17;0));I10:J10)

In G10 is the overall flag where I can scan if everything is checked.
It might be a strange way to solve this. I could do flag checking
directly on the UserForm. I could start making a Collection of the
CheckBoxes etc. But I think it's easier to let Excel calculate the sum
of all flags. This formula is just a tryout. There're much more flags
to check.
Why I'm using Offset is because this formula will in some cases be
copied and inserted down the same column. This happens when a user
wants to fill in more addresses.

I just checked some new things out. I tried to simulate the situation
and tried to get the same error. But that didn't work out. I couldn't
replicate any error, everything worked just fine. Then I tried to
track the error in my App by ripping some pieces of code. I ended up
with the following 'cause' of the flow:
-when I stop using ControlSources of the multiple line Textboxes
everything works without any problem. One such a TextBox is allowed. A
second one will cause the problem again. Then I tried to raise the
DrawBuffer, but that didn't help. I deleted two textboxes and put two
new ones and tried it with these two new once to use the ControlSource
again. Bingo (I thought) that did the job. BUT then when I used a
CheckBox (also with a ControlSource) within the same Frame I got the
same hanging again.

Actually I'm eager to build up from the ground the whole project
again. I main especially the UserForm. But it's too much of work. To
give you an indication: the ScrollHeight of the UserForm is 4800.
(I've tried to copy all the controls to a new form: same problem
again.)
But thanks to Jim Thomlinson I've a workaround. But still I'm
fascinated. By editing different versions during my quest for the
cause, something spooky started to happen. Every time I run my App.
another version of my App. was opened automatically. I didn't write
any code for that!

Bart

 

[NickHK]

You could keep it all in VBA by using a long and bit mask.
You can set a bit by using OR and test for a bit with and AND.
Also, look into NOT for removal of a bit.

You could an array of longs to use for each instance.
You could set up an ENUM to make the setting/reading of specific bits more
readable.
You can get more complex by checking for numerous bits simultaneously. A
BIN2DEC routine will make this easier.

Private Enum MyBits
CHECK_ADDRESS1 = 0
CHECK_ADDRESS2 = 1
CHECK_TELEPHONE = 2
CHECK_EMAIL = 3
'.....etc
CHECK_FAX = 30
End Enum

Dim Flags As Long

Private Sub CommandButton1_Click()

Flags = 0 'Clear all bits
SetBit Flags, 2
SetBit Flags, CHECK_TELEPHONE 'This does nothing, as that bit is already
set
Debug.Print Flags

Debug.Print IsBitSet(Flags, 3)
Debug.Print IsBitSet(Flags, 2)

Flags = -1 'Set all bits
Debug.Print IsBitSet(Flags, CHECK_EMAIL)
Debug.Print IsBitSet(Flags, CHECK_FAX)

Flags = 1431655765 'Set all odd bits, binary
1010101010101010101010101010101
Debug.Print IsBitSet(Flags, 3)
Debug.Print IsBitSet(Flags, 20)

End Sub

Private Function SetBit(ByRef WhichVar As Long, BitToSet As Long)
WhichVar = WhichVar Or 2 ^ (BitToSet - 1)
End Function

Private Function IsBitSet(ByVal WhichVar As Long, BitToTest As Long) As
Boolean
IsBitSet = ((WhichVar And 2 ^ (BitToTest - 1)) = 2 ^ (BitToTest - 1))
End Function

NickHK

 

[Bart]

Thanks a lot NickHK! Never had I taken the time to find out what it
was all about these bitwise comparison. I couldn't understand this
matter by just the scarce explanation in the VBA help files. This is a
great hint. I liked this one. I found some more detailed source on the
internet: "How to utilize binary flags in your program"<
http://www.vb6.us/tutorials/how-utilize-binary-flags-your-program>

It could be that I didn't understand it completely. Please correct me
if so. A Long variable is 32 bits. So you can store 32 flags. (One bit
is used for the minus sign.)
Suppose I've 4 flags and I would like to know if all flags are
checked, assuming that the last bit is used for signing, the Long
variable has to be 15.

BIN2DEC(00000000000000000000000000001111) = 15
BIN2DEC(10000000000000000000000000001111) = -15

In short: I used a method, one that's quick and dirty. The mysteries
aren't solved, but it won't keep me awake any longer.

Thanks again,

Bart

 

[NickHK]

Bart,
To avoid all the issues with the sign, it may be better to only use a
maximum of 31 bits and always work with +ve number. As you are using the
long for flags, rather than calculation, this should not matter.
This means that only need to work with the bits up to the most significant
bit set, not all 32 bits all the time. e.g.
00000000000000000000000000001111 = 1111
Depending on your preference/situation, you can essentially work with binary
(bits) or decimals.

For a better explanation than I could manage:
http://www.romanpress.com/Articles/Bitwise_R/Bitwise.htm
Also, the Calculator that comes with Windows, with View>Scientific set will
be of use to see what you are doing/trying to do <g>.

Here's a some more on this:
' All these routine use the windows convention of the Most Significant Bit
to the left
' i.e. a higher bit position
Private Sub CommandButton1_Click()
Dim Flags As Long

Flags = 13

'Test for the 3rd and 10th bits set
Debug.Print AreBitsSet(Flags, 3, 10)
'Test for 1st and 3rd bits set
'00000000000000000000000000000101 = 101 = 5 dec
Debug.Print AreBitsSet2(Flags, 5)

Flags = 3
UnSetBit Flags, 2
Debug.Print Flags

End Sub

'The bit positions that you are testing for
Private Function AreBitsSet(ByVal WhichVar As Long, ParamArray CheckBits()
As Variant) As Boolean
Dim i As Long

For i = LBound(CheckBits) To UBound(CheckBits)
If (WhichVar And 2 ^ (CheckBits(i) - 1)) <> 2 ^ (CheckBits(i) - 1) Then
AreBitsSet = False
Exit Function
End If
Next

AreBitsSet = True

End Function

'The decimal value of the bits that you are testing for
Private Function AreBitsSet2(ByVal WhichVar As Long, BitsValueDec As Long)
As Boolean
AreBitsSet2 = ((WhichVar And BitsValueDec) = BitsValueDec)
End Function

'Set the indicated bit to 0
Private Function UnSetBit(ByRef WhichVar As Long, WhichBit As Long)
WhichVar = WhichVar And Not (2 ^ (WhichBit - 1))
End Function

NickHK

 

[Bart]

NickHK,
Indeed in theory it's possible to use all the bits in a variable, it's
just practical not advisable.
Very elegant ParamArray in the AreBitsSet! I've never used it before.
To unset a flag I use the Xor operator:

0 Xor 0 = 0
0 Xor 1 = 1
1 Xor 0 = 1
1 Xor 1 = 0

Again, thank you for providing me all this information. I learned a
lot of new things.

Bart

 

[Peter T]

I haven't read the rest of the thread, just your message below.
Strictly speaking Xor does not 'unset' or remove a bit flag, it toggles it
on/off. If that's what you want fine. But if you only want to remove it if
it exists use 'And Not'

Compare -

Debug.Print 0 Xor 1 ' = 1
Debug.Print 0 And Not 1 ' = 0

Regards,
Peter T

 

[Bart]

First I had to make a (handwritten) example to fully understand what
is going on when using the NOT operator in combination with AND. Then
I saw the difference with the XOR operator as well: AND NOT makes sure
that the flag is unset, even when it already was unset. If you want to
use XOR instead, I guess you have to make an accounting of an
accounting!
I was thinking being clever by just using one operator instead of two.
But now I see the problem using XOR. Thanks Peter T!

1101 Xor 0110 = 1011
1101 And Not 0110 = 1001

0 And Not 0 = 0
0 And Not 1 = 0
1 And Not 0 = 1
1 And Not 1 = 0

But I managed to make one function to Set and Unset. To unset a flag
you just give the negative equivalent as an argument:

Public Enum enuAddress
Name = 1
Address = 2
PostalCode = 3
City = 4
'Etc...
End Enum

Public Sub setFlags(ByRef var, ParamArray flags() As Variant)
Dim i As Byte

If LBound(flags) > UBound(flags) Then GoTo ErrorHandling
For i = LBound(flags) To UBound(flags)
If flags(i) < 0 Then
var = var And Not 2 ^ (-flags(i) - 1)
ElseIf flags(i) > 0 Then
var = var Or 2 ^ (flags(i) - 1)
End If
Next
Exit Sub

ErrorHandling:
'...

End Sub

In the help files for the function IsMissing I found out about
checking ParamArray: If IsMissing is used on a ParamArray argument, it
always returns False. To detect an empty ParamArray, test to see if
the array's upper bound is less than its lower bound.

 

[Dana DeLouis]

First I had to make a (handwritten) example to fully understand what

is going on when using the NOT operator in combination with AND.

0 And Not 0 = 0
0 And Not 1 = 0
1 And Not 0 = 1
1 And Not 1 = 0

If interested, this is usually written as:

Not (x Imp y)

(a little easier to follow for computer geeks)
Read as something like: When "Control Switch" y is off, the output is set
to match the input x, and output blocked when switch is on.

 

[NickHK]

Hopefully, as well as learning a new section of coding, this approach will
prove more flexible and straight forward than your earlier worksheet
approach.
Good Luck.

NickHK