XL2BB Runtime Error 1004 on Excel Beta Channel

[DRSteele]

Can someone please help me?

I am now getting this error when I select a range in XL2BB: Runtime Error 10004, invalid reference.

It happens in any workbook that contains an Excel function, but not in any that does not.

Version 365 Beta 2106 Build 14107.20000, which arrived yesterday.

I uninstalled and re-installed XL2BB and restarted the device (Windows 10 64), to no avail.

 

[smozgur]

This should be directly related with the update as I was informed about the same issue by another member today, and they also mentioned that they recently installed an update.

I will be able to see the problem as soon as I have the update installed. Until then, could you please deselect all additional options and try to create a mini-sheet? If it works, then try to enable each additional option one by one to see if one of the options is causing the issue? Thanks.

 

[DRSteele]

Thanks smozgur. I did all that and it won't go past the runtime error. Strangely, after I dismiss the message, it goes to cell A1 of the first worksheet and selects all the data! Weirdness.

 

[smozgur]

@DRSteele - could you please also confirm if the problem happens even you select an empty cell, or a cell with a value, or with a function, or a dynamic array function? That would be great if you could test it for a single cell.

 

[smozgur]

@DRSteele: I think it will work with a single cell. I believe it is a bug with VBA in the Beta channel. I'll let you know when I am certain about it.

In the meantime, XL2BB is done copying the selected range before the execution halts with the error. You can click the on the End button when it halts, and paste the clipboard content as XL2BB code. Just FYI.

 

[DRSteele]

Ah. Good show. I wanted to help someone earlier but I was forced to just describe a solution rather than post a solution.

 

[smozgur]

It looks like a VBA bug. @MrExcel reported it to Microsoft. We should hear about it in a week or so, I guess.

Reason: Application.Goto method fails with multi-cell range.
Version: Beta Channel - 2106 (Build 14107.20000) Windows and 16.50 (210409000) Mac

How to: Open a workbook, and go to VBE Immediate window. Write the following command in the debug window and press Enter:

Application.Goto Range("A1:B1")

All other variations fail as well:

' Same with above
Application.Goto Activesheet.Range("A1:B1")
' With worksheet reference
Application.Goto Sheet1.Range("A1:B1")
' With range object
set rng = Activesheet.Range("A1:B1")
Application.Goto rng

It works with multiple ranges if only single cells are defined:

Application.Goto Sheet1.Range("A1,B3")

 

[DRSteele]

I have as much chance of figuring out that code as Tesla's financial statements. I shall wait for a fix.

 

[schardt679]

I am also having this issue. I have discovered having any combination of formulas, conditional formatting, and/or data validation checked works. The error pops up but it does copy the range to the clipboard. Named ranges cause it to fail and does not copy. Also, I can't create a named range by typing in the name box. It gives me the "There's a problem with this formula." error. I can only create named ranges from the name manager or create from selection

 

[smozgur]

That's correct. Named range processing module also executes Application.Goto method, and in this case, XL2BB has no chance to complete the tag generation at that point.
As a workaround, until Microsoft fixes the problem, or you downgrade to the stable channel, you can uncheck the Named Ranges option in the XL2BB settings to generate the XL2BB code. Of course, the resulting mini-sheet won't include Named Ranges in this case.

Also, I can't create a named range by typing in the name box. It gives me the "There's a problem with this formula." error. I can only create named ranges from the name manager or create from selection

It looks like another bug in the beta channel. We will check if it was already reported, or report it otherwise.
So many bugs in the latest beta release.