"Dim" problem

[Phil_V]

Hi all I am trying to Dim an object at a module level, this is my code:


Code
-------------------
Dim Main_wb As Workbook

Sub ImportFiles()

' Record the main workbook
Set Main_wb = ActiveWorkbook

'more code blah blah

ws_index = Find_Sheet(Sheet_Name)
End Sub

Function Find_Sheet(s_name As String) As Integer
' Will check the workbook for a sheet for this name;
' If found returns the worksheets index, if not found inserts it and returns index
Dim ws_index As Integer

' Cycle through the sheets
With Main_wb
MsgBox .Worksheets.Count ' <==== HERE
For ws_index = 0 To .Worksheets.Count
If .Worksheets(ws_index).name = s_name Then
Find_Sheet = ws_index
Exit Function
End If
Next
.Worksheets.Add Before:=.Worksheets(1)
.Worksheets(1).name = s_name
End With
End Functio
-------------------


However on the line marked "<=== HERE" I get an automation error.
When I checked using the watch window my Main_wb object doesn't seem t
contain anything at this point but when running through the main sub i
does.

I thought by "Dim"ing it outside of any sub or function it would b
available to all?

What have I done wrong?

Thanks,

Phi

 

[mudraker]

Phil


Unless you are using the Option Explicit option for your module sheet
when you run a macro Excel will automatically create any undeclare
variables at the time the macro or function is run.
What you have is a variable within Sub ImportFiles() called Main_wb
and anther variable within Function Find_Sheet(s_name As String) A
Integer also called Main_wb.

Main_wb in Function Find_Sheet(s_name As String) As Integer has n
instructions as to what it refers to.

you need to declare the variable at the top of the module sheet

Dim Main_wb as Worksheets
or
Private Main_wb as Worksheets
or
Public Main_wb as Worksheets

Sub ImportFiles()

' Record the main workbook
Set Main_wb = ActiveWorkbook

'more code blah blah

ws_index = Find_Sheet(Sheet_Name)
End Sub

Function Find_Sheet(s_name As String) As Integer
' Will check the workbook for a sheet for this name;
' If found returns the worksheets index, if not found inserts it an
returns index
Dim ws_index As Integer

' Cycle through the sheets
With Main_wb
MsgBox .Worksheets.Count ' <==== HERE
For ws_index = 0 To .Worksheets.Count
If .Worksheets(ws_index).name = s_name Then
Find_Sheet = ws_index
Exit Function
End If
Next
.Worksheets.Add Before:=.Worksheets(1)
.Worksheets(1).name = s_name
End With
End Functio

 

[Bill]

I tried running your code and received a subscript out of range error
for the following:

For ws_index = 0 To .Worksheets.Count

I changed the 0 to 1 and it ran fine.

 

[Phil_V]

What you have is a variable within Sub ImportFiles() called Main_wb an
anther variable within Function Find_Sheet(s_name As String) As Intege
also called Main_wb.

Main_wb in Function Find_Sheet(s_name As String) As Integer has n
instructions as to what it refers to.

you need to declare the variable at the top of the module sheet

I thought that is what I had done above?

The "Dim Main_wb As Workbook" that I have written is outside of an
subs or functions?

Thanks,

Phi

 

[Jim May]

sheet index numbers are 1 based, not 0 based.