I have some very basic and simple VBA code in the Worksheet_SelectionChange
event like the following:
btnB.Top = btnA.Top + btnA.Height
It runs fine on my computer but it works sporadically on my coworker's computer. Seems like 3 out of 5 clicks around the sheet will produce the "Expression Too Complex"
error. The other 2 work without throwing an error. I tried:
Dim D as Double:D = btnA.Top + btnA.Height
btnB.Top = D
And same thing, works sometimes, sometimes it throws the error. When the error happened, I broke and checked the value of D
which was "-1.#IND"
. I googled that value and discovered it means interdeterminate. btnA.Top
is about 11,500 so it's not an overflow issue. There seems to be no rhyme or reason to this issue. In 16 years of VB and VBA programming, I've never seen or heard of anything quite like this. Thanks for any help/insight.
Edit Full Code:
Sub LineUpBtns()
CommandButton2.Top = CommandButton1.Top + CommandButton1.Height
CommandButton3.Top = CommandButton2.Top + CommandButton2.Height
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
LineUpBtns
End Sub
d1 = btnA.Top : d2 = btnA.Height
Look at d1
, d2
, as well as btnA
properties in the watch window. We can't help if we don't have the workbook, or at least the entire code and a screenshot of the button - Jean-François Corbett 2012-04-06 07:46
I know this might seem crazy, but maybe reference the buttons based upon the worksheet... Maybe there's something just loopy enough on the other machine that it is looking in a sheet that doesn't have the buttons on it so that would produce this kind of error...
Sub LineUpBtns()
Sheets("Sheet1").Shapes("CommandButton2").Top = Sheets("Sheet1").Shapes("CommandButton1").Top + Sheets("Sheet1").Shapes("CommandButton1").Height
Sheets("Sheet1").Shapes("CommandButton3").Top = Sheets("Sheet1").Shapes("CommandButton2").Top + Sheets("Sheet1").Shapes("CommandButton2").Height
End Sub
Hope this helps!!
#IND
is a NaN (Not a Number) which is a value of numeric data type that represents a undefined/unrepresentable value. In floating-point calculations as you state, windows displays it as -1.#IND (indeterminate).
To get it I believe you have to be doing something like:
Dim d As Double
On Error Resume Next
d = 0 / 0
On Error GoTo 0
If you have any error handling then remove it to test for the problem. You could be experiencing some kind of bug too. So for problems like yours it can be difficult to diagnose without seeing the workbook itself, you could upload it to www.ozgrid.com/forum so we can test ourselves.
Try adding this to the code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
application.EnableEvents = False
''''your code here
application.EnableEvents = True
End Sub
It wont produce an overflow then