I can't find the solution to my problem...

P

Paul

So I decided to post it here. I have read as many message boards as I
could find about the error I continue to receive. "Compile Error: Only
comments may appear after End Sub, End Function, or End Property" I
have seen posts referring to either APIs or Function Declarations. I
do not have either of these in my code. I also saw a post that
recommended keping the size of the module below 64k. My code was less
than 50k, but to be on the safe side I split it up to two modules and
adjusted the code accordingly. I still get the error. Excel is
highlighting the first line of the last Sub in the module. I will post
all of module1 and two of the subs from module2. I can post the last
function if it is deemed necessary, I just didn't want this post to be
longer than it had to be. Cheers!

'Module1 Code:

Public Type activityType
exist As Boolean
End Type

Public Type levelType
exist As Boolean
End Type

Public Type strandType
exist As Boolean
activity(1 To 10) As activityType
End Type

Public Type stationType
exist As Boolean
level(1 To 4) As levelType
End Type

Public Type dayType
exist As Boolean
strand(1 To 4) As strandType
End Type

Public Type readingLessonType
exist As Boolean
station(1 To 10) As stationType
days(1 To 5) As dayType
End Type

Public Type lessonType
exist As Boolean
End Type

Public Type chapterType
exist As Boolean
lesson(50) As lessonType
End Type

Public Type unitType
exist As Boolean
chapter(50) As chapterType
End Type

Public Type readingUnitType
exist As Boolean
readingLesson(10) As readingLessonType
End Type
Public Function OpenFile() As Variant
Dim filex As Variant
filex = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
OpenFile = filex
End Function
Public Function StripFileName(ByRef filename As Variant, ByVal cell As
Integer) As String
Dim myArray As Variant
myArray = Split(filename(cell), "\") 'Remove the tree
structure from the filename
filename(cell) = myArray(UBound(myArray)) 'Store JUST the
file name
myArray = Split(myArray(UBound(myArray)), "_") 'Remove _'s
myArray = Split(myArray(UBound(myArray)), ".") 'Remove the .'s
leaving just the file name and xls
StripFileName = myArray(0) 'Return just the
file name
End Function
Public Function OrderFileList(ByRef fileList As Variant) As Variant
Dim strTempList(13) As String
Dim orgFileList As Variant
Dim strTemp As String
Dim i As Integer
Dim isReading As Boolean

orgFileList = fileList
isReading = IsItReading()

For i = 1 To UBound(fileList)
strTemp = StripFileName(fileList, i)
strTempList(i - 1) = strTemp
Next
i = FindFileName(strTempList, "eplanner")
orgFileList(1) = fileList(i)
i = FindFileName(strTempList, "book")
orgFileList(2) = fileList(i)
i = FindFileName(strTempList, "unit")
orgFileList(3) = fileList(i)

If isReading Then
i = FindFileName(strTempList, "lesson")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "day")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "strand")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "activity")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "station")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "level")
orgFileList(9) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(10) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(11) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(12) = fileList(i)
i = FindFileName(strTempList, "resactivity")
orgFileList(13) = fileList(i)
i = FindFileName(strTempList, "resstationactivity")
orgFileList(14) = fileList(i)
Else
i = FindFileName(strTempList, "chapter")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "lesson")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "reschapter")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(9) = fileList(i)
End If

OrderFileList = orgFileList
End Function
Public Function CheckForErrors(ByVal strFileType As String, ByVal
strFileName As String)
Dim intFlag As Integer
Dim isReading As Boolean

Module2.SwapToFile (strFileName)
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

isReading = IsItReading()

'The junk variable is required because a sub can't be used if
passing multiple parameters
'and a function is required to be = to something
junk = CheckData(LastRow, strFileName, "ISBN") 'Always
check the ISBN column
Select Case strFileType 'Determine which
file we are checking.
'Each case has the
"checkable" columns underneath it
Case "lesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "reslesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resunit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "unit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "PACING")
Case "book"
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "chapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "eplanner"
junk = CheckData(LastRow, strFileName, "SUBJECT")
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "resbook"
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "reschapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "strand"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
Case "station"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
Case "level"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
Case "day"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
Case "activity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
Case "resactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resstationactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case Else
MsgBox ("Unrecognized file type. The error checker has not
been " + vbCrLf + _
"adapted to this file type: " + vbCrLf + vbCrLf + vbTab +
vbTab + filetype)
End Select
Module2.SwapToFile (strFileName)
ActiveWorkbook.Close False
End Function
Private Function FindFileName(ByVal fileList As Variant, ByVal
strFileType As String) As Integer
Dim i As Integer
For i = 0 To UBound(fileList)
If fileList(i) = strFileType Then
FindFileName = i + 1
Exit For
End If
Next
End Function
Private Function CheckData(ByVal intBottomOfInfo As Integer, ByVal
strFile As String, ByVal strCol As String)
'Checks the imported column (strCol)to make sure that it conforms to
the correct format of the respective column

'including no inappropriate spaces
Dim test, test2, test3, flag As Boolean
Dim strTemp, strTest, strTest2, strTest3 As String

Select Case strCol
Case "ISBN" 'ISBN column
strTest = "##########"
strTest2 = "#############"
strTest3 = "#########[0-Z]"
Case "SYLLABUS_ITEM_ID", "CONTENT_ID", "BOOK_ID"
'Syllabus_Item_ID, Content_ID, Book_ID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]"
+ _

"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "STARS_GUID" 'Stars_GUID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z]-"
+ _

"[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "PACING" 'Pacing
strTest = "#"
strTest2 = "#.#"
strTest3 = "##.#"
Case "RES_TYPE_ID", "RES_CAT_ID" 'RESOURCE IDs
junk = CheckRES_ID(intBottomOfInfo, strCol, strFile)
Exit Function
Case Else 'Any plain
numeric column
strTest = "#"
strTest2 = "##"
strTest3 = "###"
End Select
Module2.SwapToFile (strFile)
intColNum = FindWhichColumn(strCol)
For intRowNum = 2 To intBottomOfInfo
flag = True
strTemp = Cells(intRowNum, intColNum).Value2
test = strTemp Like strTest
test2 = strTemp Like strTest2
test3 = strTemp Like strTest3
If Cells(intRowNum, intColNum) = "" Then
strTemp = strFile & ": There is no number in column: " &
strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
ElseIf (Not test) And (Not test2) And (Not test3) Then
strTemp = strFile & ": Improper number format in column: "
& strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
End If
If strCol = "ISBN" And flag Then
If Cells(intRowNum, intColNum).Value2 <> Cells(2, 1).Value2
Then
strTemp = strFile & ": There is a differing " & strCol
& " in row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
End If
Next
Module2.SwapToErrorChecker
End Function
Private Function CheckRES_ID(ByVal intBottomOfInfo As Integer, ByVal
strCol As String, ByVal strFile As String)
Dim ResList(50), strTemp As String 'Chose the array to be 50
elements because that would cover both RES lists
Dim intRow, intCol, i, intURICol As Integer 'with some room
for growth.
intRow = 1
Module2.SwapToErrorChecker
Sheets(strCol).Activate

