Enhanced VBA Hyperlink Handler

Language/Technology Identification

The code snippet provided is written in VBA (Visual Basic for Applications), commonly used for macros in Excel.

Original Code

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ws As Worksheet
    Dim targetSheetName As String
    Dim targetSheet As Worksheet
    Dim wasHidden As Boolean
    Dim cellAddress As String
    
    ' Извлечение имени листа и адреса ячейки из гиперссылки
    targetSheetName = Split(Target.SubAddress, "!")(0)
    cellAddress = Split(Target.SubAddress, "!")(1)
    
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Sheets(targetSheetName)
    On Error GoTo 0
    
    ' Проверка, существует ли лист и скрыт ли он
    If Not targetSheet Is Nothing Then
        If targetSheet.Visible = xlSheetHidden Or targetSheet.Visible = xlSheetVeryHidden Then
            wasHidden = True
            targetSheet.Visible = xlSheetVisible
        Else
            wasHidden = False
        End If

        ' Переход на целевой лист и адрес ячейки
        Application.Goto targetSheet.Range(cellAddress), True

        ' Скрытие листа, если он был скрыт ранее
        If wasHidden Then
            targetSheet.Visible = xlSheetHidden
        End If
    Else
        MsgBox "Лист " & targetSheetName & " не найден.", vbExclamation
    End If
End Sub

Refactored Code

' Handles the event when a hyperlink is clicked in the worksheet
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim targetWorksheet As Worksheet
    Dim targetSheetName As String
    Dim targetSheet As Worksheet
    Dim wasSheetHidden As Boolean
    Dim targetCellAddress As String
    
    ' Extract the worksheet name and cell address from the hyperlink
    targetSheetName = Split(Target.SubAddress, "!")(0)
    targetCellAddress = Split(Target.SubAddress, "!")(1)
    
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Sheets(targetSheetName)
    On Error GoTo 0
    
    ' Check if the worksheet exists and if it is hidden
    If Not targetSheet Is Nothing Then
        If targetSheet.Visible = xlSheetHidden Or targetSheet.Visible = xlSheetVeryHidden Then
            wasSheetHidden = True
            targetSheet.Visible = xlSheetVisible
        Else
            wasSheetHidden = False
        End If

        ' Navigate to the target worksheet and cell address
        Application.Goto targetSheet.Range(targetCellAddress), True

        ' Hide the worksheet again if it was previously hidden
        If wasSheetHidden Then
            targetSheet.Visible = xlSheetHidden
        End If
    Else
        MsgBox "Worksheet " & targetSheetName & " not found.", vbExclamation
    End If
End Sub

Annotations of Changes

• Variable and Method Names:

  • Original:
    • ws, targetSheetName, targetSheet, wasHidden, cellAddress
  • Refactored:
    • targetWorksheet, targetSheetName, targetSheet, wasSheetHidden, targetCellAddress
  • Rationale: Improved clarity by using more descriptive names, aligning with naming conventions for better readability and maintainability.

• Comments:

  • Original:
    • Извлечение имени листа и адреса ячейки из гиперссылки (Extracting sheet name and cell address from the hyperlink)
  • Refactored:
    • Extract the worksheet name and cell address from the hyperlink
  • Rationale: Rephrased comments to be in English, ensuring consistency with the code's language for better understanding by a wider audience.

• Additional Changes:

  • Original:
    • Application.Goto targetSheet.Range(cellAddress), True
  • Refactored:
    • Application.Goto targetSheet.Range(targetCellAddress), True
  • Rationale: Ensuring consistency with renamed variables.

• Maintainability:

  • Original:
    • MsgBox "Лист " & targetSheetName & " не найден.", vbExclamation
  • Refactored:
    • MsgBox "Worksheet " & targetSheetName & " not found.", vbExclamation
  • Rationale: Ensuring error messages are in English to align with the rest of the code.

Conclusion

The refactored version of the code improves clarity, readability, and maintainability by using descriptive names and consistent language. Comments are rephrased for better understanding, and proper naming conventions are followed throughout the code.