Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

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
like image 219
DontFretBrett Avatar asked Nov 13 '22 07:11

DontFretBrett


1 Answers

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

like image 62
John Bustos Avatar answered Dec 31 '22 15:12

John Bustos