Return Target Formula of a cell

Question

I am attempting to create a check in which the macro will extract the old value, old formula, new value, and new formula of a changed cell.

Dim oldValue As String
Dim oldAddress As String
Dim oldFormula As String

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName as String
sSheetName = ActiveSheet.Name
If ActiveSheet.Name <>"Log Details" Then
    Application.EnableEvents = False
    Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = ActiveSheet.Name & "-" & Target.Address(0,0)
    Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = oldValue
    Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = **oldFormula**
    Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = Target.Value
    Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = **Target.Formula2**
    Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = Environ("username")
    Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1,0).Value = Now
    Sheets("Log Details").Hyperlinks.Add Anchor:=Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0,7),Address:="",SubAddress:="'"& sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress
    Application.EnableEvents=True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range
    oldValue = VarType(Target.Value)
    oldAddress = Target.Address
    oldFormula = **Range(Target.Address).Formula2**
End Sub

I believe I am not understanding how to extract the formula from the cell before it changes and then again once the cell is updated.

Answer 1

Some suggestions:

Dim oldFormat As String '<<<
Dim oldValue As String
Dim oldSheet As String  '<<<
Dim oldAddress As String
Dim oldFormula As String

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Const LOG_SHEET As String = "Log Details"
    Dim sSheetName As String, wsLog As Worksheet, previousOK As Boolean
    
    If Target.Cells.CountLarge > 1 Then Exit Sub      'can't handle multi-cell changes
    Set wsLog = ThisWorkbook.Worksheets(LOG_SHEET)
    If Sh.Name = wsLog.Name Then Exit Sub             'no logging on log sheet
    
    'is this the same cell for which previous values were captured?
    previousOK = Sh.Name = oldSheet And Target.Address = oldAddress
    
    On Error GoTo haveError 'make sure error handling is turned back on...
    Application.EnableEvents = False
    With wsLog.Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow
        .Cells(1).Value = Sh.Name & "-" & Target.Address(0, 0)
        If previousOK Then 'make sure these are appropriate...
            .Cells(2).NumberFormat = oldFormat
            .Cells(2).Value = oldValue
            .Cells(3).Value = oldFormula       'shown as text...
        End If
        .Cells(4).NumberFormat = Target.NumberFormat
        .Cells(4).Value = Target.Value
        .Cells(5).Value = IIf(Target.HasFormula, "'" & Target.Formula, "")
        .Cells(6).Value = Environ("username")
        .Cells(7).Value = Now
        wsLog.Hyperlinks.Add Anchor:=.Cells(8), Address:="", _
                    SubAddress:="'" & Sh.Name & "'!" & oldAddress, _
                    TextToDisplay:=Sh.Name & " > " & oldAddress
    End With
haveError:
    Application.EnableEvents = True
    
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.CountLarge = 1 Then 'only handling single-cell selections
        oldFormat = Target.NumberFormat '<<<
        oldValue = Target.Value
        oldSheet = Sh.Name              '<<<
        oldAddress = Target.Address
        oldFormula = IIf(Target.HasFormula, "'" & Target.Formula, "")
    End If
End Sub

The main thing is to make sure you don't end up trying to process Target when it's a multi-cell range.