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