Assign a value to a cell in Excel VBA crashes VBA with no message

Question

I am using Excel 2016 32-bit and Excel 2019 32-bit. In both Excel versions, programmatic assigns halt VBA execution without any message.

Trust Center settings are set to Enable all Macros and Trust the access to the VBA Project Model.
The calculations work and are correct.

The code I am working with is below. Execution halts when the value of the LL95 variable is assigned to a cell. At this point VBA halts without any message.

    ' Compute 95%, 85% and 75% Confidence Intervals
    Public Sub ConfidenceIntervals(topInputRow As Integer, bottomInputRow As Integer, outputRow As Integer)

        Dim avg As Double
        Dim sd As Double
        Dim n As Double

        Dim LL95 As Double
        Dim UL95 As Double

        Dim LL85 As Double
        Dim UL85 As Double

        Dim LL75 As Double
        Dim UL75 As Double

        Dim intervalRange As Range
        Dim rangeString As String

        n = CDbl(bottomInputRow - topInputRow)

        rangeString = "C" & CStr(topInputRow) & ":C" & CStr(bottomInputRow)

        Set intervalRange = Worksheets("Samples").Range(rangeString)

        avg = WorksheetFunction.Average(intervalRange)
        sd = WorksheetFunction.StDev_S(intervalRange)

        ' 95% Confidence Intervals
        LL95 = Exp(avg - sd * 2.2622 / Sqr(n))
        UL95 = Exp(avg + sd * 2.2622 / Sqr(n))

        ' 85% Confidence Intervals
        LL85 = Exp(avg - sd * 1.5737 / Sqr(n))
        UL85 = Exp(avg + sd * 1.5737 / Sqr(n))

        ' 75% Confidence Intervals
        LL75 = Exp(avg - sd * 1.2297 / Sqr(n))
        UL75 = Exp(avg + sd * 1.2297 / Sqr(n))

        ' Write the intervals to the output row.
        Sheets("Samples").Select

        Cells(outputRow, 7).Value = LL95
        Cells(outputRow, 8).Value = UL95

        Cells(outputRow, 9).Value = LL85
        Cells(outputRow, 10).Value = UL85

        Cells(outputRow, 11).Value = LL75
        Cells(outputRow, 12).Value = UL75

    End Sub

I tried the following code to assign values. However, this produces the same behavior. VBA bombs with no message on the Rng.Value = LL95.

        Dim Rng As Range
        Set Rng = Sheets("Samples").Cells(outputRow, 7)
        Rng.Value = LL95

        Set Rng = Sheets("Samples").Cells(outputRow, 8)
        Rng.Value = UL95

        Set Rng = Sheets("Samples").Cells(outputRow, 9)
        Rng.Value = LL85

        Set Rng = Sheets("Samples").Cells(outputRow, 10)
        Rng.Value = UL85

        Set Rng = Sheets("Samples").Cells(outputRow, 11)
        Rng.Value = LL75

        Set Rng = Sheets("Samples").Cells(outputRow, 12)
        Rng.Value = UL75

I have additionally tried:

Setting Tools | Options | General | Break on All Errors. No additional information or error messages are shown.
Adding an On Error Goto: (to obtain further information).

What am I overlooking?

What would be the value of `sampleRow` when it stops ? It is at the first iteration ? I assume it might have something to do with `Randomize` maybe. — FAB, Jun 13 '19 at 12:30
It never reaches the randomize. It bombs on the line above the Randomize. The value of sampleRow is 2. This is the initial value set the For loop. — Doug Kimzey, Jun 13 '19 at 12:43
It doesn't make any sense to exit there... the only scenario when that can happen seemingly for "no reason", is when you would have variables instead of numbers, and second one is smaller. Clearly not the case here... — FAB, Jun 13 '19 at 12:46
And what version of Excel are you using? I needed to change `LogNorm.Inv` to get the code to compile in 2010, but then it ran without issue. — Degustaf, Jun 13 '19 at 12:46
If you set Tools->Options->General->Error Trapping to `Break on All Errors`, do you get an error message? Maybe "Method 'Cells' of object '_Global' failed"? — Degustaf, Jun 13 '19 at 13:00
Excellent tip - using .Value on the cell assignment seemed to do the trick. I have not had to do this in the past. I have posted the revised code below. — Doug Kimzey, Jun 13 '19 at 13:11

Doug Kimzey · Answer 1 · 2019-06-20T18:01:19.070

I gave the Excel workbook to a friend of mine. He opened the workbook and ran it without any issue. The VBA would not run on three of my workstations. The VBA ran without any issue on two of his workstations. One difference between my workstations and his is that I am signed into my Office 365 account inside Excel 2016 and 2019.

As a test (I did not expect this to make a difference), I signed out of Office 365 in Excel. After signing out, I ran the macro. The VBA executed without silently crashing and populated the cells.

The solution seems to be:

Sign out of your Office 365 Account in Excel.
Run the macro. Cells are assigned just fine.

Apart from a potential issue encountered when internally raising a cell event, I am not sure why this worked.

I would be interested in hearing more solid and better substantiated reasoning.

Assign a value to a cell in Excel VBA crashes VBA with no message

1 Answers1