VBA Worksheet Size Extractor

Analysis

The provided code snippet is written in VBA (Visual Basic for Applications). Below is the corrected and refactored version of the provided code. The main issues identified are:

1. Incorrect use of some keywords and control structures.
2. Syntax issues with variable assignment and usage.
3. Mistakes in language—some parts appear to not be in English (e.g., "Для каждого").

Refactored Code

Sub GetEachWorksheetSize()
    Dim targetSheetName As String
    Dim tempWorkbookPath As String
    Dim targetWorksheet As Worksheet
    Dim sourceWorksheet As Worksheet
    Dim lastEmptyRow As Long 
    
    ' Initialize variables
    targetSheetName = "Sheet Sizes"
    tempWorkbookPath = ThisWorkbook.Path & "\TempWorkbook.xls"
    
    ' Add a new worksheet at the beginning and set it up
    With ActiveWorkbook.Worksheets.Add(Before:=Application.Worksheets(1))
        .Name = targetSheetName
        .Cells(1, 1).Value = "Sheet"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True
        .Cells(1, 2).Value = "Size"
        .Cells(1, 2).Font.Size = 14
        .Cells(1, 2).Font.Bold = True
    End With
    
    Set targetWorksheet = Application.Worksheets(targetSheetName)
    
    ' Loop through each worksheet in the active workbook
    For Each sourceWorksheet In Application.ActiveWorkbook.Worksheets
        If sourceWorksheet.Name <> targetSheetName Then
            ' Copy the worksheet to a new workbook and save it
            sourceWorksheet.Copy
            Application.ActiveWorkbook.SaveAs tempWorkbookPath
            Application.ActiveWorkbook.Close SaveChanges:=False
            
            ' Find the next empty row in the target worksheet and fill it
            lastEmptyRow = targetWorksheet.Range("A" & targetWorksheet.Rows.Count).End(xlUp).Row + 1
            With targetWorksheet
                .Cells(lastEmptyRow, 1).Value = sourceWorksheet.Name
                .Cells(lastEmptyRow, 2).Value = FileLen(tempWorkbookPath)
            End With
            
            ' Delete the temporary workbook file
            Kill tempWorkbookPath
        End If
    Next sourceWorksheet
End Sub

Significant Modifications & Justifications

1. Variable Naming:

   • Changed strTargetSheetName to targetSheetName, strTempWorkbook to tempWorkbookPath, objTargetWorksheet to targetWorksheet, and objWorksheet to sourceWorksheet.
   • Using clearer, descriptive variable names in camel case adheres to common naming conventions.

2. Corrected Syntax Errors:

   • Corrected .Cells(1, 1) = "Лист".Cells(1, 1).Font.Size = 14 to .Cells(1, 1).Value = "Sheet" and .Cells(1, 2) = "Размер" to .Cells(1, 2).Value = "Size". The original syntax was incorrect and contained some non-English text.
   • Changed loop and If structure from incorrect usage (Для каждого objWorksheet) to correct For Each sourceWorksheet In Application.ActiveWorkbook.Worksheets.

3. Variable Types and Declaration:

   • Corrected the variable type for nLastEmptyRow by changing it to Long to ensure it can hold sufficiently large values.

4. Comments and Readability:

   • Added comments to outline major steps within the subroutine for better understanding and context.

5. Workbook Path Handling:

   • Ensured the temporary workbook path is clear and consistently named with camel case for easier reading and maintenance.

Each change was made to ensure the original intent and functionality remain intact while improving clarity, adherence to best practices, and maintainability.

For further learning on writing cleaner VBA code, you might consider exploring courses on the Enterprise DNA platform.