I have a xls file loading hidden from my alternate startup folder.

After a lot of help file reading, hunting and web searching I can't figure
how to call a vba routine I have defined in the startup xls in another xls
file. Can someone please let me in on the secret.

Thanks a lot,

Cheers - Tom.

Application.Run "Startup.xls!Macro1"

--
Regards,
Tom Ogilvy

Tom Corcoran wrote in message
...
I have a xls file loading hidden from my alternate startup folder.

After a lot of help file reading, hunting and web searching I can't figure
how to call a vba routine I have defined in the startup xls in another xls
file. Can someone please let me in on the secret.

Thanks a lot,

Cheers - Tom.

"Tom Ogilvy" wrote
Application.Run "Startup.xls!Macro1"

Thanks a lot for the post. I did a search after your suggestion on working
with arguments.

My function in my vba code.xls is :

Public Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Range(Ref.Address)
End Function

I have tried
=Application.Run("My vba code.xls!SHEETOFFSET",1,A1)+31
and the likes. I no longer am getting an error but it can't evaluate it and
I get a #name?

I am trying it in A1 in the sheet to the left of the previous sheet where A1
contains a valid date. Can you spot my error?

Cheers, Tom.

You can't use application.Run directly as a function in a worksheet.

If you want to use a function in another workbook (using the function in a
cell), then put the function in an addin and load the addin.

Other than that, it is difficult to see what it is you are trying to do.

--
Regards,
Tom Ogilvy


Tom Corcoran wrote in message
...
"Tom Ogilvy" wrote
Application.Run "Startup.xls!Macro1"

Thanks a lot for the post. I did a search after your suggestion on working
with arguments.

My function in my vba code.xls is :

Public Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Range(Ref.Address)
End Function

I have tried
=Application.Run("My vba code.xls!SHEETOFFSET",1,A1)+31
and the likes. I no longer am getting an error but it can't evaluate it
and
I get a #name?

I am trying it in A1 in the sheet to the left of the previous sheet where
A1
contains a valid date. Can you spot my error?

Cheers, Tom.

"Tom Ogilvy" wrote in message
...
You can't use application.Run directly as a function in a worksheet.

If you want to use a function in another workbook (using the function in a
cell), then put the function in an addin and load the addin.

Other than that, it is difficult to see what it is you are trying to do.

Thanks for the post!

It looks as I was not clear in my post. I am trying to figure out how to
call a function in another workbook. I am trying to use a function in a
number of workbooks by having it stored in one place so I don't have to
duplicate a copy in each workbook. Sorry it was unclear.

After your mail, from reading the help it seems I could also add a .xla/.xlt
add in which would contain the function. So I saved my "my vba code.xls" as
a xlt file and added it via the tools add in menu. However, the problem is
the same how can I refer to it from my separate xls workbook?

Thanks for your patience. Is it clearer now?

Cheers, Tom.

As I just answered:

If you want to use a function in another workbook (using the function in a
cell), then put the function in an addin and load the addin.


an xlt is a template and would not be appropriate to what you are trying to
do. An addin usually has an xla extension.

also, as I stated, the addin has to be loaded. The option to save as an
addin is one of the last choices in the save file as type dropdown.

--
Regards,
Tom Ogilvy


Tom Corcoran wrote in message
...
"Tom Ogilvy" wrote in message
...
You can't use application.Run directly as a function in a worksheet.

If you want to use a function in another workbook (using the function in
a
cell), then put the function in an addin and load the addin.

Other than that, it is difficult to see what it is you are trying to do.

Thanks for the post!

It looks as I was not clear in my post. I am trying to figure out how to
call a function in another workbook. I am trying to use a function in a
number of workbooks by having it stored in one place so I don't have to
duplicate a copy in each workbook. Sorry it was unclear.

After your mail, from reading the help it seems I could also add a
..xla/.xlt
add in which would contain the function. So I saved my "my vba code.xls"
as
a xlt file and added it via the tools add in menu. However, the problem is
the same how can I refer to it from my separate xls workbook?

Thanks for your patience. Is it clearer now?

Cheers, Tom.