M
mchensel
I have this issue running in Exel 2003, it works fine in Excel 97. This
is not my code so I'm not really much help with the logic in it, hoping
its just a generic problem someone is aware of. This code was written
10 years ago so no one has a clue.
It gives an error that says:
Run-time error "9:
Subscript out of range
The offending line of code is highlighted below where it says "error
here>>>>>"
I have seen some posts about sheet and workbook naming giving these
issues, but this line does not use workbook and worksheet names or even
references, it just uses ActiveSheet
any help is appreciated
'
'
'
Sub UpdateStateVariables()
Const STATE_NAME_ROW_OFFSET As Integer = -2
Dim lengthColNum As Integer
Dim enumeratedColNum As Integer
Dim responseColNum As Integer
Dim equivalenceColNum As Integer
Dim lengthCol As Range ' Column containing enum
lengths
Dim enumeratedCol As Range ' Column containing enums
Dim responseCol As Range ' Column containing responses
Dim equivalenceCol As Range ' Column containing
equivalence/ILLEGAL
Dim stimuliList() As String ' List of all stimulus
Dim priorEnumLength As String ' History length of prior
enumeration
' Row numbers are relative from first row of enumerations (length 1)
unless otherwise noted
Dim numStimuli As Integer ' Total number of stimuli to
enumerate
Dim lastRowOfPriorEnum As Integer ' Last row number of the last
generated enumeration
Dim priorEnumRow As Integer ' Current row number for
sequencing through last generated enumeration
Dim firstRowNumber As Integer ' First row of enumerations
(ABSOLUTE row number)
Dim currentRowOfNextEnum As Integer ' Current row of newly created
enumeration (ABSOLUTE row number)
Dim defaultResponseFlag As Integer
Dim DefaultResponseText As String
Dim DefaultEquivalenceFlag As Integer
Dim DefaultEquivalenceText As String
Dim sequenceText As String
Dim token() As String
Dim numSeqStimuli As Integer
Dim j As Integer
Dim stimNumberIndex As Integer
Dim stimLength As Integer
Dim sequenceRowNum As Integer
Dim beforeFirstRowNum As Integer
Dim quitFunction As Boolean
Dim stateNames(MAX_NUM_STATE_VARIABLES) As String '
Names of the state data variables
Dim numStateValues(MAX_NUM_STATE_VARIABLES) As Integer '
Number of values per variable
Dim stateValues(MAX_NUM_STATE_VARIABLES,
MAX_NUM_VALS_PER_STATE_VARIABLE) As String ' Values of each variable
' The states range from 1 .. max state, the values range from 1 to
max value
Dim stateValuesForCurrentRow(MAX_NUM_STATE_VARIABLES) As String '
Values of state data variables for current row
Application.StatusBar = "Creating the State Box..."
Call ShowAll
Call UnStimulusSort ' Must be sorted by enumeration length for
below to work
Application.ScreenUpdating = False
Call GetEnumerationSpreadsheet(lengthColNum, enumeratedColNum, _
equivalenceColNum, responseColNum,
firstRowNumber, _
lastRowOfPriorEnum,
currentRowOfNextEnum, _
priorEnumLength, numStimuli, _
stimuliList, lengthCol, _
enumeratedCol, responseCol,
equivalenceCol)
Dim StateRow As Range ' Row with the equivalent state
Dim colNum As Integer
Dim newColNum As Integer
Dim commentsColNum As Integer ' This variable is
unused
Dim assumptionsColNum As Integer ' This variable is
unused
Dim firstCurrentStateVariableColNum As Integer
Dim lastCurrentStateVariableColNum As Integer
Dim firstNewStateVariableColNum As Integer
Dim lastNewStateVariableColNum As Integer
Dim firstStateBoxColNum As Integer
Call GetColumnDefinitions(lengthColNum, _
enumeratedColNum, _
responseColNum, _
equivalenceColNum, _
commentsColNum, _
assumptionsColNum, _
firstCurrentStateVariableColNum, _
lastCurrentStateVariableColNum, _
firstNewStateVariableColNum, _
lastNewStateVariableColNum,
firstStateBoxColNum)
If ActiveSheet.Cells(firstRowNumber + STATE_NAME_ROW_OFFSET,
firstCurrentStateVariableColNum).Value <> "State Name" Or _
ActiveSheet.Cells(firstRowNumber + STATE_NAME_ROW_OFFSET,
firstNewStateVariableColNum).Value <> "State Name" Then
If (vbNo = MsgBox("Possible mismatch in columns for state table
(defined by Enumeration Options Dialog Box). Do you want to
continue?", _
vbYesNo + vbQuestion + vbDefaultButton1
+ vbApplicationModal)) Then
Application.ScreenUpdating = True ' Let the screen
update again
Application.StatusBar = False ' Let the toolbar
update again
Exit Sub
End If
End If
Application.StatusBar = "Getting the State Variable Names..."
For colNum = 1 To lastNewStateVariableColNum -
firstNewStateVariableColNum
stateNames(colNum) = ActiveSheet.Cells(firstRowNumber +
STATE_NAME_ROW_OFFSET, firstNewStateVariableColNum + colNum).Value
Next
'============================================================================
'============================================================================
' Update the new state for all equivalenced states to match that
of the unequivalenced state
Application.StatusBar = "Creating all new state variables for all
equivalent states..."
For rowIndex = 1 To lastRowOfPriorEnum
If (Left(equivalenceCol.Rows(rowIndex).Value, 1) = "=") Then
Dim equivalence As String
equivalence = Mid(equivalenceCol.Rows(rowIndex).Value, 2)
Do While Left(equivalence, 1) = " "
equivalence = Mid(equivalence, 2)
Loop
error here>>>>>> Set StateRow =
Columns(enumeratedColNum).Find(what:=equivalence, MatchCase:=True,
after:=ActiveSheet.Cells(1, enumeratedColNum), lookat:=xlWhole,
lookin:=xlValue)
If StateRow Is Nothing Then
MsgBox "Equivalence '" &
Mid(equivalenceCol.Rows(rowIndex).Value, 2) & "' (at line number " &
Str(firstRowNumber + rowIndex - 1) & ")" & " was not found"
quitFunction = True
Else
For colNum = firstNewStateVariableColNum To
lastNewStateVariableColNum
stateValuesForCurrentRow(colNum) =
ActiveSheet.Cells(StateRow.Row, colNum).Value
Next
For colNum = firstNewStateVariableColNum To
lastNewStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
colNum).Value _
= stateValuesForCurrentRow(colNum)
Next
End If
ElseIf (UCase(equivalenceCol.Rows(rowIndex).Value) = "ILLEGAL")
Then
For colNum = firstNewStateVariableColNum To
lastNewStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
colNum).Value _
= NOT_APPLICABLE
Next
End If
Next
If quitFunction = True Then
MsgBox "Errors were encountered. Check spelling,
capitalization, and spaces. Then run this function again."
Application.ScreenUpdating = True ' Let the screen update
again
Application.StatusBar = False ' Let the toolbar update again
Exit Sub
End If
'============================================================================
'============================================================================
'============================================================================
'============================================================================
'Update the current state for all states
Application.StatusBar = "Creating all current state variables for
all states..."
For rowIndex = 1 To lastRowOfPriorEnum
Dim enumeration As String
enumeration =
GetUpToLastToken(enumeratedCol.Rows(rowIndex).Value)
Set StateRow =
Columns(enumeratedColNum).Find(what:=enumeration, MatchCase:=True,
after:=ActiveSheet.Cells(1, enumeratedColNum), lookat:=xlWhole,
lookin:=xlValue)
If ((StateRow Is Nothing) Or (enumeration = "")) Then ' Get the
values from the "" state
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
stateValuesForCurrentRow(colNum) =
ActiveSheet.Cells(firstRowNumber - 1, _
firstNewStateVariableColNum + colNum).Value
Next
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
firstCurrentStateVariableColNum + colNum).Value _
= stateValuesForCurrentRow(colNum)
Next
Else ' Get the values from the prior state
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
stateValuesForCurrentRow(colNum) =
ActiveSheet.Cells(StateRow.Row, firstNewStateVariableColNum +
colNum).Value
Next
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
firstCurrentStateVariableColNum + colNum).Value _
= stateValuesForCurrentRow(colNum)
Next
End If
Next
'============================================================================
'============================================================================
'============================================================================
'============================================================================
'Get all the state values
Call GetStateValues(firstRowNumber, lastRowOfPriorEnum, _
firstCurrentStateVariableColNum,
lastCurrentStateVariableColNum, _
numStateValues, stateValues)
Call OutputStateValues(firstRowNumber + lastRowOfPriorEnum, _
firstCurrentStateVariableColNum,
lastCurrentStateVariableColNum, _
numStateValues, stateValues)
' Clear the contents of the state box
Dim eraseStateBoxFlag As Integer
Call GetStateBoxOptions(eraseStateBoxFlag)
If eraseStateBoxFlag = 1 Then
Columns(firstStateBoxColNum + currentStateOffset).ClearContents
Columns(firstStateBoxColNum + newStateOffset).ClearContents
Columns(firstStateBoxColNum +
changedDataStateOffset).ClearContents
Columns(firstStateBoxColNum +
responseStateOffset).ClearContents
Columns(firstStateBoxColNum + puiStateOffset).ClearContents
Columns(firstStateBoxColNum +
commentsStateOffset).ClearContents
End If
'============================================================================
'============================================================================
'============================================================================
'============================================================================
' Build the transition matrix and shade all variables that have
changed or that are N/A
Application.StatusBar = "Building transition matrix for current to
new state..."
Call StimulusSort ' Must be sorted by enumeration length for
below to work
For colNum = firstCurrentStateVariableColNum To
lastCurrentStateVariableColNum
ActiveSheet.Columns.Interior.ColorIndex = xlNone
Next
Call OutlineStateVariables
Dim enumRow As Integer
Dim firstStateBoxRowNum As Integer
enumRow = firstRowNumber
firstStateBoxRowNum = 0
For stimNumberIndex = 1 To numStimuli
Call CalcStateBox(enumRow, enumeratedColNum, responseColNum,
equivalenceColNum, _
firstCurrentStateVariableColNum,
lastCurrentStateVariableColNum, _
firstNewStateVariableColNum,
lastNewStateVariableColNum, _
firstStateBoxColNum, firstStateBoxRowNum,
stateNames, numStateValues, stateValues)
Next
'============================================================================
'============================================================================
Call OutlineStateBox(numColsInStateBox)
Application.StatusBar = "Done creating state box"
Application.ScreenUpdating = True ' Let the screen update
again
Application.StatusBar = False ' Let the toolbar update
again
End Sub
is not my code so I'm not really much help with the logic in it, hoping
its just a generic problem someone is aware of. This code was written
10 years ago so no one has a clue.
It gives an error that says:
Run-time error "9:
Subscript out of range
The offending line of code is highlighted below where it says "error
here>>>>>"
I have seen some posts about sheet and workbook naming giving these
issues, but this line does not use workbook and worksheet names or even
references, it just uses ActiveSheet
any help is appreciated
'
'
'
Sub UpdateStateVariables()
Const STATE_NAME_ROW_OFFSET As Integer = -2
Dim lengthColNum As Integer
Dim enumeratedColNum As Integer
Dim responseColNum As Integer
Dim equivalenceColNum As Integer
Dim lengthCol As Range ' Column containing enum
lengths
Dim enumeratedCol As Range ' Column containing enums
Dim responseCol As Range ' Column containing responses
Dim equivalenceCol As Range ' Column containing
equivalence/ILLEGAL
Dim stimuliList() As String ' List of all stimulus
Dim priorEnumLength As String ' History length of prior
enumeration
' Row numbers are relative from first row of enumerations (length 1)
unless otherwise noted
Dim numStimuli As Integer ' Total number of stimuli to
enumerate
Dim lastRowOfPriorEnum As Integer ' Last row number of the last
generated enumeration
Dim priorEnumRow As Integer ' Current row number for
sequencing through last generated enumeration
Dim firstRowNumber As Integer ' First row of enumerations
(ABSOLUTE row number)
Dim currentRowOfNextEnum As Integer ' Current row of newly created
enumeration (ABSOLUTE row number)
Dim defaultResponseFlag As Integer
Dim DefaultResponseText As String
Dim DefaultEquivalenceFlag As Integer
Dim DefaultEquivalenceText As String
Dim sequenceText As String
Dim token() As String
Dim numSeqStimuli As Integer
Dim j As Integer
Dim stimNumberIndex As Integer
Dim stimLength As Integer
Dim sequenceRowNum As Integer
Dim beforeFirstRowNum As Integer
Dim quitFunction As Boolean
Dim stateNames(MAX_NUM_STATE_VARIABLES) As String '
Names of the state data variables
Dim numStateValues(MAX_NUM_STATE_VARIABLES) As Integer '
Number of values per variable
Dim stateValues(MAX_NUM_STATE_VARIABLES,
MAX_NUM_VALS_PER_STATE_VARIABLE) As String ' Values of each variable
' The states range from 1 .. max state, the values range from 1 to
max value
Dim stateValuesForCurrentRow(MAX_NUM_STATE_VARIABLES) As String '
Values of state data variables for current row
Application.StatusBar = "Creating the State Box..."
Call ShowAll
Call UnStimulusSort ' Must be sorted by enumeration length for
below to work
Application.ScreenUpdating = False
Call GetEnumerationSpreadsheet(lengthColNum, enumeratedColNum, _
equivalenceColNum, responseColNum,
firstRowNumber, _
lastRowOfPriorEnum,
currentRowOfNextEnum, _
priorEnumLength, numStimuli, _
stimuliList, lengthCol, _
enumeratedCol, responseCol,
equivalenceCol)
Dim StateRow As Range ' Row with the equivalent state
Dim colNum As Integer
Dim newColNum As Integer
Dim commentsColNum As Integer ' This variable is
unused
Dim assumptionsColNum As Integer ' This variable is
unused
Dim firstCurrentStateVariableColNum As Integer
Dim lastCurrentStateVariableColNum As Integer
Dim firstNewStateVariableColNum As Integer
Dim lastNewStateVariableColNum As Integer
Dim firstStateBoxColNum As Integer
Call GetColumnDefinitions(lengthColNum, _
enumeratedColNum, _
responseColNum, _
equivalenceColNum, _
commentsColNum, _
assumptionsColNum, _
firstCurrentStateVariableColNum, _
lastCurrentStateVariableColNum, _
firstNewStateVariableColNum, _
lastNewStateVariableColNum,
firstStateBoxColNum)
If ActiveSheet.Cells(firstRowNumber + STATE_NAME_ROW_OFFSET,
firstCurrentStateVariableColNum).Value <> "State Name" Or _
ActiveSheet.Cells(firstRowNumber + STATE_NAME_ROW_OFFSET,
firstNewStateVariableColNum).Value <> "State Name" Then
If (vbNo = MsgBox("Possible mismatch in columns for state table
(defined by Enumeration Options Dialog Box). Do you want to
continue?", _
vbYesNo + vbQuestion + vbDefaultButton1
+ vbApplicationModal)) Then
Application.ScreenUpdating = True ' Let the screen
update again
Application.StatusBar = False ' Let the toolbar
update again
Exit Sub
End If
End If
Application.StatusBar = "Getting the State Variable Names..."
For colNum = 1 To lastNewStateVariableColNum -
firstNewStateVariableColNum
stateNames(colNum) = ActiveSheet.Cells(firstRowNumber +
STATE_NAME_ROW_OFFSET, firstNewStateVariableColNum + colNum).Value
Next
'============================================================================
'============================================================================
' Update the new state for all equivalenced states to match that
of the unequivalenced state
Application.StatusBar = "Creating all new state variables for all
equivalent states..."
For rowIndex = 1 To lastRowOfPriorEnum
If (Left(equivalenceCol.Rows(rowIndex).Value, 1) = "=") Then
Dim equivalence As String
equivalence = Mid(equivalenceCol.Rows(rowIndex).Value, 2)
Do While Left(equivalence, 1) = " "
equivalence = Mid(equivalence, 2)
Loop
error here>>>>>> Set StateRow =
Columns(enumeratedColNum).Find(what:=equivalence, MatchCase:=True,
after:=ActiveSheet.Cells(1, enumeratedColNum), lookat:=xlWhole,
lookin:=xlValue)
If StateRow Is Nothing Then
MsgBox "Equivalence '" &
Mid(equivalenceCol.Rows(rowIndex).Value, 2) & "' (at line number " &
Str(firstRowNumber + rowIndex - 1) & ")" & " was not found"
quitFunction = True
Else
For colNum = firstNewStateVariableColNum To
lastNewStateVariableColNum
stateValuesForCurrentRow(colNum) =
ActiveSheet.Cells(StateRow.Row, colNum).Value
Next
For colNum = firstNewStateVariableColNum To
lastNewStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
colNum).Value _
= stateValuesForCurrentRow(colNum)
Next
End If
ElseIf (UCase(equivalenceCol.Rows(rowIndex).Value) = "ILLEGAL")
Then
For colNum = firstNewStateVariableColNum To
lastNewStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
colNum).Value _
= NOT_APPLICABLE
Next
End If
Next
If quitFunction = True Then
MsgBox "Errors were encountered. Check spelling,
capitalization, and spaces. Then run this function again."
Application.ScreenUpdating = True ' Let the screen update
again
Application.StatusBar = False ' Let the toolbar update again
Exit Sub
End If
'============================================================================
'============================================================================
'============================================================================
'============================================================================
'Update the current state for all states
Application.StatusBar = "Creating all current state variables for
all states..."
For rowIndex = 1 To lastRowOfPriorEnum
Dim enumeration As String
enumeration =
GetUpToLastToken(enumeratedCol.Rows(rowIndex).Value)
Set StateRow =
Columns(enumeratedColNum).Find(what:=enumeration, MatchCase:=True,
after:=ActiveSheet.Cells(1, enumeratedColNum), lookat:=xlWhole,
lookin:=xlValue)
If ((StateRow Is Nothing) Or (enumeration = "")) Then ' Get the
values from the "" state
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
stateValuesForCurrentRow(colNum) =
ActiveSheet.Cells(firstRowNumber - 1, _
firstNewStateVariableColNum + colNum).Value
Next
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
firstCurrentStateVariableColNum + colNum).Value _
= stateValuesForCurrentRow(colNum)
Next
Else ' Get the values from the prior state
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
stateValuesForCurrentRow(colNum) =
ActiveSheet.Cells(StateRow.Row, firstNewStateVariableColNum +
colNum).Value
Next
For colNum = 0 To lastCurrentStateVariableColNum -
firstCurrentStateVariableColNum
ActiveSheet.Cells(firstRowNumber + rowIndex - 1,
firstCurrentStateVariableColNum + colNum).Value _
= stateValuesForCurrentRow(colNum)
Next
End If
Next
'============================================================================
'============================================================================
'============================================================================
'============================================================================
'Get all the state values
Call GetStateValues(firstRowNumber, lastRowOfPriorEnum, _
firstCurrentStateVariableColNum,
lastCurrentStateVariableColNum, _
numStateValues, stateValues)
Call OutputStateValues(firstRowNumber + lastRowOfPriorEnum, _
firstCurrentStateVariableColNum,
lastCurrentStateVariableColNum, _
numStateValues, stateValues)
' Clear the contents of the state box
Dim eraseStateBoxFlag As Integer
Call GetStateBoxOptions(eraseStateBoxFlag)
If eraseStateBoxFlag = 1 Then
Columns(firstStateBoxColNum + currentStateOffset).ClearContents
Columns(firstStateBoxColNum + newStateOffset).ClearContents
Columns(firstStateBoxColNum +
changedDataStateOffset).ClearContents
Columns(firstStateBoxColNum +
responseStateOffset).ClearContents
Columns(firstStateBoxColNum + puiStateOffset).ClearContents
Columns(firstStateBoxColNum +
commentsStateOffset).ClearContents
End If
'============================================================================
'============================================================================
'============================================================================
'============================================================================
' Build the transition matrix and shade all variables that have
changed or that are N/A
Application.StatusBar = "Building transition matrix for current to
new state..."
Call StimulusSort ' Must be sorted by enumeration length for
below to work
For colNum = firstCurrentStateVariableColNum To
lastCurrentStateVariableColNum
ActiveSheet.Columns.Interior.ColorIndex = xlNone
Next
Call OutlineStateVariables
Dim enumRow As Integer
Dim firstStateBoxRowNum As Integer
enumRow = firstRowNumber
firstStateBoxRowNum = 0
For stimNumberIndex = 1 To numStimuli
Call CalcStateBox(enumRow, enumeratedColNum, responseColNum,
equivalenceColNum, _
firstCurrentStateVariableColNum,
lastCurrentStateVariableColNum, _
firstNewStateVariableColNum,
lastNewStateVariableColNum, _
firstStateBoxColNum, firstStateBoxRowNum,
stateNames, numStateValues, stateValues)
Next
'============================================================================
'============================================================================
Call OutlineStateBox(numColsInStateBox)
Application.StatusBar = "Done creating state box"
Application.ScreenUpdating = True ' Let the screen update
again
Application.StatusBar = False ' Let the toolbar update
again
End Sub