如果打开两个具有相同宏的相似xls文件，则全局变量为空

There is macro in Excel which was already written and there was bug reported on this and I have to fix this. Initial investigations are below... There is ABC.xls file which has the macro.

Excel中有一个宏，已经写好了，有错误报告，我必须修复它。初步调查低于…美国广播公司(ABC)。具有宏的xls文件。

Now, the macro has a sub named changeTheCode which is getting called when I press Ctrl + M.

现在，这个宏有一个叫changeTheCode的子函数，当我按Ctrl + M时它会被调用。

This sub will open a Open File Dialog where the user can choose a CSV file. The path of the CSV file I am storing in a global variable declare outside of all the function...

此子程序将打开一个打开的文件对话框，用户可以在其中选择CSV文件。我存储在全局变量声明中的CSV文件的路径在所有函数之外……

Public txtFileNameAndPath As String

This global variable will be used to save the changes into the CSV file when the user closes the excel.

当用户关闭excel时，将使用这个全局变量将更改保存到CSV文件中。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call saveUnicodeCSV
    Call deleteXLS
End Sub

I use this ABC.xls file for opening a ABC123.CSV file.

我使用这个ABC。用于打开ABC123的xls文件。CSV文件。

I use this DEF.xls (a copy of ABC.xls) file to open DEF123.CSV file. But when I open the DEF123.CSV using Ctrl + M, the sub changeTheCode of the ABC.xls is getting called and the global variable txtFileNameAndPath of DEF.xls is empty and when I close the Excel, things are not getting saved because of this.

我使用这个DEF.xls (ABC.xls的副本)文件来打开DEF123。CSV文件。但是当我打开DEF123时。使用Ctrl + M的CSV, ABC的子更改码。xls将被调用，而DEF.xls的全局变量txtFileNameAndPath是空的，当我关闭Excel时，由于这个原因，不会保存任何东西。

Code where the global variable is getting set.

设置全局变量的代码。

Public txtFileNameAndPath As String
Sub CodePageChange()
    Dim SheetName As Worksheet
    Dim fd As Office.FileDialog
    Dim sheetName1 As String
    Dim tabSheetName As String

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        '....
        '....
        '....
        If .Show = True Then
          txtFileNameAndPath = .SelectedItems(1)
        Else
            MsgBox "Please start over.  You must select a csv file."
            Exit Sub
        End If
    End With

Inputs on how to handle this will help me a lot.

如何处理这个问题对我有很大帮助。

Note: The Excel containing macro will be given to customer. Hence I cannot ask customer to do some registry tweaks to open the Excel in separate instance.

注意:包含宏的Excel将提供给客户。因此，我不能要求客户在单独的实例中进行一些注册表调整来打开Excel。

Thanks.

谢谢。

8 个解决方案

#1

The problem I think is that when you bind the same macro names to a Shortcut Key, the first book that is opened will be assigned to that shortcut key.

我认为的问题是，当您将相同的宏名绑定到快捷键时，打开的第一本书将被分配给快捷键。

A work around would be to make another macro to call the right macro based on the workbook name. This might require you to replace some of the Workbooks and Worksheets to ActiveWorkbook or ActiveWorksheet. But please try it out first.

一种解决方法是创建另一个宏，根据工作簿名称调用正确的宏。这可能需要您将一些工作簿和工作表替换为ActiveWorkbook或ActiveWorksheet。但请先试一试。

This is just example code I tried out but please edit it to your needs. I tried it by making two files a.xlsm and b.xlsm. difference is a.xlsm has msgbox "a" in first line and b.xlsm has msbox "b" in the first line. You check by opening a.xlsm first then b.xls. Then assign the same shortcut key. You will see that when you run Ctrl+M in b.xlsm that the macro that will be run will be in a.xlsm with msgbox "A" but then it will call the correct macro in b.xlsm.

这只是我尝试过的示例代码，但是请根据需要编辑它。我试过做两个文件a。xlsm b.xlsm。区别是一个。xlsm在第一行有msgbox“a”和b。xlsm在第一行有msbox“b”。你通过打开a来检查。xlsm第一然后b.xls。然后分配相同的快捷键。当你在b中运行Ctrl+M时，你会看到。将运行的宏将在a中。使用msgbox“A”的xlsm，但是它会调用b.xlsm中的正确宏。