Cells(intRow, 1).Select
While Cells(intRow, 1).Value2 <> ""
ResList(intRow) = Cells(intRow, 1).Value2
intRow = intRow + 1
Wend
ResList(intRow + 1) = "End of List" 'Insert manual EOF
Module2.SwapToFile (strFile)

intRow = 2
intCol = FindWhichColumn(strCol)
intURICol = FindWhichColumn("URI")
For intRow = 2 To intBottomOfInfo
i = 1
Do While ResList(i) <> "End of List"
If Cells(intRow, intCol).Value2 = ResList(i) Then
If ResList(i) = "D661E0B7264D1B55E034080020A7D594" Or
ResList(i) = "D661E0B7264E1B55E034080020A7D594" Then
If Cells(intRow, intURICol).Value2 = "" Then
strTemp = strFile & ": Resource listed without
corresponding URI on row: " & intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 1)
Module2.SwapToFile (strFile)
End If
End If
Exit Do
Else
i = i + 1
End If
If ResList(i) = "End of List" Then
strTemp = strFile & ": Invalid " & strCol & " in row: "
& intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
Loop
intRow = intRow + 1
Next
Module2.SwapToErrorChecker
End Function
Private Function FindWhichColumn(ByVal strColName As String) As Integer
Dim intColNum As Integer
intColNum = 1
While Cells(1, intColNum).Value2 <> ""
If Cells(1, intColNum).Value2 = strColName Then
FindWhichColumn = intColNum
Exit Function
End If
intColNum = intColNum + 1
Wend
FindWhichColumn = 255
End Function
Private Function InsertNextMessage(ByVal strError As String, ByVal
choice As Integer)
'Used to insert an error message in the proper location, "Possible
Errors" or "Warnings" based on "choice"
'choice = 0 <--- Possible Error
'choice = 1 <--- Warning
Dim strSearch As String
Select Case choice
Case 0
strSearch = "Possible Errors"
Case 1
strSearch = "Warnings"
End Select
CurrCell = Cells.Find(What:=strSearch, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

If Cells(Selection.Row + 1, 1).Value2 <> "No errors found." Then
'test to see if entering first error/warning
If Cells(Selection.Row + 2, 1).Value2 <> "" Then 'test
to see if entering second error/warning
Selection.End(xlDown).Offset(1, 0).Select
Rows(Selection.Row).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
Else
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
End If
Else
Cells(Selection.Row + 1, 1).Value2 = strError
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
End If
End Function
Private Function IsItReading() As Boolean
Dim isReading As Boolean
intFlag = FindWhichColumn("THEME_NUMBER") 'Test to determine if
it is a reading group if 'THEME_NUMBER' doesn't exist
If intFlag <> 32767 Then 'intFlag is set to
32767
If Cells(2, intFlag).Value2 = "" Then
intFlag = 32767
isReading = False
Else
isReading = True
End If
Else
isReading = False
End If
IsItReading = isReading
End Function


'Module2 Code:

Public Sub SwapToErrorChecker()
Windows("ErrorChecker.xls").Activate
Sheets("Errors").Activate
End Sub

Public Sub SwapToFile(ByVal strFile As String)
Windows(strFile).Activate
Sheets(1).Activate
End Sub
 
J

Jeff Standen

Which is the one you are getting the error on - is it the last sub of module
1 or module 2? If it is 2 it would help if that were posted. Perhaps you
spelt Sub wrong :)

FYI Subs can have more than one argument - just when you call them, don't
put brackets round the arguments. Also functions can be called without a
variable and '=' - just use the keyword 'Call' in front of the function
call.

Jeff

Paul said:
So I decided to post it here. I have read as many message boards as I
could find about the error I continue to receive. "Compile Error: Only
comments may appear after End Sub, End Function, or End Property" I
have seen posts referring to either APIs or Function Declarations. I
do not have either of these in my code. I also saw a post that
recommended keping the size of the module below 64k. My code was less
than 50k, but to be on the safe side I split it up to two modules and
adjusted the code accordingly. I still get the error. Excel is
highlighting the first line of the last Sub in the module. I will post
all of module1 and two of the subs from module2. I can post the last
function if it is deemed necessary, I just didn't want this post to be
longer than it had to be. Cheers!

'Module1 Code:

Public Type activityType
exist As Boolean
End Type

Public Type levelType
exist As Boolean
End Type

Public Type strandType
exist As Boolean
activity(1 To 10) As activityType
End Type

Public Type stationType
exist As Boolean
level(1 To 4) As levelType
End Type

Public Type dayType
exist As Boolean
strand(1 To 4) As strandType
End Type

Public Type readingLessonType
exist As Boolean
station(1 To 10) As stationType
days(1 To 5) As dayType
End Type

Public Type lessonType
exist As Boolean
End Type

Public Type chapterType
exist As Boolean
lesson(50) As lessonType
End Type

Public Type unitType
exist As Boolean
chapter(50) As chapterType
End Type

Public Type readingUnitType
exist As Boolean
readingLesson(10) As readingLessonType
End Type
Public Function OpenFile() As Variant
Dim filex As Variant
filex = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
OpenFile = filex
End Function
Public Function StripFileName(ByRef filename As Variant, ByVal cell As
Integer) As String
Dim myArray As Variant
myArray = Split(filename(cell), "\") 'Remove the tree
structure from the filename
filename(cell) = myArray(UBound(myArray)) 'Store JUST the
file name
myArray = Split(myArray(UBound(myArray)), "_") 'Remove _'s
myArray = Split(myArray(UBound(myArray)), ".") 'Remove the .'s
leaving just the file name and xls
StripFileName = myArray(0) 'Return just the
file name
End Function
Public Function OrderFileList(ByRef fileList As Variant) As Variant
Dim strTempList(13) As String
Dim orgFileList As Variant
Dim strTemp As String
Dim i As Integer
Dim isReading As Boolean

orgFileList = fileList
isReading = IsItReading()

For i = 1 To UBound(fileList)
strTemp = StripFileName(fileList, i)
strTempList(i - 1) = strTemp
Next
i = FindFileName(strTempList, "eplanner")
orgFileList(1) = fileList(i)
i = FindFileName(strTempList, "book")
orgFileList(2) = fileList(i)
i = FindFileName(strTempList, "unit")
orgFileList(3) = fileList(i)

If isReading Then
i = FindFileName(strTempList, "lesson")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "day")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "strand")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "activity")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "station")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "level")
orgFileList(9) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(10) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(11) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(12) = fileList(i)
i = FindFileName(strTempList, "resactivity")
orgFileList(13) = fileList(i)
i = FindFileName(strTempList, "resstationactivity")
orgFileList(14) = fileList(i)
Else
i = FindFileName(strTempList, "chapter")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "lesson")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "reschapter")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(9) = fileList(i)
End If

