The following code works as intended but can it be written more efficiently: Private Sub…
- 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
- 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 SubThis version of the code uses a
Select Casestatement to efficiently check the Target address and determine the corresponding sheet name. It also includes some minor changes for readability and efficiency.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.