Variable not Defined: Sheet and/or Worksheet

[stevenblanc]

[SOLVED] Variable not Defined: Sheet and/or Worksheet

Hey folks,

Not entirely sure whats going on. I keep getting the 'variable not defined error' in the following code:

Sub Aggregate_Click()
Dim MyPath, MyFilename As String
Dim vNames() As Variant
Dim i, intSheetCount As Integer

    If Worksheets(1).Cells(14, 15).Value = "1" Then intSheetCount = 2
    If Worksheets(1).Cells(14, 15).Value = "2" Then intSheetCount = 8

    Call ClearImportData

    MyPath = Worksheets(1).Cells(14, 5).Value
    
    ' Add a slash at the end of the path if needed.
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
    
    MyFilename = Dir(MyPath & "*.xl*")


    Do While MyFilename <> ""

        Workbooks.Open Filename:=MyPath & MyFilename, ReadOnly:=True
        
        i = -1
        
        For Each [B]Sheet[/B] In ActiveWorkbook.Sheets
            If Sheet.Index > intSheetCount Then
                i = i + 1
                ReDim Preserve vNames(i)
                vNames(i) = Sheet.Name
            End If
        Next
        
        Application.DisplayAlerts = False
        ActiveWorkbook.Sheets(vNames).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Application.DisplayAlerts = True
        
        Workbooks(MyFilename).Close savechanges:=False
        MyFilename = Dir()

    Loop
    
    ' This procedure will ensure that all links to Department Guidelines are re-routed to this workbook.
    ' Call ChangeLinks
    Call BreakLinks
    
End Sub

The error is on the bolded 'sheet' above. I tried changing it to worksheet and I get the same error. I'm fairly certain when I saved the file yesterday afternoon it was working perfectly. I have no idea what has cracked.

Any ideas?

Steven

 

[Rx_]

Is this running from inside Excel or is is running from Access (automation)?
It looks as if your are running the click event from inside Excel.
Are you able to use the debugger and step through this a line at a time?

 

[stevenblanc]

I don't know how I posted this on the access forum. I meant to post it under excel. I'll move it across there. It is a click in excel.

When I attempt to step through the code I instantly get the error.

 

[Rx_]

Look at line 110 you can move that to the top with your other declarations
I commented out the other subroutine calls for testing.
Added a simple error trap.

Option Explicit

Sub Aggregate_Click()
      Dim MyPath, MyFilename As String
      Dim vNames() As Variant
      Dim i, intSheetCount As Integer
        On Error GoTo MyXLError
10        If Worksheets(1).Cells(14, 15).Value = "1" Then intSheetCount = 2
20        If Worksheets(1).Cells(14, 15).Value = "2" Then intSheetCount = 8
30        'Call ClearImportData
40        MyPath = Worksheets(1).Cells(14, 5).Value
          ' Add a slash at the end of the path if needed.
50        If Right(MyPath, 1) <> "\" Then
60            MyPath = MyPath & "\"
70        End If
80        MyFilename = Dir(MyPath & "*.xl*")
90        Do While MyFilename <> ""
100           Workbooks.Open Filename:=MyPath & MyFilename, ReadOnly:=True
110           i = -1
              Dim MySheet As Worksheet
120           For Each MySheet In ActiveWorkbook.Sheets
130               If MySheet.Index > intSheetCount Then
140                   i = i + 1
150                   ReDim Preserve vNames(i)
160                   vNames(i) = MySheet.Name
170               End If
180           Next
190           Application.DisplayAlerts = False
200           ActiveWorkbook.Sheets(vNames).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
210           Application.DisplayAlerts = True
              
220           Workbooks(MyFilename).Close savechanges:=False
230           MyFilename = Dir()
240       Loop
          
          ' This procedure will ensure that all links to Department Guidelines are re-routed to this workbook.
          ' Call ChangeLinks
250      ' Call BreakLinks
Exit Sub
MyXLError:
    MsgBox "Error " & Err.Number & "  for " & Err.Description, vbOKOnly, "Error on Aggregate_click"
End Sub

 

[Rx_]

Search for MZTools -
It is free for VBA
A button for Line Numbers / Remove Line Numbers
It really makes referencing a line of code easy. For Excel / Access programming it offers some nice features.

 

[stevenblanc]

So I move i = - 1 to the top and added in your error trap. I still get exactly the same error and no additional information:

Current Code:

Sub Aggregate_Click()
Dim MyPath, MyFilename As String
Dim vNames() As Variant
Dim i, intSheetCount As Integer
    On Error GoTo MyXLError
    
    i = -1
    
    If Worksheets(1).Cells(14, 15).Value = "1" Then intSheetCount = 2
    If Worksheets(1).Cells(14, 15).Value = "2" Then intSheetCount = 8

    Call ClearImportData

    MyPath = Worksheets(1).Cells(14, 5).Value
    
    ' Add a slash at the end of the path if needed.
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
    
    MyFilename = Dir(MyPath & "*.xl*")


    Do While MyFilename <> ""

        Workbooks.Open Filename:=MyPath & MyFilename, ReadOnly:=True
        
        For Each Sheet In ActiveWorkbook.Sheets
            If Sheet.Index > intSheetCount Then
                i = i + 1
                ReDim Preserve vNames(i)
                vNames(i) = Sheet.Name
            End If
        Next
        
        Application.DisplayAlerts = False
        ActiveWorkbook.Sheets(vNames).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Application.DisplayAlerts = True
        
        Workbooks(MyFilename).Close savechanges:=False
        MyFilename = Dir()

    Loop
    
    ' This procedure will ensure that all links to Department Guidelines are re-routed to this workbook.
    ' Call ChangeLinks
    Call BreakLinks
    
Exit Sub
MyXLError:
    MsgBox "Error " & Err.Number & "  for " & Err.Description, vbOKOnly, "Error on Aggregate_click"
End Sub

Did it work for you otherwise? I'm starting to wonder if my machine is just acting up.

 

[stevenblanc]

Installing it now.

 

[Rx_]

You saw that other variable defined for Sheet - did you change that?
The blank unnumbered line after 110 (not 110)
Yeah, it seemed to work fine.
Do you want to attach a worksheet?
Don't include any corporate information on it.

 

[stevenblanc]

Saw it. Did it. Conquered it.

Thank you my friend! I still have no idea why it would just stop recognizing sheet and worksheet like that. Anyway, cheers!

 

[stevenblanc]

Not sure what's caused this. But now that the import works I get the following error when I try to save:

errors were detected while saving [file name]. Microsoft Excel may be able to save the file by removing or repairing some features.

I don't see how it can be a data volume issue, as before the first error I was easily aggregating 100 sheets with no error and now it breaks when I'm trying to save with only 20 aggregated sheets.

 

[Brianwarnock]

Saw it. Did it. Conquered it.

Thank you my friend! I still have no idea why it would just stop recognizing sheet and worksheet like that. Anyway, cheers!

Sheet and worksheet are variables and as such you need to define them, the default is variant which you do not want.
Rx changed Sheet to mysheet to avoid confusion.

Brian

 

[Rx_]

My Line 160 - forgot to change sheet to MySheet ..... welllllll sheet!
Check to see if there are others.
And, in the future.... don't use common names as variables.

I had a project once that used First, Second, Third, Forth, ... as variables.
It was embarrassing to describe how much trouble it caused since Second is a reserved key word related to time. Back in '97 the compiler didn't catch it. I ran in circles on a very large program. Don't be like the old me!
Start using verbose variable names.