mochalatte
New Member
- Joined
- Feb 12, 2021
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hi, I obtained this code for get individual worksheet sizes inside the workbook but it produces errors when attempting to copy a worksheet with vba code defining a global variable such as
This is the code I used to get individual size of each worksheet and store it into a worksheet on same workbook called "Settings". The code works but produces User Defined Type Errors. I want to ignore these error messages but I could not come up with a way to do it. I tried disabling alerts and background checking but to no avail. I dont wish to use On Error Resume Next since it is still working as expecting. Is there a way I can just ignore the error messages prompted. The error occurs when
is executed. It only happens for certain worksheets containing a Scripting Dictionary. I am using Microsoft Excel 365. Any help would be greatly appreciated.
P.S. I have Microsoft Sciprting Runtime, Microsoft Scriplet Library and Microsoft Office 16.0 Object Library enabled under Tools/ References. I have tried disabling display alerts and background checking but I still get erros when trying to Copy the worksheet.
VBA Code:
Private timerDictionary As Scripting.Dictionary
This is the code I used to get individual size of each worksheet and store it into a worksheet on same workbook called "Settings". The code works but produces User Defined Type Errors. I want to ignore these error messages but I could not come up with a way to do it. I tried disabling alerts and background checking but to no avail. I dont wish to use On Error Resume Next since it is still working as expecting. Is there a way I can just ignore the error messages prompted. The error occurs when
VBA Code:
xWs.Copy
VBA Code:
Sub WorksheetSizes()
'Update 20140526
Dim xWs As Worksheet
Dim Rng As Range
Dim xOutWs As Worksheet
Dim xOutFile As String
Dim xOutName As String
xOutName = "Settings"
xOutFile = ThisWorkbook.Path & "\TempWb.xls"
On Error Resume Next
Application.DisplayAlerts = False
Application.ErrorCheckingOptions.BackgroundChecking = False
Err = 0
Set xOutWs = Application.Worksheets(xOutName)
If Err = 0 Then
'xOutWs.Delete
Err = 0
End If
'With Application.ActiveWorkbook.Worksheets.Add(Before:=Application.Worksheets(1))
' .name = xOutName
' .Range("TabSizeStart").Resize(1, 2).value = Array("Worksheet Name", "Size")
'End With
Set xOutWs = Application.Worksheets(xOutName)
Application.ScreenUpdating = False
xIndex = 1
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.name <> xOutName Then
If (xWs.name <> "Trading") Then
xWs.Copy
Application.ActiveWorkbook.SaveAs xOutFile
Application.ActiveWorkbook.Close SaveChanges:=False
Set Rng = xOutWs.Range("TabSizeStart").Offset(xIndex, 0)
Rng.Resize(1, 2).value = Array(xWs.name, VBA.FileLen(xOutFile))
Kill xOutFile
xIndex = xIndex + 1
End If
End If
Next
Application.ScreenUpdating = True
Application.Application.DisplayAlerts = True
End Sub