OrderFileList = orgFileList
End Function
Public Function CheckForErrors(ByVal strFileType As String, ByVal
strFileName As String)
Dim intFlag As Integer
Dim isReading As Boolean

Module2.SwapToFile (strFileName)
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

isReading = IsItReading()

'The junk variable is required because a sub can't be used if
passing multiple parameters
'and a function is required to be = to something
junk = CheckData(LastRow, strFileName, "ISBN") 'Always
check the ISBN column
Select Case strFileType 'Determine which
file we are checking.
'Each case has the
"checkable" columns underneath it
Case "lesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "reslesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resunit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "unit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "PACING")
Case "book"
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "chapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "eplanner"
junk = CheckData(LastRow, strFileName, "SUBJECT")
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "resbook"
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "reschapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "strand"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
Case "station"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
Case "level"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
Case "day"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
Case "activity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
Case "resactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resstationactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case Else
MsgBox ("Unrecognized file type. The error checker has not
been " + vbCrLf + _
"adapted to this file type: " + vbCrLf + vbCrLf + vbTab +
vbTab + filetype)
End Select
Module2.SwapToFile (strFileName)
ActiveWorkbook.Close False
End Function
Private Function FindFileName(ByVal fileList As Variant, ByVal
strFileType As String) As Integer
Dim i As Integer
For i = 0 To UBound(fileList)
If fileList(i) = strFileType Then
FindFileName = i + 1
Exit For
End If
Next
End Function
Private Function CheckData(ByVal intBottomOfInfo As Integer, ByVal
strFile As String, ByVal strCol As String)
'Checks the imported column (strCol)to make sure that it conforms to
the correct format of the respective column

'including no inappropriate spaces
Dim test, test2, test3, flag As Boolean
Dim strTemp, strTest, strTest2, strTest3 As String

Select Case strCol
Case "ISBN" 'ISBN column
strTest = "##########"
strTest2 = "#############"
strTest3 = "#########[0-Z]"
Case "SYLLABUS_ITEM_ID", "CONTENT_ID", "BOOK_ID"
'Syllabus_Item_ID, Content_ID, Book_ID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]"
+ _

"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "STARS_GUID" 'Stars_GUID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z]-"
+ _

"[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "PACING" 'Pacing
strTest = "#"
strTest2 = "#.#"
strTest3 = "##.#"
Case "RES_TYPE_ID", "RES_CAT_ID" 'RESOURCE IDs
junk = CheckRES_ID(intBottomOfInfo, strCol, strFile)
Exit Function
Case Else 'Any plain
numeric column
strTest = "#"
strTest2 = "##"
strTest3 = "###"
End Select
Module2.SwapToFile (strFile)
intColNum = FindWhichColumn(strCol)
For intRowNum = 2 To intBottomOfInfo
flag = True
strTemp = Cells(intRowNum, intColNum).Value2
test = strTemp Like strTest
test2 = strTemp Like strTest2
test3 = strTemp Like strTest3
If Cells(intRowNum, intColNum) = "" Then
strTemp = strFile & ": There is no number in column: " &
strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
ElseIf (Not test) And (Not test2) And (Not test3) Then
strTemp = strFile & ": Improper number format in column: "
& strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
End If
If strCol = "ISBN" And flag Then
If Cells(intRowNum, intColNum).Value2 <> Cells(2, 1).Value2
Then
strTemp = strFile & ": There is a differing " & strCol
& " in row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
End If
Next
Module2.SwapToErrorChecker
End Function
Private Function CheckRES_ID(ByVal intBottomOfInfo As Integer, ByVal
strCol As String, ByVal strFile As String)
Dim ResList(50), strTemp As String 'Chose the array to be 50
elements because that would cover both RES lists
Dim intRow, intCol, i, intURICol As Integer 'with some room
for growth.
intRow = 1
Module2.SwapToErrorChecker
Sheets(strCol).Activate

Cells(intRow, 1).Select
While Cells(intRow, 1).Value2 <> ""
ResList(intRow) = Cells(intRow, 1).Value2
intRow = intRow + 1
Wend
ResList(intRow + 1) = "End of List" 'Insert manual EOF
Module2.SwapToFile (strFile)

