时间戳

首页 > 解决方案 > VBA 开始要求在以前工作的代码中标注以前没有标注过的变量

excel - VBA 开始要求在以前工作的代码中标注以前没有标注过的变量

问题描述

我有一个从合并文件中复制 excel 文件的例程。这些复制的 excel 文件由工作表中的名称列表命名。我使用“for”函数从第一个名称到工作表中列表中的最后一个名称循环执行此操作。去年我使用了代码，vba 并没有要求声明所有变量。而现在，当我尝试使用时，它开始要求我声明。我声明了，但代码中缺少一些我找不到答案的东西，vba 给了我一个 msgbox：

“对象变量或未设置块变量”。

我从来没有读过类似的东西。按照下面的代码以及发生问题的行：


Application.DisplayAlerts = False

Dim destino As String, gestor, Arquivo2
Dim Arquivo As Workbook
Dim UltimaLinhaGestor As Integer, i, UltimaLinhaBases, UltimaLinhaArquivo, CC


CC = 8

destino = "C:\Users\arno\Documents\"
UltimaLinhaGestor = Sheets("Farol").Cells(Rows.Count, CC).End(xlUp).Row
    
    For i = 3 To UltimaLinhaGestor
        gestor = ThisWorkbook.Sheets("Farol").Cells(i, CC).Value
        
'##########     HERE     ##########
        Arquivo = "Orçamento 2021 - " & gestor
'##########     HERE     ##########       
 
        ThisWorkbook.SaveCopyAs (destino & Arquivo & ".xlsm")
        
        Workbooks.Open (destino & Arquivo & ".xlsm"), False
            
        Workbooks(Arquivo).Activate

    Next i```

标签： excelvbaobjectvariablesdeclare

解决方案

从列表创建工作簿

  • 根据需要调整const ants 部分中的值。
  • 变量名称可能很蹩脚，但关键是要使它们具有描述性。而我对葡萄牙语的了解是我使用谷歌翻译学习到编写这段代码的。

编码

Option Explicit

Sub consolidar()
    
    ' Constants
    Const camArq As String = "C:\Users\arno\Documents\" ' File Path
    Const orcAno As String = "Orçamento 2021 - "        ' Budget/Year
    Const extArq As String = ".xlsm"                    ' File Extensiom
    Const nomePlanilhaFonte As String = "Farol"         ' Source Worksheet Name
    Const primeiraLinha As Long = 3                     ' First Row
    Const colunaFonte As Variant = 8 ' e.g. 1 or "A"    ' Source Column
    Dim livroFonte As Workbook                          ' Source Workbook Object
    Set livroFonte = ThisWorkbook
    
    ' Define Source Worksheet (planilhaFonte).
    Dim planilhaFonte As Worksheet
    Set planilhaFonte = livroFonte.Worksheets(nomePlanilhaFonte)
    
    ' Calculate Last Row (ultimaLinha) containing data in Source Column.
    Dim ultimaLinha As Long
    ultimaLinha = planilhaFonte.Cells(planilhaFonte.Rows.Count, colunaFonte) _
                               .End(xlUp).Row
    
    ' Speed up the code. You won't be able to see what the code is doing.
    Application.ScreenUpdating = False
    
    ' Declare additional variables to be used in the "For Next" loop.
    Dim livroDestino As Workbook, i As Long, gestor As String
    Dim nomeCompleto As String
    
    ' In source column, loop from first to last source row of source workbook.
    For i = primeiraLinha To ultimaLinha
        
        ' Define 'FullName' (nomeCompleto) of the to be created
        ' destination workbook, to be used with "SaveCopyAs" and "Open".
        gestor = planilhaFonte.Cells(i, colunaFonte).Value
        nomeCompleto = camArq & orcAno & gestor & extArq
        
        ' Save source workbook as current destination workbook.
        ' "DisplayAlerts" is used to overwrite the possible existing files
        ' without "Excel" 'complaining'.
        Application.DisplayAlerts = False
        livroFonte.SaveCopyAs nomeCompleto
        Application.DisplayAlerts = False
        
        ' Open current destination workbook.
        Set livroDestino = Workbooks.Open(nomeCompleto)
            
        ' Do stuff in current destination workbook.
        With livroDestino
            
            ' e.g. Print name to Immediate window (CTRL+G).
            Debug.Print .Name
            ' or:
            '.Worksheets(1).Cells(1, 1) = "Test"
            ' ...
            
            ' Finally close current destination workbook.
            .Close True ' True means save changes.
        
        End With
    
    Next i

    ' Stop speeding up the code and refresh screen.
    Application.ScreenUpdating = True
    
    ' Inform user-
    MsgBox "Sucesso"
 
End Sub

推荐阅读