Short Answer

简短的回答

This code makes use of Application.Run which runs the macro from a specific workbook. create a helper macro that will check filename then append macro name.

这段代码使用了应用程序。运行，从特定的工作簿运行宏。创建一个助手宏，检查文件名，然后追加宏名。

So when you ctrl+m, regardless which workbook run_code is run from, it will go back to activeworkbook and run the macro, in this case plaster, from that workbook. Also it will populate the Public Variable with the activeworkbook value.

因此，当您按ctrl+m时，无论运行哪个工作簿run_code，它都将返回到activeworkbook并运行这个宏，在本例中，是该工作簿中的paste。它还将使用activeworkbook值填充公共变量。

https://www.rondebruin.nl/win/s9/win001.htm some samples of application.run

https://www.rondebruin.nl/win/s9/win001.htm application.run的一些示例

By far the easiest solution for you just add Caller Macro

到目前为止，最简单的解决方案就是添加调用者宏。

Caller Macro:

调用者宏:

Sub call_changeTheCode() ' add to all workbooks, that have changeTheCode macro then assign to Ctrl + m

Application.Run ActiveWorkbook.Name & "!changeTheCode"

End Sub

Proof of concept, not your actual code, use above Caller Macro:

概念证明，而不是你的实际代码，使用以上调用者宏:

Public varvar As String
Sub run_code() 'assignt to shortcut key CTRL+M both macros in a.xlsm and b.xlsm
MsgBox "a" ' to test create another workbook and change this to b
file_path = ActiveWorkbook.Path 'just to check path
file_name = ActiveWorkbook.Name 'gets the file name
MsgBox file_path 'msgbox the file_path
MsgBox file_name 'msgbox the file_name
MsgBox file_name & "!plaster" 'msgbox the file name plus macro name, in your instance it would be "ABC.xlsm'!macro_name" please note the format
Application.Run file_name & "!plaster"

End Sub

Sub plaster() 'this is the test macro that will show correct macro in workbook is called
varvar = ActiveWorkbook.Name
MsgBox "hi this is workbook " & varvar

End Sub

#2

If I understand it properly, user has DEF.xls as a top-level workbook, hence equal to ActiveWorkbook. Then my idea is to add new sheet to this workbook with very unique name, like "zZzVBAdatazZz" and make it very hidden (set Sheets("zZzVBAdatazZz").Visible = xlVeryHidden), so user can't unhide it with menu commands. When the macro is run you can store the path to DEF123.CSV in this sheet, let's say in cell A1 (ActiveWorkbook.Sheets("zZzVBAdatazZz").Range(A1) = .SelectedItems(1)). In your Private Sub Workbook_BeforeClose(Cancel As Boolean) check if there is a value in Sheets("zZzVBAdatazZz").Range(A1). If so, take it and store in variable (it can be local now), delete value in A1 and pass the path to saveUnicodeCSV.

如果理解正确，用户将DEF.xls作为*工作簿，因此等于ActiveWorkbook。然后，我的想法是在这个工作簿中添加一个具有非常独特名称的新表，比如“zZzVBAdatazZz”，并使它非常隐藏(设置表(“zZzVBAdatazZz”)。可见= xlVeryHidden)，因此用户不能用菜单命令来隐藏它。当宏运行时，您可以存储到DEF123的路径。在这个表中，假设是单元格A1 (ActiveWorkbook.Sheets(“zZzVBAdatazZz”). range (A1) = . selecteditems(1))。在您的私有子Workbook_BeforeClose(以布尔形式取消)中检查表(“zZzVBAdatazZz”). range (A1)中是否有值。如果是，将它存储在变量中(现在它可以是本地的)，在A1中删除值，并将路径传递给saveUnicodeCSV。

#3

Interesting problem, I believe the heart of the problem is that a workbook's global object is not really global across the whole application space. Hereafter I write global as "global" to highlight this misnoma.

有趣的问题是，我认为问题的核心是工作簿的全局对象在整个应用程序空间中并不是全局的。此后，我把global写成global，以强调这个错误。

I believe your macro will always run from one place, so how to scope a "global" variable for each workbook and yet still reach it from another workbook's code base.