intRow = 2
intCol = FindWhichColumn(strCol)
intURICol = FindWhichColumn("URI")
For intRow = 2 To intBottomOfInfo
i = 1
Do While ResList(i) <> "End of List"
If Cells(intRow, intCol).Value2 = ResList(i) Then
If ResList(i) = "D661E0B7264D1B55E034080020A7D594" Or
ResList(i) = "D661E0B7264E1B55E034080020A7D594" Then
If Cells(intRow, intURICol).Value2 = "" Then
strTemp = strFile & ": Resource listed without
corresponding URI on row: " & intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 1)
Module2.SwapToFile (strFile)
End If
End If
Exit Do
Else
i = i + 1
End If
If ResList(i) = "End of List" Then
strTemp = strFile & ": Invalid " & strCol & " in row: "
& intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
Loop
intRow = intRow + 1
Next
Module2.SwapToErrorChecker
End Function
Private Function FindWhichColumn(ByVal strColName As String) As Integer
Dim intColNum As Integer
intColNum = 1
While Cells(1, intColNum).Value2 <> ""
If Cells(1, intColNum).Value2 = strColName Then
FindWhichColumn = intColNum
Exit Function
End If
intColNum = intColNum + 1
Wend
FindWhichColumn = 255
End Function
Private Function InsertNextMessage(ByVal strError As String, ByVal
choice As Integer)
'Used to insert an error message in the proper location, "Possible
Errors" or "Warnings" based on "choice"
'choice = 0 <--- Possible Error
'choice = 1 <--- Warning
Dim strSearch As String
Select Case choice
Case 0
strSearch = "Possible Errors"
Case 1
strSearch = "Warnings"
End Select
CurrCell = Cells.Find(What:=strSearch, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

If Cells(Selection.Row + 1, 1).Value2 <> "No errors found." Then
'test to see if entering first error/warning
If Cells(Selection.Row + 2, 1).Value2 <> "" Then 'test
to see if entering second error/warning
Selection.End(xlDown).Offset(1, 0).Select
Rows(Selection.Row).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
Else
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
End If
Else
Cells(Selection.Row + 1, 1).Value2 = strError
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
End If
End Function
Private Function IsItReading() As Boolean
Dim isReading As Boolean
intFlag = FindWhichColumn("THEME_NUMBER") 'Test to determine if
it is a reading group if 'THEME_NUMBER' doesn't exist
If intFlag <> 32767 Then 'intFlag is set to
32767
If Cells(2, intFlag).Value2 = "" Then
intFlag = 32767
isReading = False
Else
isReading = True
End If
Else
isReading = False
End If
IsItReading = isReading
End Function


'Module2 Code:

Public Sub SwapToErrorChecker()
Windows("ErrorChecker.xls").Activate
Sheets("Errors").Activate
End Sub

Public Sub SwapToFile(ByVal strFile As String)
Windows(strFile).Activate
Sheets(1).Activate
End Sub
Click to expand...
 
T

Tom Ogilvy

Just to add, if I put a stray character at the bottom of the module, past
all the code, then I can get your error message.

Maybe you have an invisible character such as chr(160) - non-breaking space
somewhere in your module outside a procedure. If you copied something from
a web page an pasted it in, this is a possibility.


--
Regards,
Tom Ogilvy

Paul said:
So I decided to post it here. I have read as many message boards as I
could find about the error I continue to receive. "Compile Error: Only
comments may appear after End Sub, End Function, or End Property" I
have seen posts referring to either APIs or Function Declarations. I
do not have either of these in my code. I also saw a post that
recommended keping the size of the module below 64k. My code was less
than 50k, but to be on the safe side I split it up to two modules and
adjusted the code accordingly. I still get the error. Excel is
highlighting the first line of the last Sub in the module. I will post
all of module1 and two of the subs from module2. I can post the last
function if it is deemed necessary, I just didn't want this post to be
longer than it had to be. Cheers!

'Module1 Code:

Public Type activityType
exist As Boolean
End Type

Public Type levelType
exist As Boolean
End Type

Public Type strandType
exist As Boolean
activity(1 To 10) As activityType
End Type

Public Type stationType
exist As Boolean
level(1 To 4) As levelType
End Type

Public Type dayType
exist As Boolean
strand(1 To 4) As strandType
End Type

Public Type readingLessonType
exist As Boolean
station(1 To 10) As stationType
days(1 To 5) As dayType
End Type

Public Type lessonType
exist As Boolean
End Type

Public Type chapterType
exist As Boolean
lesson(50) As lessonType
End Type

Public Type unitType
exist As Boolean
chapter(50) As chapterType
End Type

Public Type readingUnitType
exist As Boolean
readingLesson(10) As readingLessonType
End Type
Public Function OpenFile() As Variant
Dim filex As Variant
filex = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
OpenFile = filex
End Function
Public Function StripFileName(ByRef filename As Variant, ByVal cell As
Integer) As String
Dim myArray As Variant
myArray = Split(filename(cell), "\") 'Remove the tree
structure from the filename
filename(cell) = myArray(UBound(myArray)) 'Store JUST the
file name
myArray = Split(myArray(UBound(myArray)), "_") 'Remove _'s
myArray = Split(myArray(UBound(myArray)), ".") 'Remove the .'s
leaving just the file name and xls
StripFileName = myArray(0) 'Return just the
file name
End Function
Public Function OrderFileList(ByRef fileList As Variant) As Variant
Dim strTempList(13) As String
Dim orgFileList As Variant
Dim strTemp As String
Dim i As Integer
Dim isReading As Boolean

orgFileList = fileList
isReading = IsItReading()

For i = 1 To UBound(fileList)
strTemp = StripFileName(fileList, i)
strTempList(i - 1) = strTemp
Next
i = FindFileName(strTempList, "eplanner")
orgFileList(1) = fileList(i)
i = FindFileName(strTempList, "book")
orgFileList(2) = fileList(i)
i = FindFileName(strTempList, "unit")
orgFileList(3) = fileList(i)

If isReading Then
i = FindFileName(strTempList, "lesson")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "day")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "strand")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "activity")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "station")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "level")
orgFileList(9) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(10) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(11) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(12) = fileList(i)
i = FindFileName(strTempList, "resactivity")
orgFileList(13) = fileList(i)
i = FindFileName(strTempList, "resstationactivity")
orgFileList(14) = fileList(i)
Else
i = FindFileName(strTempList, "chapter")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "lesson")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "reschapter")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(9) = fileList(i)
End If

OrderFileList = orgFileList
End Function
Public Function CheckForErrors(ByVal strFileType As String, ByVal
strFileName As String)
Dim intFlag As Integer
Dim isReading As Boolean

Module2.SwapToFile (strFileName)
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

isReading = IsItReading()

'The junk variable is required because a sub can't be used if
passing multiple parameters
'and a function is required to be = to something
junk = CheckData(LastRow, strFileName, "ISBN") 'Always
check the ISBN column
Select Case strFileType 'Determine which
file we are checking.
'Each case has the
"checkable" columns underneath it
Case "lesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "reslesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resunit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "unit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "PACING")
Case "book"
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "chapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "eplanner"
junk = CheckData(LastRow, strFileName, "SUBJECT")
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "resbook"
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "reschapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "strand"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
Case "station"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
Case "level"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
Case "day"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
Case "activity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
Case "resactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resstationactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case Else
MsgBox ("Unrecognized file type. The error checker has not
been " + vbCrLf + _
"adapted to this file type: " + vbCrLf + vbCrLf + vbTab +
vbTab + filetype)
End Select
Module2.SwapToFile (strFileName)
ActiveWorkbook.Close False
End Function
Private Function FindFileName(ByVal fileList As Variant, ByVal
strFileType As String) As Integer
Dim i As Integer
For i = 0 To UBound(fileList)
If fileList(i) = strFileType Then
FindFileName = i + 1
Exit For
End If
Next
End Function
Private Function CheckData(ByVal intBottomOfInfo As Integer, ByVal
strFile As String, ByVal strCol As String)
'Checks the imported column (strCol)to make sure that it conforms to
the correct format of the respective column

'including no inappropriate spaces
Dim test, test2, test3, flag As Boolean
Dim strTemp, strTest, strTest2, strTest3 As String

Select Case strCol
Case "ISBN" 'ISBN column
strTest = "##########"
strTest2 = "#############"
strTest3 = "#########[0-Z]"
Case "SYLLABUS_ITEM_ID", "CONTENT_ID", "BOOK_ID"
'Syllabus_Item_ID, Content_ID, Book_ID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]
[0-Z]"
+ _

"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]
[0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "STARS_GUID" 'Stars_GUID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-
Z][0-Z]-"
+ _

