XL2000: Creating scenario crashes Excel

[geoB]

In a workbook originally designed in XL2007 but ported to XL2000:

The workbook contains a range of external text data (a Quicken export) that is updated on open. The data will then be used to populate scenarios. Because the line items in the data may change the code below steps through the line items to create range names and assign their values. At the end an existing scenario is deleted and recreated. edit: after the code runs Excel crashes when clicking on Tools, Scenarios.

I've narrowed the source of the crash down to the line that creates the scenario. (Excel also crashes if I use ChangeScenario rather than delete and create.)

Is there a work-around? Or have I misused the Scenarios object?

Thanks.

George

Sub MakeRangeNames()
Dim sht As Worksheet, intRow As Long, intCol As Long, rngStart As Range
Dim blnTotal As Boolean, blnNameExists As Boolean, blnEmpty As Boolean
Dim strName As String, dicRangeNames As Dictionary, arrValues()
Dim strAddresses As String, strCell As String, lngValues As Long

Set sht = Worksheets("Consolidation")
Set dicRangeNames = New Dictionary
Set rngStart = sht.UsedRange.Find("Portfolio Export")

intRow = rngStart.Row + 7
intCol = rngStart.Column + 1

While sht.Cells(intRow, intCol).Value <> "TOTAL Investments"
    strCell = sht.Cells(intRow, intCol).Address
    blnTotal = Range(strCell).Value Like "TOTAL*"
    strName = rangeNameCreate(Range(strCell).Value)
    blnNameExists = dicRangeNames.Exists(strName)
    blnEmpty = IsEmpty(Range(strCell).Offset(0, 2).Value)
    If Not blnTotal And Not blnEmpty Then
        'create scenario cells and values
        lngValues = lngValues + 1
        strAddresses = strAddresses & sht.Cells(intRow, intCol + 2).Address & ","
        ReDim Preserve arrValues(lngValues)
        arrValues(lngValues) = sht.Cells(intRow, intCol + 2).Value
        If Not blnNameExists Then
            sht.Cells(intRow, intCol + 2).Name = strName
            dicRangeNames.Add Key:=strName, Item:=sht.Cells(intRow, intCol + 2).Address
        End If
    End If
    intRow = intRow + 1
Wend
sht.Scenarios("Current").Delete
strAddresses = Left(strAddresses, Len(strAddresses) - 1)
sht.Scenarios.Add Name:="Current", ChangingCells:=sht.Range(strAddresses), Values:=arrValues
'sht.Scenarios("Current").ChangeScenario sht.Range(strAddresses), arrValues

[geoB]

In an experiment I used Scenarios.Add (with range names and values created by the above code) to create a scenario in a new, otherwise unused worksheet. I was able to get to the scenario (via Tools, Scenarios...) without crashing. So clearly there is something about the Consolidation sheet that does not like having a scenario created. It's not the code, it's the sheet.

Edit: So I tried a copy & paste values of the sheet to a new workbook. Ran the above code (less the Scenario.Delete) and it also crashes on Tools, Scenarios.

Ugh!

g

[geoB]

I have identified a difference in behavior between XL2000 & XL2007 that is most likely the source of error. In the code above I added/changed the last couple of lines to read

Debug.Print "Addresses before: " & Range(strAddresses).Count
sht.Scenarios.Add Name:="test", ChangingCells:=sht.Range(strAddresses), Values:=arrValues
Debug.Print "Addresses after: " & sht.Scenarios("test").ChangingCells.Count

XL2000 prints:

Addresses before: 27
Addresses after: 22

XL2007 prints:

Addresses before: 27
Addresses after: 27

Clearly, XL2000 is truncating the address set.
Sure looks like a bug to me!
g

[geoB]

If one runs the following:

Sub scTest()
Dim sht As Worksheet, i As Integer, addresses As String, j As Integer
Set sht = Worksheets(1)
For j = 1 To 26
    On Error Resume Next
    sht.Scenarios("test").Delete
    addresses = ""
    For i = 1 To j
        addresses = addresses & "$" & Chr(64 + i) & "$" & CStr(i) & ","
    Next i
    addresses = Left(addresses, Len(addresses) - 1)
    addresses = Replace(addresses, "$", "")
    Debug.Print "Before: " & Range(addresses).Count
    sht.Scenarios.Add Name:="test", ChangingCells:=Range(addresses)
    Debug.Print "After: " & sht.Scenarios("test").ChangingCells.Count
Next j
End Sub

one finds that the maximum number of cells one can add with vba is 24, which is less than the stated spec of 32 changing cells per scenario!

g

[geoB]

Well, I've found the work-around. Not only must vba limit the number of changing cells to 24, the length of the cell's name may be no longer than 7 characters! There may be more characters allowed if there are fewer changing cells, but I'll save that for another investigator. But for now this problem is solved. Sort of.

g