我相信您的宏将始终从一个地方运行，因此如何为每个工作簿确定“全局”变量的范围，并且仍然可以从另一个工作簿的代码库中访问它。

My solution would be to use the ThisWorkbook module as the place to house the "global" variable. So in the ThisWorkbook module place the following code

我的解决方案是使用ThisWorkbook模块作为存放“global”变量的地方。因此，在ThisWorkbook模块中放置以下代码

Option Explicit

Public NJMRGlobalVar As Variant

then in the module of your macro code you will need a function to test if a given workbook supports/exports this new "global" variable. So something like this ( given along with unit test)

然后在宏代码的模块中，您将需要一个函数来测试给定的工作簿是否支持/导出这个新的“全局”变量。像这样(连同单元测试一起给出)

Private Function WorkbookHasNJMRGlobalVar(ByVal wb As Excel.Workbook) As Boolean
    If wb Is Nothing Then
        WorkbookHasNJMRGlobalVar = False
    Else
        Dim vTest As Variant
        vTest = CVErr(xlErrName)
        'Requires VBA IDE->Tools->Options->General->Error Trapping->Break on Unhandled Errors
        On Error Resume Next
        vTest = CallByName(wb, "NJMRGlobalVar", VbGet)

        Dim lSaveError As Long
        lSaveError = Err.Number
        On Error GoTo 0
        WorkbookHasNJMRGlobalVar = (lSaveError = 0)
    End If

End Function

Private Sub TestWorkbookHasNJMRGlobalVar()

    Debug.Assert WorkbookHasNJMRGlobalVar(ThisWorkbook) = True

    Dim wbUnsuitable As Excel.Workbook
    Set wbUnsuitable = Workbooks.Item("VBA Fileshare.xlsm") '*<---- different for you!

    Debug.Assert WorkbookHasNJMRGlobalVar(wbUnsuitable) = False

End Sub

Final step is to rewrite your code not in terms of ThisWorkbook but either ActiveWorkbook or acquiring the target workbook with Application.Workbooks.Item("foo.xlsm"). So here is some code (which works on ActiveWorkbook).

最后一步是重写您的代码，而不是使用本工作簿，而是使用ActiveWorkbook或使用Application.Workbooks.Item(“foo.xlsm”)来获取目标工作簿。这里有一些代码(在ActiveWorkbook上工作)。

Sub CodePageChange()
    Dim SheetName As Worksheet
    Dim fd As Office.FileDialog
    Dim sheetName1 As String
    Dim tabSheetName As String

    If Not WorkbookHasNJMRGlobalVar(ActiveWorkbook) Then
        MsgBox "Currently Active Workbook not a suitable candidate for that macro."
    Else

        Dim wbGlobalVarEnabled As Excel.Workbook
        Set wbGlobalVarEnabled = ActiveWorkbook

        Set fd = Application.FileDialog(msoFileDialogFilePicker)

        With fd
            '....
            '....
            '....
            If .Show = True Then
                wbGlobalVarEnabled.NJMRGlobalVar = .SelectedItems(1)
            Else
                MsgBox "Please start over.  You must select a csv file."
                Exit Sub
            End If
        End With
    End If
End Sub

So now you have a "global" variable per workbook which is freely accessible from one code simply by acquiring a reference to an Excel.Workbook. This works because Excel.Workbook does not forbid extensibility in the interface meaning extra methods and properties can be added (but not all workbooks will support them hence you a function to detect, e.g WorkbookHasNJMRGlobalVar above).

因此，现在每个工作簿都有一个“全局”变量，只需获取对excell . workbook的引用，就可以从一个代码*访问该变量。这个工作因为Excel。Workbook并不禁止接口中的可扩展性，这意味着可以添加额外的方法和属性(但是并不是所有的工作簿都支持它们，因此您可以使用一个函数来检测它们，e。g WorkbookHasNJMRGlobalVar上图)。

Please post feedback if not enough and I will amend, I have 99/100 on VBA questions I would like to earn my bronze VBA badge :)

如果没有足够的反馈，我将修改，我有99/100的VBA问题，我想获得我的青铜VBA徽章:)

EDIT: reading your profile you have C/C++ skills so I can go further in low-level explanation as to how this works. In the type library for Excel the "nonextensible" IDL keyword decorates most interfaces forbidding extra methods and properties but this is missing from interface _Workbook so you can. Here is a screenshot from OleView.exe

