How to call startup folder vba function?

  • Thread starter Thread starter Tom Corcoran
  • Start date Start date
T

Tom Corcoran

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 said:
Application.Run "Startup.xls!Macro1"
Click to expand...

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.
 
Tom Ogilvy said:
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.
Click to expand...

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.
Click to expand...


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.
 
Hi Tom,

Tom Ogilvy said:
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.
Click to expand...

Thanks for your patience. That worked out nicely, great. I saved my code an
xla file and added it to the list of add-ins (not in the default directory).
I later moved the file to another location and on startup excel said it
hadn't found the xla file and should it delete it from the list. I did and
readded but found that to avoid that question every time you then had remove
it form the list first, close excel and then readd.

I have a button assigned to a macro that I moved to this add on. To reassign
it in the macro name dropdown I type 'My vba code.xla'!CopySheetAndRename.
But when I click on the button it says Macro CopySheetAndRename not found.
When I go back into to edit the macro name it has stripped away the xla
reference and just left the macro name which suggests it has found it. Do
you have any idea what the problem might be?

Thanks a lot for the continued help.

Regards, Tom.
 
Back
Top