VBA question

cornbread · Apr 12th, 2002, 07:22 AM

Is there any way to get excel to run a macro from a .bas file or project that isn't in any currently open workbook? I need to run a vba macro mutiple times without it updating where the button is assigned to the current workbook or sheet. Is there a way to do this? and or run the macro by referencing it's location outside of any open workbook?

Mark O'Brien · Apr 12th, 2002, 07:31 AM

Quote:

On 2002-04-12 06:22, cornbread wrote:
I need to run a vba macro mutiple times without it updating where the button is assigned to the current workbook or sheet.

I'm not quite sure what you're trying to say here. Could you clarify this for me?

The first part of your question about the .bas file and without looking into it too hard, I would say that this is not possible. I guess that this is sort of what .dll's are designed for. You'd need at least the full version of VB6 to create your own .dlls.

As for the second part, you can't call a macro from a closed workbook, it needs to be open. I think this is sort of common sense since there are some malicious macros out there.

HTH

cornbread · Apr 12th, 2002, 07:49 AM

Basically I need to have a Macro permanently assigned to a button. I need that button to reference that Macro no matter what sheet is currently open. The only way I have found to do this so far is create that macro in a generic workbook have that workbook hide upon opening it and then open the workbooks I want to run the macro on. I would like a cleaner solution to this without stepping out into a VB project if I don't have to.

Mark O'Brien · Apr 12th, 2002, 08:08 AM

I can code this, but I need to take some advice before posting here.

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-12 07:19 ]

cornbread · Apr 12th, 2002, 08:27 AM

Right, that is esentially what I have and in the sub in book1 called main I have all my commands and it runs fine. However if you tie a button within excel to your run macro sub the button will update itself to reference the sheet last opened and saved in the path name to the macro. I suppose I could creat a VB form and have them open that first which would then run the sub much in the way you describe that way the button is not in Excel so it will quite updating itself. I will try that later today and post back if it doesn't work. Thanks for the help.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 12th, 2002, 07:22 AM	#1
cornbread Board Regular Join Date: Apr 2002 Location: Oregon Posts: 77	Is there any way to get excel to run a macro from a .bas file or project that isn't in any currently open workbook? I need to run a vba macro mutiple times without it updating where the button is assigned to the current workbook or sheet. Is there a way to do this? and or run the macro by referencing it's location outside of any open workbook?

Apr 12th, 2002, 07:49 AM	#3
cornbread Board Regular Join Date: Apr 2002 Location: Oregon Posts: 77	Basically I need to have a Macro permanently assigned to a button. I need that button to reference that Macro no matter what sheet is currently open. The only way I have found to do this so far is create that macro in a generic workbook have that workbook hide upon opening it and then open the workbooks I want to run the macro on. I would like a cleaner solution to this without stepping out into a VB project if I don't have to.

Apr 12th, 2002, 08:08 AM	#4
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	I can code this, but I need to take some advice before posting here. _________________ [b] Mark O'Brien [ This Message was edited by: Mark O'Brien on 2002-04-12 07:19 ]

Apr 12th, 2002, 08:27 AM	#5
cornbread Board Regular Join Date: Apr 2002 Location: Oregon Posts: 77	Right, that is esentially what I have and in the sub in book1 called main I have all my commands and it runs fine. However if you tie a button within excel to your run macro sub the button will update itself to reference the sheet last opened and saved in the path name to the macro. I suppose I could creat a VB form and have them open that first which would then run the sub much in the way you describe that way the button is not in Excel so it will quite updating itself. I will try that later today and post back if it doesn't work. Thanks for the help.