"[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z
][0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "PACING" 'Pacing
strTest = "#"
strTest2 = "#.#"
strTest3 = "##.#"
Case "RES_TYPE_ID", "RES_CAT_ID" 'RESOURCE IDs
junk = CheckRES_ID(intBottomOfInfo, strCol, strFile)
Exit Function
Case Else 'Any plain
numeric column
strTest = "#"
strTest2 = "##"
strTest3 = "###"
End Select
Module2.SwapToFile (strFile)
intColNum = FindWhichColumn(strCol)
For intRowNum = 2 To intBottomOfInfo
flag = True
strTemp = Cells(intRowNum, intColNum).Value2
test = strTemp Like strTest
test2 = strTemp Like strTest2
test3 = strTemp Like strTest3
If Cells(intRowNum, intColNum) = "" Then
strTemp = strFile & ": There is no number in column: " &
strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
ElseIf (Not test) And (Not test2) And (Not test3) Then
strTemp = strFile & ": Improper number format in column: "
& strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
End If
If strCol = "ISBN" And flag Then
If Cells(intRowNum, intColNum).Value2 <> Cells(2, 1).Value2
Then
strTemp = strFile & ": There is a differing " & strCol
& " in row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
End If
Next
Module2.SwapToErrorChecker
End Function
Private Function CheckRES_ID(ByVal intBottomOfInfo As Integer, ByVal
strCol As String, ByVal strFile As String)
Dim ResList(50), strTemp As String 'Chose the array to be 50
elements because that would cover both RES lists
Dim intRow, intCol, i, intURICol As Integer 'with some room
for growth.
intRow = 1
Module2.SwapToErrorChecker
Sheets(strCol).Activate

Cells(intRow, 1).Select
While Cells(intRow, 1).Value2 <> ""
ResList(intRow) = Cells(intRow, 1).Value2
intRow = intRow + 1
Wend
ResList(intRow + 1) = "End of List" 'Insert manual EOF
Module2.SwapToFile (strFile)

intRow = 2
intCol = FindWhichColumn(strCol)
intURICol = FindWhichColumn("URI")
For intRow = 2 To intBottomOfInfo
i = 1
Do While ResList(i) <> "End of List"
If Cells(intRow, intCol).Value2 = ResList(i) Then
If ResList(i) = "D661E0B7264D1B55E034080020A7D594" Or
ResList(i) = "D661E0B7264E1B55E034080020A7D594" Then
If Cells(intRow, intURICol).Value2 = "" Then
strTemp = strFile & ": Resource listed without
corresponding URI on row: " & intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 1)
Module2.SwapToFile (strFile)
End If
End If
Exit Do
Else
i = i + 1
End If
If ResList(i) = "End of List" Then
strTemp = strFile & ": Invalid " & strCol & " in row: "
& intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
Loop
intRow = intRow + 1
Next
Module2.SwapToErrorChecker
End Function
Private Function FindWhichColumn(ByVal strColName As String) As Integer
Dim intColNum As Integer
intColNum = 1
While Cells(1, intColNum).Value2 <> ""
If Cells(1, intColNum).Value2 = strColName Then
FindWhichColumn = intColNum
Exit Function
End If
intColNum = intColNum + 1
Wend
FindWhichColumn = 255
End Function
Private Function InsertNextMessage(ByVal strError As String, ByVal
choice As Integer)
'Used to insert an error message in the proper location, "Possible
Errors" or "Warnings" based on "choice"
'choice = 0 <--- Possible Error
'choice = 1 <--- Warning
Dim strSearch As String
Select Case choice
Case 0
strSearch = "Possible Errors"
Case 1
strSearch = "Warnings"
End Select
CurrCell = Cells.Find(What:=strSearch, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

If Cells(Selection.Row + 1, 1).Value2 <> "No errors found." Then
'test to see if entering first error/warning
If Cells(Selection.Row + 2, 1).Value2 <> "" Then 'test
to see if entering second error/warning
Selection.End(xlDown).Offset(1, 0).Select
Rows(Selection.Row).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
Else
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
End If
Else
Cells(Selection.Row + 1, 1).Value2 = strError
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
End If
End Function
Private Function IsItReading() As Boolean
Dim isReading As Boolean
intFlag = FindWhichColumn("THEME_NUMBER") 'Test to determine if
it is a reading group if 'THEME_NUMBER' doesn't exist
If intFlag <> 32767 Then 'intFlag is set to
32767
If Cells(2, intFlag).Value2 = "" Then
intFlag = 32767
isReading = False
Else
isReading = True
End If
Else
isReading = False
End If
IsItReading = isReading
End Function


'Module2 Code:

Public Sub SwapToErrorChecker()
Windows("ErrorChecker.xls").Activate
Sheets("Errors").Activate
End Sub

Public Sub SwapToFile(ByVal strFile As String)
Windows(strFile).Activate
Sheets(1).Activate
End Sub
Click to expand...
 
P

Paul

Jeff,
Thanks for that info, I did not know about the FYI stuff you wrote (too
used to VB.NET I guess). The sub I was getting the error on is
"InsertNextMessage." Unfortunately, I do not know why, but I am no
longer getting that message. I put all my functions back in one
module, and readjusted my code. (The module is still only 40k).
However, my code is still not working. I am getting an "expression too
complex" within the one fucntion that is not posted above, so I will
post it after your response. To make things even more curious... if I
follow the code through the debugger tool, it passes through just fine
with no errors. I have also noticed that when I let it run, and it
dies.. the values of the variables indicate that it dies on the first
time entering the function.
I know my code looks bloated, but I really can't seem to find a way to
better optimize it in these functions because I have to do these steps
in order and they are just different enough to not allow them to be
combined into common functions.

Jeff said:
Which is the one you are getting the error on - is it the last sub of module
1 or module 2? If it is 2 it would help if that were posted. Perhaps you
spelt Sub wrong :)

FYI Subs can have more than one argument - just when you call them, don't
put brackets round the arguments. Also functions can be called without a
variable and '=' - just use the keyword 'Call' in front of the function
call.

Jeff
Click to expand...


Private Function CheckHierarchy(ByVal strFileType As String, ByVal
strFile As String, ByVal flagIsReading As Boolean, ByVal intBottom As
Integer) As Boolean
'This function is used after the array has been organized according
to the OrderFileList function.
'It is used to ensure that no child records exist without an
existing parent.
Static UNITCOL, LESSONCOL, CHAPTERCOL, ACTIVITYCOL, STATIONCOL,
LEVELCOL, STRANDCOL, DAYCOL As Integer
Static tallyArray(7, 50) As Boolean
Dim intRow As Integer
Dim unitCell, chapCell, lessonCell, activityCell, stationCell,
levelCell, strandCell, dayCell
Static unit(15) As unitType
Static readingUnit(10) As readingUnitType

intRow = 2
SwapToFile (strFile)

If flagIsReading Then
UNITCOL = FindWhichColumn("THEME_NUMBER")
Else
UNITCOL = FindWhichColumn("UNIT_NUMBER")
End If
CHAPTERCOL = FindWhichColumn("CHAPTER_NUMBER")
LESSONCOL = FindWhichColumn("LESSON_NUMBER")
ACTIVITYCOL = FindWhichColumn("ACTIVITY_NUMBER")
STATIONCOL = FindWhichColumn("STATION_NUMBER")
LEVELCOL = FindWhichColumn("LEVEL_NUMBER")
STRANDCOL = FindWhichColumn("STRAND_NUMBER")
DAYCOL = FindWhichColumn("DAY_NUMBER")

Select Case strFileType
Case "unit"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
If Not isReading Then
If Not unit(unitCell) Then
'************ Line below is highlighted by debugger with expression too
complex error*****************
unit(unitCell).exist = True
End If
Else
If Not readingUnit(unitCell) Then
readingUnit(unitCell).exist = True
End If
End If
intRow = intRow + 1
Wend
Case "chapter"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
chapCell = Cells(intRow, CHAPTERCOL).Value2
If unit(unitCell).exist Then
If Not unit(unitCell).chapter(chapCell).exist Then
unit(unitCell).chapter(chapCell).exist = True
End If
Else
strError = strFile & ": CHAPTER assigned to invalid
UNIT in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "lesson"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
chapCell = Cells(intRow, CHAPTERCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
If Not isReading Then
If unit(unitCell).exist Then
If unit(unitCell).chapter(chapCell).exist Then
If Not
unit(unitCell).chapter(chapCell).lesson(lessonCell).exist Then

unit(unitCell).chapter(chapCell).lesson(lessonCell).exist = True
End If
Else
strError = strFile & ": LESSON assigned to
invalid CHAPTER in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": LESSON assigned to
invalid UNIT in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
If readingUnit(unitCell).exist Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).exist Then

readingUnit(unitCell).readingLesson(lessonCell).exist = True
End If
Else
strError = strFile & ": LESSON assigned to
invalid THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
End If
intRow = intRow + 1
Wend
Case "resunit"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
If Not isReading Then
If Not unit(unitCell).exist Then
strError = strFile & ": Resource assigned to
invalid UNIT in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
If Not readingUnit(unitCell).exist Then
strError = strFile & ": Resource assigned to
invalid THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
End If
intRow = intRow + 1
Wend
Case "reschapter"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
chapCell = Cells(intRow, CHAPTERCOL).Value2
If unit(unitCell).exist Then
If Not unit(unitCell).chapter(chapCell).exist Then
strError = strFile & ": Resource assigned to
invalid CHAPTER in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned to
invalid UNIT in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "reslesson"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
chapCell = Cells(intRow, CHAPTERCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
If Not isReading Then
If unit(unitCell).exist Then
If unit(unitCell).chapter(chapCell).exist Then
If Not
unit(unitCell).chapter(chapCell).lesson(lessonCell).exist Then
strError = strFile & ": Resource
assigned to invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned
to invalid CHAPTER in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned to
invalid UNIT in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
If readingUnit(unitCell).exist Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).exist Then
strError = strFile & ": Resource assigned
to invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned to
invalid THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
End If
intRow = intRow + 1
Wend
Case "activity"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
dayCell = Cells(intRow, DAYCOL).Value2
strandCell = Cells(intRow, STRANDCOL).Value2
activityCell = Cells(intRow, ACTIVITYCOL).Value2
If readingUnit(unitCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).exist
Then
If
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).strand(strandCell).exist
Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).strand(strandCell).activity(activityCell).exist
Then

readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).strand(strandCell).activity(activityCell).exist
= True
End If
Else
strError = strFile & ": ACTIVITY
assigned to invalid STRAND in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": ACTIVITY assigned
to invalid DAY in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": ACTIVITY assigned to
invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
strError = strFile & ": ACTIVITY assigned to
invalid THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "station"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
stationCell = Cells(intRow, STATIONCOL).Value2
If readingUnit(unitCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).exist Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).station(stationCell).exist
Then

readingUnit(unitCell).readingLesson(lessonCell).station(stationCell).exist
= True
End If
Else
strError = strFile & ": STATION assigned to
invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": STATION assigned to invalid
THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "resstationactivity"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
stationCell = Cells(intRow, STATIONCOL).Value2
levelCell = Cells(intRow, LEVELCOL).Value2
If readingUnit(unitCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).station(stationCell).exist
Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).station(stationCell).level(levelCell).exist
Then
strError = strFile & ": Resource
assigned to invalid LEVEL in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned
to invalid STATION in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned to
invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned to
invalid THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "level"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
stationCell = Cells(intRow, STATIONCOL).Value2
levelCell = Cells(intRow, LEVELCOL).Value2
If readingUnit(unitCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).station(stationCell).exist
Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).station(stationCell).level(levelCell).exist
Then

readingUnit(unitCell).readingLesson(lessonCell).station(stationCell).level(levelCell).exist
= True
End If
Else
strError = strFile & ": LEVEL assigned to
invalid STATION in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": LEVEL assigned to
invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": LEVEL assigned to invalid
THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "strand"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
dayCell = Cells(intRow, DAYCOL).Value2
strandCell = Cells(intRow, STRANDCOL).Value2
If readingUnit(unitCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).exist
Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).strand(strandCell).exist
Then

readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).strand(strandCell).exist
= True
End If
Else
strError = strFile & ": STRAND assigned to
invalid DAY in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": STRAND assigned to
invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": STRAND assigned to invalid
THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "day"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
dayCell = Cells(intRow, DAYCOL).Value2
If readingUnit(unitCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).exist Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).exist
Then

readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).exist =
True
End If
Else
strError = strFile & ": DAY assigned to invalid
LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": DAY assigned to invalid
THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case "resactivity"
While intRow <> intBottom + 1
unitCell = Cells(intRow, UNITCOL).Value2
lessonCell = Cells(intRow, LESSONCOL).Value2
dayCell = Cells(intRow, DAYCOL).Value2
strandCell = Cells(intRow, STRANDCOL).Value2
activityCell = Cells(intRow, ACTIVITYCOL).Value2
If readingUnit(unitCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).exist Then
If
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).exist
Then
If
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).strand(strandCell).exist
Then
If Not
readingUnit(unitCell).readingLesson(lessonCell).days(dayCell).strand(strandCell).activity(activityCell).exist
Then
strError = strFile & ": Resource
assigned to invalid ACTIVITY in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError,
0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource
assigned to invalid STRAND in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned
to invalid DAY in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
Else
strError = strFile & ": Resource assigned to
invalid LESSON in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
strError = strFile & ": Resource assigned to
invalid THEME in ROW: " & intRow
SwapToErrorChecker
junk = InsertNextMessage(strError, 0)
SwapToFile (strFile)
End If
intRow = intRow + 1
Wend
Case Else
MsgBox ("Unsupported filetype passed to CheckHierarchy
function.")
End Select
End Function


Private Function FindFileName(ByVal fileList As Variant, ByVal
strFileType As String) As Integer
Dim i As Integer
For i = 0 To UBound(fileList)
If fileList(i) = strFileType Then
FindFileName = i + 1
Exit For
End If
Next
End Function
 
P

Paul

Tom,
Thanks for the offer. When I first started getting that error, I
tried deleting everything between an end portion and the start of the
next function/sub. I had also checked the bottom of the code.
Good I am not getting that error anymore.
Bad I don't know what I did to fix it. (see my reply to Jeff's
post)

