excel - 内存不足提示VBA

Question

我正在尝试编写一个代码，该代码将根据位于 URL 中的主电子表格中的另一个工作簿中的版本列表检查一个工作簿中的版本号。一切正常，指令竞争，但我不断得到“内存不足”提示任何想法，为什么？

Option explicit 
Sub test()

    Dim MyPath as Object
    Dim WorkbookType as Range 
    Dim Version as Integer 
    Dim CurrentVersion as Integer 
    Dim SearchRange as Object
    Dim WorkbookVersion as Range 

    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False

    Version = Worksheets(“sheet1”).Range(“Ver”)
    Set WorkbookType = Worksheets(“sheet1”).Range(“Typ”)
    Set MyPath Workbooks.open(Filename:=“https://LocationOfTheMasterSpreadsheet.xlsm”, ReadOnly:=True, UpdateLinks:=False)

    Worksheets(“Master”).Activate
    Set SearchRange = Worksheets(“Master”).Range(“Type”)
    Set WorkbookType = SearchRange.Find(What:=WorkbookType, lookIn:=xlValues)
    Set CurrentVersion = WorkbookType.Offset(0, 1)

    If WorkbookType is nothing then
        Msgbox “No such data found”
    End If
    If CurrentVersion = Version then
        Msgbox “ success this is a current version”
    ElseIf  CurrentVersion <> Version then 
        Msgbox “Sorry you’re not using Current Version “
    End If

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End sub

任何帮助将不胜感激。

谢谢

标签： excelvba

Answer 1

我有一个类似的错误，并且能够通过简单地添加Application.Calculation = xlCalculationManualunderApplication.ScreenUpdating = False然后恢复到Application.Calculation = xlCalculationAutomaticunder来修复它Application.ScreenUpdating = True。

此外，Tim William 的代码也稍微干净一些，这可能会有所帮助。