编辑:阅读你的个人资料，你有C/ c++的技能，所以我可以更深入地解释它是如何工作的。在Excel的类型库中，“不可扩展”IDL关键字修饰了大多数禁止附加方法和属性的接口，但是接口_Workbook中没有这个接口，所以可以这样做。这是来自OleView.exe的截图。

#4

A unique shortcut can only be assigned to a single procedure in a workbook. Setting the same shortcut a second time overrides the previous assignment.

唯一的快捷方式只能分配给工作簿中的单个过程。第二次设置相同的快捷方式将覆盖先前的赋值。

To handle the same shortcut in different workbooks, either assign the shortcut when the workbook is activated :

要在不同的工作簿中处理相同的快捷方式，可以在激活工作簿时指定快捷方式:

' ThisWorkbook '

Private Sub Workbook_Activate()
  Application.OnKey "^m", "CodePageChange"
End Sub

' Module '

Public Sub CodePageChange()
  MsgBox ThisWorkbook.Name
End Sub

Or handle the callback in the workbook which receives it and then call the procedure on the targeted workbook:

或者在工作簿中处理接收它的回调，然后调用目标工作簿上的过程:

' ThisWorkbook '

Private Sub Workbook_Open()
  Application.OnKey "^m", "OnHotkeyCtrlM"
End Sub

' Module '

Public Sub OnHotkeyCtrlM()
  Application.Run "'" & ActiveWorkbook.Name & "'!CodePageChange"
End Sub

Public Sub CodePageChange()
   MsgBox ThisWorkbook.Name
End Sub

#5

use custom property as a global variable

使用自定义属性作为全局变量。

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Debug.Print ActiveWorkbook.CustomDocumentProperties("xyz")

End Sub


Sub changeTheCode()

    On Error Resume Next
    ActiveWorkbook.CustomDocumentProperties("xyz").Delete
    ActiveWorkbook.CustomDocumentProperties.Add Name:="xyz", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=ActiveWorkbook.Name
    Debug.Print ActiveWorkbook.CustomDocumentProperties("xyz")

End Sub

#6

I agree with S Meaden, you cannot have a Global variable across several workbooks without specifying the workbook where the "global" variable is stored.

我同意S Meaden的观点，如果不指定存储“全局”变量的工作簿，就不可能在多个工作簿中拥有全局变量。

Another approach: In the Excel file with the macro where the setting of the "global" variable is done: Replace the setting with

另一种方法是:在Excel文件中使用宏来完成“全局变量”的设置:将设置替换为。

Shell ("Cmd.Exe /C SetX txtFileNameAndPath thePath")

Replace thePath with the actual path or value. After this macro is run you can run your Excel files and retrieve the true global value through

用实际的路径或值替换路径。运行此宏之后，您可以运行您的Excel文件并通过它检索真正的全局值

txtFileNameAndPath = Environ("txtFileNameAndPath")

#7

Put the global variable into the ThisWorkbook class module.

将全局变量放入ThisWorkbook类模块。

Public txtFileNameAndPath As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Debug.Print Me.Name & ":" & txtFileNameAndPath
End Sub 

Then in the procedure CodePageChange loop through all the workbooks and check using ActiveWorkbook.Name which global variable should be used. HTH

然后在程序代码转换中，通过所有的工作簿，并使用ActiveWorkbook进行检查。命名应该使用的全局变量。HTH

If .Show = True Then
  Dim w As Workbook
  On Error Resume Next
  For Each w In Workbooks
    If ActiveWorkbook.Name = w.Name Then
        w.txtFileNameAndPath = .SelectedItems(1)
        Exit For
    End If
  Next w
  On Error GoTo 0
Else
    MsgBox "Please start over.  You must select a csv file."
    Exit Sub
End If

#8

User-friendly workaround: Ditch the keyboard short-cut and add either a CommandButton to a worksheet or Custom Ribbon Tab to the workbook to call the procedure. They're guaranteed to call the procedure from their own workbook and easier for users.

用户友好的解决方案:抛弃键盘上的快捷键，向工作表中添加命令按钮或自定义丝带选项卡来调用过程。它们保证从自己的工作簿中调用过程，对用户来说更容易。