VBA in Excel - Running Macro from other workbook

Dr Pepper

Hello,

I know almost no VB/VBA. I just started using it this morning.

I click a form button on workbooka.xls that runs a macro called MyMacro which is stored in workbookb.xls. That all works fine, however when I save & close the files and open them again, it's like the reference to workbookb has been lost. When I click the button I get a message saying:

'' could not be found. Check the spelling of the file name, and verify that the file location is correct (It can't find the macro)

When I right-click the button and select 'Assign Macro', it shows this as the Macro name:

' '!MyMacro

The reference to the file workbookb.xls has disappeared! (The above line is supposed to say 'workbookb.xls'!MyMacro)

What's going on? How do I fix it? It driving me mental!!
Thanks,
Brian

Dr Pepper

Another question - Is there any reason my computer has become dog slow since I've started using VB editor and editing a few Excel files? Is that common?

TommyGunne

The easiest thing to do would be just to declare the macro in excel in general and not in a particular workbook.

nordydan

Dr Pepper wrote: »

Hello,

I know almost no VB/VBA. I just started using it this morning.

I click a form button on workbooka.xls that runs a macro called MyMacro which is stored in workbookb.xls. That all works fine, however when I save & close the files and open them again, it's like the reference to workbookb has been lost. When I click the button I get a message saying:

'' could not be found. Check the spelling of the file name, and verify that the file location is correct (It can't find the macro)

When I right-click the button and select 'Assign Macro', it shows this as the Macro name:

' '!MyMacro

The reference to the file workbookb.xls has disappeared! (The above line is supposed to say 'workbookb.xls'!MyMacro)

What's going on? How do I fix it? It driving me mental!!
Thanks,
Brian

Hi Brian, I am a VBA developer.

Handiest thing I find is to store all your macros in a single workbook (WorkbookB).

You should also store a local macro in the module of your own workbook (WorkbookA) to run this e.g. Assign Macro on the button should be to Main. This in turn runs the macro stored in the apppropriate module (I'd recommend calling it the same as the name of WorkbookA if it only applies to that particular workbook) of the WorkbookB workbook. The other two modules OpenWorkbookB and CloseWorkbookB do just that. Replace WorkbookA/WorkbookB/LocationWorkbookB as appropriate.


Global LocationWorkbookB As String, OpenedWorkbookb as Boolean

Sub Main()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Let LocationWorkbookB = "S:\WorkbookB.xls"
Application.Run "OpenWorkbookB"
Application.Run "WorkbookB.xls!" & [whatever you wish to run]
Application.Run "CloseWorkbookB"
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

Sub OpenWorkbookB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Let CurrentWorkbook = ActiveWorkbook.Name
For Each wb In Workbooks
If wb.Name = "WorkbookB.xls" Then
Let OpenedWorkbookB = True
GoTo MarkOpenedWorkbookB
End If
Next wb
Workbooks.Open Filename:=LocWorkbookB, ReadOnly:=True
Windows(CurrentWorkbook).Activate
MarkOpenedWorkbookB:
End Sub

Sub CloseWorkbookB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If OpenedWorkbookB = False Then
Windows("WorkbookB.xls").Activate
ActiveWindow.Close False
Else
End If
End Sub

Dr Pepper

Thanks very much lads.

TommyGunne, I don't think I'd be able to use that solution because the files in question are on a network drive and will be opened/run by lots of people on different PCs. Therefore, I don't want to have to configure the macros in Excel on each PC - I'd rather if it was embedded in the files so that it will run on any PC - Although the macro security restrictions on people's Excel are going to be a bit of a nuisance also I think.

nordydan, Thanks for taking the time to put that reply together! Unfortunately, I'm working on something else this morning but I'll give that solution a try later and let you know how it goes.

nordydan

Dr Pepper,

No problem. Do this every day of the week.

Remember that when defining variants, the simplest Dim XXX as XXX only applies to an individual procedure.
Global XXX as XXX means the variant value is stored universally throughout the VB project.

This the best solution if multiple users source off the same code. It means the central source can be updated to reflect all change.

Evil Phil

If your computer is still running slow check the task manager to see how many instances of excel.exe you have running, that might be a cause of the problem.

nordydan

These two lines speed up the running of the macro:

Application.ScreenUpdating = False
Application.Calculation = xlManual

They turn off screen updating (i.e. the macro runs then the screen is "live again"), and automatic calculation (this is more important of the two).

At the end these two lines:

Application.ScreenUpdating = true
Application.Calculation = xlAutomatic

Will reset the parameters back to normal. If you are filling a formula based spreadsheet however, you need to take care with the calcuation parameter. Remember that all functions can be replicated a lot more quickly and efficiently with vba code.