Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· openaiHugging Face Suzhen/CodeChat

The following code works as intended but can it be written more efficiently: Private Sub…

  1. User

    The following code works as intended but can it be written more efficiently:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then Cancel = True Sheets("Day").Activate End If If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("B1")) Is Nothing Then Cancel = True Sheets("Week").Activate End If If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("C1")) Is Nothing Then Cancel = True Sheets("Forthnight").Activate End If If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("D1")) Is Nothing Then Cancel = True Sheets("Month").Activate End If If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("E1")) Is Nothing Then Cancel = True Sheets("QtrYear").Activate End If If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("F1")) Is Nothing Then Cancel = True Sheets("HalfYear").Activate End If If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("G1")) Is Nothing Then Cancel = True Sheets("Year").Activate End If If Target.Count > 1 Or IsNumeric(Target.Value) Then Exit Sub If Not Intersect(Target, Range("A2:G52")) Is Nothing Then 'Application.EnableEvents = False 'Application.ScreenUpdating = False Dim FormulaReference As String FormulaReference = Target.Formula If Left(FormulaReference, 1) = "=" Then ' Remove leading "=" and single apostrophes around the worksheet name: FormulaReference = Mid(FormulaReference, 2) ' Remove leading "=" FormulaReference = Replace(FormulaReference, "'", "") ' Remove single apostrophes ' Extract worksheet name and cell address using Split: Dim FormulaParts As Variant FormulaParts = Split(FormulaReference, "!") If UBound(FormulaParts) >= 1 Then Dim WorksheetName As String, CellAddress As String WorksheetName = FormulaParts(0) CellAddress = Right(FormulaReference, Len(FormulaReference) - Len(WorksheetName) - 1) Dim DestinationWorksheet As Worksheet Set DestinationWorksheet = ThisWorkbook.Worksheets(WorksheetName) If Not DestinationWorksheet Is Nothing Then Cancel = True 'Application.ScreenUpdating = True 'Application.EnableEvents = True DestinationWorksheet.Activate DestinationWorksheet.Range(CellAddress).Select End If End If End If End If End Sub

  2. Assistant

    Here is a more efficient version of the code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Target.Count > 1 Or IsNumeric(Target.Value) Then Exit Sub
        
        Dim sheetName As String
        Select Case Target.Address
            Case "$A$1": sheetName = "Day"
            Case "$B$1": sheetName = "Week"
            Case "$C$1": sheetName = "Forthnight"
            Case "$D$1": sheetName = "Month"
            Case "$E$1": sheetName = "QtrYear"
            Case "$F$1": sheetName = "HalfYear"
            Case "$G$1": sheetName = "Year"
        End Select
        
        If sheetName <> "" Then
            Cancel = True
            Sheets(sheetName).Activate
        Else
            Dim formula As String
            formula = Target.Formula
            If Left(formula, 1) = "=" Then
                Dim parts As Variant
                parts = Split(Mid(formula, 2), "!")
                If UBound(parts) >= 1 Then
                    Dim destSheet As Worksheet
                    Set destSheet = ThisWorkbook.Worksheets(Replace(parts(0), "'", ""))
                    If Not destSheet Is Nothing Then
                        Cancel = True
                        destSheet.Activate
                        destSheet.Range(Right(parts(1), Len(parts(1)) - 1)).Select
                    End If
                End If
            End If
        End If
    End Sub
    

    This version of the code uses a Select Case statement to efficiently check the Target address and determine the corresponding sheet name. It also includes some minor changes for readability and efficiency.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.