Strange Excel VBA Error "Expression Too Complex" -1.#IND

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

excel
vba

2012-04-05 20:26
by DontFretBrett

how can you assign a double to btnB.Top - l--''''''---------'''''''''''' 2012-04-05 20:52

Because it is a double. TypeName(btnB.Top) = Doubl - DontFretBrett 2012-04-05 22:32

What are btnA, btnB ? Some types of object seems to have a .Top property which is a Single instead of Double. EDIT: never mind - answered just now in your comment - Tim Williams 2012-04-05 22:35

For MSForms.Control the .Top property is Single, but the btnA (which is an MSForms.CommandButton) accepts and returns Doubles with no issue - DontFretBrett 2012-04-05 22:41

Can we see the rest of the code please - Reafidy 2012-04-06 05:47

Break it down into smaller pieces until you identify the culprit: 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 have a sub and all it does is line up buttons vertically. Really the code wouldn't provide anymore insight than I've provided. I know that's rarely the case but it's definitely the case now. Thanks for the suggestion @Jean-FrançoisCorbett but I tried that to no avai - DontFretBrett 2012-04-09 17:20

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!!

2012-10-12 19:26
by John Bustos

#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.

2012-04-06 06:15
by Reafidy

I have no error handling on the sub. Even when I add error handling (like on error resume next), it still throws the error. Sounds like a bug. I can't upload the workbook (company policy), but it may not help much anyway because the code works perfect on my computer and several others I've tried except one. It's SUPER simple code. There's no error. I'm thinking it must be a bu - DontFretBrett 2012-04-09 17:27

I don't think we can help you, your simply not giving us the required info. You could at least post the entire selection_change event - Reafidy 2012-04-09 23:17

Bud, it's seriously as simple as... Worksheet_SelectionChange(Target as Range):btnB.Top = btnA.Top+BtnA.Height:btnC.Top = btnB.Top+btnB.Height:End Sub. No joke, I'm not under/over exaggerating the simplicity. I can't upload the whole workbook but it wouldn't make a difference anyway. I know you feel like I'm leaving something out because it doesn't make sense. That's the problem, it makes no sense and I can't figure out why it works on all computers except one - DontFretBrett 2012-04-10 04:12

Perhaps you could try re-installing Excel on the offending machine? Maybe an AddIn or other library is interfering - Bobort 2012-05-04 14:05

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

2012-04-20 06:27
by Kartik Anand