Excel crashing when putting value from cell into dimension

Question

I have a workbook which creates a new file from template. At one particular step I need a "Channel Count" which is held in formula on the settings sheet of the creator.

This is the code that crashes Excel:

Dim ChCount%
ChCount% = Sheet3.Cells(11, 8).Value

Annoyingly, this works (but obviously not in the long run as this number is dynamic):

Dim ChCount%
'ChCount% = Sheet3.Cells(11, 8).Value
ChCount% = 2

Even more annoyingly, this is only affecting the "24h" version of my tool. I have a "standard" version using the exact same code and it's still working normally. I have no idea what is going on... Excel just full hard crashes, no error codes, and resets with a bunch of "Repaired" files.

Edit: In case it helps this is the formula in the targeted cell... it simply counts Yes or No how many channels out of 8 max it can have. In this example it should be returning "2":

=IF(LEN(H3)>0,1,0)+IF(LEN(H4)>0,1,0)+IF(LEN(H5)>0,1,0)+IF(LEN(H6)>0,1,0)+IF(LEN(H7)>0,1,0)+IF(LEN(H8)>0,1,0)+IF(LEN(H9)>0,1,0)+IF(LEN(H10)>0,1,0)

I know that formula isn't "great" but it's been working fine for literally years...

Is this hard crash reproducible in a separate workbook containing only the minimum code and worksheet cell value? If you read the cell value into a Variant does it crash? — Tim Williams, Oct 05 '22 at 18:05
Thanks for help everyone. I added the formula to OP, also accidentally solved issue with wild theory that the Integer was messed up, so Rounding my formula helped. I cannot reproduce the error on my OTHER tool which uses identical code. I have no idea what the difference is. — AngryCoder, Oct 05 '22 at 18:10
Do you know that your entire formual can be reduced to `=COUNTA(H3:H10)` — Siddharth Rout, Oct 05 '22 at 18:16
BTW, does Excel crash when you use `ChCount = Sheet3.Cells(11, 8).Value` instead of `ChCount% = Sheet3.Cells(11, 8).Value`? — Siddharth Rout, Oct 05 '22 at 18:19
You already did it with `Dim ChCount%` so why `ChCount% =` again? — Siddharth Rout, Oct 05 '22 at 18:20
When I learned of the dim codes I was taught Excel needs the code if you use the code — AngryCoder, Oct 05 '22 at 18:23
@AngryCoder, someone taught you incorrectly then. Also, maybe personal preference, but I find `Dim ChCount As Integer` a lot easier to work with. Also, [run away, run away](https://stackoverflow.com/questions/4958189/what-are-possible-suffixes-after-variable-name-in-vba#comment5536182_4958189) from type hints. — BigBen, Oct 05 '22 at 18:28
I'll try soon when I can get back to it. The workbook has DB connections so it takes 3-5 minutes to load and test again every time it crashes. — AngryCoder, Oct 05 '22 at 18:34
=COUNTA() worked well to replace the formula, and switching to "as Integer" has let me run without crashing. You can make that an answer. :) — AngryCoder, Oct 07 '22 at 20:10

AngryCoder · Answer 1 · 2022-10-05T18:21:51.153

1

This is unbelievable but I just Rounded the formula and now it is working. I have a headache. What could have changed overnight? Why does the other tool without rounding still work? Inconsistencies aaaaahhhhh!!!!!!!

=ROUND(IF(LEN(H3)>0,1,0)+IF(LEN(H4)>0,1,0)+IF(LEN(H5)>0,1,0)+IF(LEN(H6)>0,1,0)+IF(LEN(H7)>0,1,0)+IF(LEN(H8)>0,1,0)+IF(LEN(H9)>0,1,0)+IF(LEN(H10)>0,1,0),0)

EDIT: Guess I'm wrong, it's crashing again UGH

My supervisor needs me to work on other stuff for now, I have to step away for awhile. :(

edited Oct 05 '22 at 18:21

answered Oct 05 '22 at 18:09

AngryCoder

11
2

1

This formula can be [simplified](https://stackoverflow.com/questions/73964324/excel-crashing-when-putting-value-from-cell-into-dimension#comment130597681_73964324) (drastically) btw. – BigBen Oct 05 '22 at 18:15

Excel crashing when putting value from cell into dimension

1 Answers1