从自定义按钮调用时，Excel宏无法正常工作 [英] Excel Macro not working properly when called from custom button

问题描述

我在Excel2010中创建了一个宏，当从宏对话框或VB窗口调用时，它工作得非常好。到那时，一切都很好。现在，我试着使用自定义功能区，并使用自定义用户界面编辑器创建了一个新选项卡，使用自定义图标调用我的宏。而且情况也不好。

对宏的调用起作用了，宏似乎运行正常，按应该的方式扫描每个工作表，查找注释并对其执行操作，但当它完成时，几乎所有的注释都没有按应该的方式修改。

这就是我的问题：当我"正常"运行宏时，它会按计划工作，只有当我试图从它的自定义图标调用它时，它才不会执行它应该做的事情(当被点击时似乎仍然是这样)。

有人知道可能出了什么问题吗？

我认为这不是代码的问题，正如我所说的，它在从宏或VB窗口调用时运行良好

---

编辑：正如我所说，我认为代码不是问题所在，因为它执行时没有错误(只是没有做它应该做的事情)，但按照要求，我将其发布在这里：

Sub ImportCommentsFromWord(control As IRibbonControl)

Dim xComment As Comment
Dim xSheet As Worksheet
Dim wApp As Object

'Opens Word if not already open
On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
Set wApp = CreateObject("Word.Application")
End If

wApp.Visible = False

For Each xSheet In ActiveWorkbook.Worksheets

'Activates each sheet one after another
xSheet.Activate
sName = xSheet.Name
expName = Application.ActiveWorkbook.Path + "" + sName + ".docx"

'Checks if there are comments in active sheet
For Each xComment In xSheet.Comments
    CommsInSheet = 1
Next

If CommsInSheet = 1 Then

    'Opens the translated document to import comments into the sheet
    wApp.Documents.Open (expName)
    wApp.Selection.ClearFormatting
    wApp.Selection.Find.MatchWildcards = False
    wApp.Selection.WholeStory
    wApp.Selection.MoveLeft
    FileEnd = 0
    'Imports comments until end of file is reached
    While FileEnd = 0
        wApp.Selection.ExtendMode = True
        wApp.Selection.MoveRight
        With wApp.Selection.Find
            .Text = "^l"
        End With
        wApp.Selection.Find.Execute
        DestCell = Mid(wApp.Selection.Text, 2, Len(wApp.Selection.Text) - 2)
        wApp.Selection.ExtendMode = False
        wApp.Selection.MoveRight
        wApp.Selection.ExtendMode = True
        With wApp.Selection.Find
            .Text = "^l"
        End With
        wApp.Selection.Find.Execute
        wApp.Selection.ExtendMode = False
        DestComm = Left(wApp.Selection.Text, Len(wApp.Selection.Text) - 1)
        wApp.Selection.MoveRight
        wApp.Selection.MoveLeft
        wApp.Documents.Add DocumentType:=0
        wApp.Selection.Text = DestComm
        With wApp.Selection.Find
            .Text = "^p"
            .Replacement.Text = Chr(10)
        End With
        wApp.Selection.Find.Execute Replace:=wdReplaceAll
        wApp.Selection.WholeStory
        DestComm = Left(wApp.Selection.Text, Len(wApp.Selection.Text) - 1)
        wApp.ActiveDocument.Close savechanges:=False
        If Right(DestComm, 11) = "END_OF_FILE" Then
            DestComm = Left(DestComm, Len(DestComm) - 11)
            FileEnd = 1
        End If
        xSheet.Range(DestCell).Comment.Text Text:=DestComm
    Wend

    'Closes the Word document
    wApp.ActiveDocument.Close savechanges:=False

End If

CommsInSheet = 0

Next

wApp.Visible = True
Set wApp = Nothing

End Sub

推荐答案

没关系，我自己找到了解决方案：问题出在定制功能区的xml代码中，它调用了错误的宏，所以它当然不能像预期的那样工作...

这篇关于从自定义按钮调用时，Excel宏无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！