Thanks for the reply though. I enjoy all input
 
J

Jeff Standen

Crikey. I've never seen that error personally, but the fact that it works
when you step through it makes me wonder if you have any asynchronous
operations going on, that finish if you step through, but are still going on
if you run it normally (which is of course much quicker). Ring any bells?
 
T

Tom Ogilvy

Apparently this was posted to you rather than the group:

I pasted it into two modules and after cleaning up the word wrap errors, it
compiled fine for me.

I put in a dummy CheckHeirarchy function.

Function CheckHierarchy(strFileType, strFileName, isReading, LastRow)


--
Regards,
Tom Ogilvy


Paul said:
So I decided to post it here. I have read as many message boards as I
could find about the error I continue to receive. "Compile Error: Only
comments may appear after End Sub, End Function, or End Property" I
have seen posts referring to either APIs or Function Declarations. I
do not have either of these in my code. I also saw a post that
recommended keping the size of the module below 64k. My code was less
than 50k, but to be on the safe side I split it up to two modules and
adjusted the code accordingly. I still get the error. Excel is
highlighting the first line of the last Sub in the module. I will post
all of module1 and two of the subs from module2. I can post the last
function if it is deemed necessary, I just didn't want this post to be
longer than it had to be. Cheers!

'Module1 Code:

Public Type activityType
exist As Boolean
End Type

Public Type levelType
exist As Boolean
End Type

Public Type strandType
exist As Boolean
activity(1 To 10) As activityType
End Type

Public Type stationType
exist As Boolean
level(1 To 4) As levelType
End Type

Public Type dayType
exist As Boolean
strand(1 To 4) As strandType
End Type

Public Type readingLessonType
exist As Boolean
station(1 To 10) As stationType
days(1 To 5) As dayType
End Type

Public Type lessonType
exist As Boolean
End Type

Public Type chapterType
exist As Boolean
lesson(50) As lessonType
End Type

Public Type unitType
exist As Boolean
chapter(50) As chapterType
End Type

Public Type readingUnitType
exist As Boolean
readingLesson(10) As readingLessonType
End Type
Public Function OpenFile() As Variant
Dim filex As Variant
filex = Application.GetOpenFilename(Title:="Select Files",
MultiSelect:=True)
OpenFile = filex
End Function
Public Function StripFileName(ByRef filename As Variant, ByVal cell As
Integer) As String
Dim myArray As Variant
myArray = Split(filename(cell), "\") 'Remove the tree
structure from the filename
filename(cell) = myArray(UBound(myArray)) 'Store JUST the
file name
myArray = Split(myArray(UBound(myArray)), "_") 'Remove _'s
myArray = Split(myArray(UBound(myArray)), ".") 'Remove the .'s
leaving just the file name and xls
StripFileName = myArray(0) 'Return just the
file name
End Function
Public Function OrderFileList(ByRef fileList As Variant) As Variant
Dim strTempList(13) As String
Dim orgFileList As Variant
Dim strTemp As String
Dim i As Integer
Dim isReading As Boolean

orgFileList = fileList
isReading = IsItReading()

For i = 1 To UBound(fileList)
strTemp = StripFileName(fileList, i)
strTempList(i - 1) = strTemp
Next
i = FindFileName(strTempList, "eplanner")
orgFileList(1) = fileList(i)
i = FindFileName(strTempList, "book")
orgFileList(2) = fileList(i)
i = FindFileName(strTempList, "unit")
orgFileList(3) = fileList(i)

If isReading Then
i = FindFileName(strTempList, "lesson")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "day")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "strand")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "activity")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "station")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "level")
orgFileList(9) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(10) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(11) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(12) = fileList(i)
i = FindFileName(strTempList, "resactivity")
orgFileList(13) = fileList(i)
i = FindFileName(strTempList, "resstationactivity")
orgFileList(14) = fileList(i)
Else
i = FindFileName(strTempList, "chapter")
orgFileList(4) = fileList(i)
i = FindFileName(strTempList, "lesson")
orgFileList(5) = fileList(i)
i = FindFileName(strTempList, "resbook")
orgFileList(6) = fileList(i)
i = FindFileName(strTempList, "resunit")
orgFileList(7) = fileList(i)
i = FindFileName(strTempList, "reschapter")
orgFileList(8) = fileList(i)
i = FindFileName(strTempList, "reslesson")
orgFileList(9) = fileList(i)
End If

OrderFileList = orgFileList
End Function
Public Function CheckForErrors(ByVal strFileType As String, ByVal
strFileName As String)
Dim intFlag As Integer
Dim isReading As Boolean

Module2.SwapToFile (strFileName)
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

isReading = IsItReading()

