I'm trying to call a sub routine in another spreadsheet but I keep getting a
compile error "sub or function not defined." I have the following code:

....
If Not mybook Is Nothing Then

'Need to do the following:
'if lazy eye hasn't been run in directorcopy then run it
With mybook.Worksheets("DirectorCopy")
If .Cells(1, 1) = "" Then
Call DirectorFormat
DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Determine how many rows in directorcopy
Else
DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
End If
....

Call DirectorFormat is giving me the error. I've also tried Call
DirectorCopyFormat.DirectorFormat (DirectorCopyFormat is the module
Directorformat sits in) but that gives me the same error. What am I doing
wrong?

If you call from a different workbook, then you have to specify the Projectt
name (Workbook in Excel) and the module where the macro resides.


"Bishop" <(E-Mail Removed)> wrote in message
news:0693725F-61D7-45D4-92AB-(E-Mail Removed)...
> I'm trying to call a sub routine in another spreadsheet but I keep getting
> a
> compile error "sub or function not defined." I have the following code:
>
> ...
> If Not mybook Is Nothing Then
>
> 'Need to do the following:
> 'if lazy eye hasn't been run in directorcopy then run
> it
> With mybook.Worksheets("DirectorCopy")
> If .Cells(1, 1) = "" Then
> Call DirectorFormat
> DCLastRow = .Range("A" &
> Rows.Count).End(xlUp).Row
> 'Determine how many rows in directorcopy
> Else
> DCLastRow = .Range("A" &
> Rows.Count).End(xlUp).Row
> End If
> ...
>
> Call DirectorFormat is giving me the error. I've also tried Call
> DirectorCopyFormat.DirectorFormat (DirectorCopyFormat is the module
> Directorformat sits in) but that gives me the same error. What am I doing
> wrong?

That's a good question. I forgot to check for that. However, it is not
declared as private.

"smartin" wrote:

> Bishop wrote:
> > I'm trying to call a sub routine in another spreadsheet but I keep getting a
> > compile error "sub or function not defined." I have the following code:
> >
> > ...
> > If Not mybook Is Nothing Then
> >
> > 'Need to do the following:
> > 'if lazy eye hasn't been run in directorcopy then run it
> > With mybook.Worksheets("DirectorCopy")
> > If .Cells(1, 1) = "" Then
> > Call DirectorFormat
> > DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> > 'Determine how many rows in directorcopy
> > Else
> > DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> > End If
> > ...
> >
> > Call DirectorFormat is giving me the error. I've also tried Call
> > DirectorCopyFormat.DirectorFormat (DirectorCopyFormat is the module
> > Directorformat sits in) but that gives me the same error. What am I doing
> > wrong?
>
> Is DirectorFormat declared as Private? If so, it cannot be called from
> outside the module it resides in.
>

And you don't have:
Option Private Module
at the top of that module, right?????


Bishop wrote:
>
> That's a good question. I forgot to check for that. However, it is not
> declared as private.
>
> "smartin" wrote:
>
> > Bishop wrote:
> > > I'm trying to call a sub routine in another spreadsheet but I keep getting a
> > > compile error "sub or function not defined." I have the following code:
> > >
> > > ...
> > > If Not mybook Is Nothing Then
> > >
> > > 'Need to do the following:
> > > 'if lazy eye hasn't been run in directorcopy then run it
> > > With mybook.Worksheets("DirectorCopy")
> > > If .Cells(1, 1) = "" Then
> > > Call DirectorFormat
> > > DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> > > 'Determine how many rows in directorcopy
> > > Else
> > > DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> > > End If
> > > ...
> > >
> > > Call DirectorFormat is giving me the error. I've also tried Call
> > > DirectorCopyFormat.DirectorFormat (DirectorCopyFormat is the module
> > > Directorformat sits in) but that gives me the same error. What am I doing
> > > wrong?
> >
> > Is DirectorFormat declared as Private? If so, it cannot be called from
> > outside the module it resides in.
> >

--

Dave Peterson

I find that application.run("module") works in all cases or if you want to
pass variables

sdoit = application.run("module",var1,var2 etc)

"Dave Peterson" wrote:

> And you don't have:
> Option Private Module
> at the top of that module, right?????
>
>
> Bishop wrote:
> >
> > That's a good question. I forgot to check for that. However, it is not
> > declared as private.
> >
> > "smartin" wrote:
> >
> > > Bishop wrote:
> > > > I'm trying to call a sub routine in another spreadsheet but I keep getting a
> > > > compile error "sub or function not defined." I have the following code:
> > > >
> > > > ...
> > > > If Not mybook Is Nothing Then
> > > >
> > > > 'Need to do the following:
> > > > 'if lazy eye hasn't been run in directorcopy then run it
> > > > With mybook.Worksheets("DirectorCopy")
> > > > If .Cells(1, 1) = "" Then
> > > > Call DirectorFormat
> > > > DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> > > > 'Determine how many rows in directorcopy
> > > > Else
> > > > DCLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> > > > End If
> > > > ...
> > > >
> > > > Call DirectorFormat is giving me the error. I've also tried Call
> > > > DirectorCopyFormat.DirectorFormat (DirectorCopyFormat is the module
> > > > Directorformat sits in) but that gives me the same error. What am I doing
> > > > wrong?
> > >
> > > Is DirectorFormat declared as Private? If so, it cannot be called from
> > > outside the module it resides in.
> > >
>
> --
>
> Dave Peterson
>