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
– FAB, Commented Jun 13, 2019 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
– Doug Kimzey, Commented Jun 13, 2019 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
– FAB, Commented Jun 13, 2019 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
– Degustaf, Commented Jun 13, 2019 at 12:46

2 revs · Accepted Answer · 2019-06-20 18:01:19Z

1

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.

edited Jun 20, 2019 at 18:01

community wiki

2 revs
Doug Kimzey

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related