'The junk variable is required because a sub can't be used if
passing multiple parameters
'and a function is required to be = to something
junk = CheckData(LastRow, strFileName, "ISBN") 'Always
check the ISBN column
Select Case strFileType 'Determine which
file we are checking.
'Each case has the
"checkable" columns underneath it
Case "lesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "reslesson"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName,
"CHAPTER_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resunit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "unit"
If isReading Then
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
Else
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
End If
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "PACING")
Case "book"
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "chapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "PACING")
Case "eplanner"
junk = CheckData(LastRow, strFileName, "SUBJECT")
junk = CheckData(LastRow, strFileName, "GRADE_ID")
junk = CheckData(LastRow, strFileName, "LOCATION_ID")
Case "resbook"
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "reschapter"
junk = CheckHierarchy(strFileType, strFileName, isReading,
LastRow)
junk = CheckData(LastRow, strFileName, "UNIT_NUMBER")
junk = CheckData(LastRow, strFileName, "CHAPTER_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "strand"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
Case "station"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
Case "level"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
Case "day"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
Case "activity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "STARS_GUID")
Case "resactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "DAY_NUMBER")
junk = CheckData(LastRow, strFileName, "STRAND_NUMBER")
junk = CheckData(LastRow, strFileName, "ACTIVITY_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case "resstationactivity"
junk = CheckData(LastRow, strFileName, "THEME_NUMBER")
junk = CheckData(LastRow, strFileName, "LESSON_NUMBER")
junk = CheckData(LastRow, strFileName, "STATION_NUMBER")
junk = CheckData(LastRow, strFileName, "LEVEL_NUMBER")
junk = CheckData(LastRow, strFileName, "RES_TYPE_ID")
junk = CheckData(LastRow, strFileName, "RES_CAT_ID")
Case Else
MsgBox ("Unrecognized file type. The error checker has not
been " + vbCrLf + _
"adapted to this file type: " + vbCrLf + vbCrLf + vbTab +
vbTab + filetype)
End Select
Module2.SwapToFile (strFileName)
ActiveWorkbook.Close False
End Function
Private Function FindFileName(ByVal fileList As Variant, ByVal
strFileType As String) As Integer
Dim i As Integer
For i = 0 To UBound(fileList)
If fileList(i) = strFileType Then
FindFileName = i + 1
Exit For
End If
Next
End Function
Private Function CheckData(ByVal intBottomOfInfo As Integer, ByVal
strFile As String, ByVal strCol As String)
'Checks the imported column (strCol)to make sure that it conforms to
the correct format of the respective column

'including no inappropriate spaces
Dim test, test2, test3, flag As Boolean
Dim strTemp, strTest, strTest2, strTest3 As String

Select Case strCol
Case "ISBN" 'ISBN column
strTest = "##########"
strTest2 = "#############"
strTest3 = "#########[0-Z]"
Case "SYLLABUS_ITEM_ID", "CONTENT_ID", "BOOK_ID"
'Syllabus_Item_ID, Content_ID, Book_ID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]
[0-Z]"
+ _

"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]
[0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "STARS_GUID" 'Stars_GUID
strTest =
"[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-
Z][0-Z]-"
+ _

"[0-Z][0-Z][0-Z][0-Z]-[0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z][0-Z
][0-Z]"
strTest2 = strTest
strTest3 = strTest
Case "PACING" 'Pacing
strTest = "#"
strTest2 = "#.#"
strTest3 = "##.#"
Case "RES_TYPE_ID", "RES_CAT_ID" 'RESOURCE IDs
junk = CheckRES_ID(intBottomOfInfo, strCol, strFile)
Exit Function
Case Else 'Any plain
numeric column
strTest = "#"
strTest2 = "##"
strTest3 = "###"
End Select
Module2.SwapToFile (strFile)
intColNum = FindWhichColumn(strCol)
For intRowNum = 2 To intBottomOfInfo
flag = True
strTemp = Cells(intRowNum, intColNum).Value2
test = strTemp Like strTest
test2 = strTemp Like strTest2
test3 = strTemp Like strTest3
If Cells(intRowNum, intColNum) = "" Then
strTemp = strFile & ": There is no number in column: " &
strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
ElseIf (Not test) And (Not test2) And (Not test3) Then
strTemp = strFile & ": Improper number format in column: "
& strCol & " row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
flag = False
End If
If strCol = "ISBN" And flag Then
If Cells(intRowNum, intColNum).Value2 <> Cells(2, 1).Value2
Then
strTemp = strFile & ": There is a differing " & strCol
& " in row: " & intRowNum
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
End If
Next
Module2.SwapToErrorChecker
End Function
Private Function CheckRES_ID(ByVal intBottomOfInfo As Integer, ByVal
strCol As String, ByVal strFile As String)
Dim ResList(50), strTemp As String 'Chose the array to be 50
elements because that would cover both RES lists
Dim intRow, intCol, i, intURICol As Integer 'with some room
for growth.
intRow = 1
Module2.SwapToErrorChecker
Sheets(strCol).Activate

Cells(intRow, 1).Select
While Cells(intRow, 1).Value2 <> ""
ResList(intRow) = Cells(intRow, 1).Value2
intRow = intRow + 1
Wend
ResList(intRow + 1) = "End of List" 'Insert manual EOF
Module2.SwapToFile (strFile)

intRow = 2
intCol = FindWhichColumn(strCol)
intURICol = FindWhichColumn("URI")
For intRow = 2 To intBottomOfInfo
i = 1
Do While ResList(i) <> "End of List"
If Cells(intRow, intCol).Value2 = ResList(i) Then
If ResList(i) = "D661E0B7264D1B55E034080020A7D594" Or
ResList(i) = "D661E0B7264E1B55E034080020A7D594" Then
If Cells(intRow, intURICol).Value2 = "" Then
strTemp = strFile & ": Resource listed without
corresponding URI on row: " & intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 1)
Module2.SwapToFile (strFile)
End If
End If
Exit Do
Else
i = i + 1
End If
If ResList(i) = "End of List" Then
strTemp = strFile & ": Invalid " & strCol & " in row: "
& intRow
Module2.SwapToErrorChecker
junk = InsertNextMessage(strTemp, 0)
Module2.SwapToFile (strFile)
End If
Loop
intRow = intRow + 1
Next
Module2.SwapToErrorChecker
End Function
Private Function FindWhichColumn(ByVal strColName As String) As Integer
Dim intColNum As Integer
intColNum = 1
While Cells(1, intColNum).Value2 <> ""
If Cells(1, intColNum).Value2 = strColName Then
FindWhichColumn = intColNum
Exit Function
End If
intColNum = intColNum + 1
Wend
FindWhichColumn = 255
End Function
Private Function InsertNextMessage(ByVal strError As String, ByVal
choice As Integer)
'Used to insert an error message in the proper location, "Possible
Errors" or "Warnings" based on "choice"
'choice = 0 <--- Possible Error
'choice = 1 <--- Warning
Dim strSearch As String
Select Case choice
Case 0
strSearch = "Possible Errors"
Case 1
strSearch = "Warnings"
End Select
CurrCell = Cells.Find(What:=strSearch, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

If Cells(Selection.Row + 1, 1).Value2 <> "No errors found." Then
'test to see if entering first error/warning
If Cells(Selection.Row + 2, 1).Value2 <> "" Then 'test
to see if entering second error/warning
Selection.End(xlDown).Offset(1, 0).Select
Rows(Selection.Row).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
Else
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
Cells(Selection.Row, 1).Value2 = strError
End If
Else
Cells(Selection.Row + 1, 1).Value2 = strError
Rows(Selection.Row + 2).Select
Selection.Insert Shift:=xlDown
End If
End Function
Private Function IsItReading() As Boolean
Dim isReading As Boolean
intFlag = FindWhichColumn("THEME_NUMBER") 'Test to determine if
it is a reading group if 'THEME_NUMBER' doesn't exist
If intFlag <> 32767 Then 'intFlag is set to
32767
If Cells(2, intFlag).Value2 = "" Then
intFlag = 32767
isReading = False
Else
isReading = True
End If
Else
isReading = False
End If
IsItReading = isReading
End Function


'Module2 Code:

Public Sub SwapToErrorChecker()
Windows("ErrorChecker.xls").Activate
Sheets("Errors").Activate
End Sub

Public Sub SwapToFile(ByVal strFile As String)
Windows(strFile).Activate
Sheets(1).Activate
End Sub
Click to expand...
 
P

Paul

To my knowledge I don't have any asynchronous operations going on. I
know I haven't written any... Is there something I could put in my code
maybe above that section, that would take up some time to allow for
another process to finish?
 
P

Paul

Here's another bit of infromation to ponder on... When Excel stops and
gives me the error message, if I hit debug, then hit the "play" button
to continue running the macro, it works until it comes around to that
place again. Once it finishes with that case, and comes back into that
function, but goes to a different case, it works just fine.
 
Top