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 Befo=.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

--
Phil_
-----------------------------------------------------------------------
Phil_V's Profile: http://www.excelforum.com/member.php...fo&userid=3185
View this thread: http://www.excelforum.com/showthread.php?threadid=52337

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 Befo=.Worksheets(1)
.Worksheets(1).name = s_name
End With
End Functio

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=52337

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.



*** Sent via Developersdex http://www.developersdex.com ***

mudraker Wrote:
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

--
Phil_
-----------------------------------------------------------------------
Phil_V's Profile: http://www.excelforum.com/member.php...fo&userid=3185
View this thread: http://www.excelforum.com/showthread.php?threadid=52337

sheet index numbers are 1 based, not 0 based.

"Bill" wrote in message
...
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.



*** Sent via Developersdex http://www.developersdex.com ***