AmazonUrlCleaner.bas

Attribute VB_Name = "AmazonUrlCleaner"
Option Explicit

' =============================================================================
' Amazon image URL auto-cleaner
'
' INSTALL into Template.xlsm:
'   1. Alt+F11 → File → Import File → select AmazonUrlCleaner.bas ONLY
'   2. Double-click ThisWorkbook — paste Sub from AmazonUrlCleaner_ThisWorkbook.txt
'   3. Debug → Compile VBAProject, then save as .xlsm
'
' SCOPE:
'   - Only Manual and WriteBuffer worksheets
'   - Only cells from B2 onward (never row 1, never column A)
'   - After any change in that region (including transpose paste), cleans the
'     full used B2+ block so every transposed Amazon URL is processed
' =============================================================================

Private Const FIRST_DATA_ROW As Long = 2
Private Const FIRST_IMAGE_COL As Long = 2
Private Const MANUAL_SHEET As String = "Manual"
Private Const WRITEBUFFER_SHEET As String = "WriteBuffer"

' Called from ThisWorkbook.Workbook_SheetChange
Public Sub HandleAmazonUrlSheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim allowedRegion As Range
    Dim touched As Range
    Dim dataRange As Range
    Dim ws As Worksheet

    On Error GoTo ErrorHandler

    If Target Is Nothing Then Exit Sub
    If TypeName(Sh) <> "Worksheet" Then Exit Sub

    Set ws = Sh
    If Not IsAmazonCleanerTargetSheet(ws) Then Exit Sub

    Set allowedRegion = ws.Range( _
        ws.Cells(FIRST_DATA_ROW, FIRST_IMAGE_COL), _
        ws.Cells(ws.Rows.Count, ws.Columns.Count))

    ' Only react when the edit touches B2+ (ignore row 1 / column A)
    Set touched = Intersect(Target, allowedRegion)
    If touched Is Nothing Then Exit Sub

    ' After transpose/paste, Target may be only the first cell (e.g. B2).
    ' Always clean the full used URL block from B2 onward on this sheet.
    Set dataRange = GetCleanerDataRange(ws)
    If dataRange Is Nothing Then Exit Sub

    CleanAmazonUrlsInRange dataRange
    Exit Sub

ErrorHandler:
    Application.EnableEvents = True
End Sub

' Optional: call from a transpose macro after writing values.
Public Sub CleanAmazonUrlsOnSheet(ByVal targetSheet As Worksheet)
    Dim dataRange As Range

    If Not IsAmazonCleanerTargetSheet(targetSheet) Then Exit Sub
    Set dataRange = GetCleanerDataRange(targetSheet)
    If dataRange Is Nothing Then Exit Sub
    CleanAmazonUrlsInRange dataRange
End Sub

Public Function IsAmazonCleanerTargetSheet(ByVal targetSheet As Worksheet) As Boolean
    If targetSheet Is Nothing Then Exit Function
    IsAmazonCleanerTargetSheet = _
        (StrComp(targetSheet.Name, MANUAL_SHEET, vbTextCompare) = 0) Or _
        (StrComp(targetSheet.Name, WRITEBUFFER_SHEET, vbTextCompare) = 0)
End Function

' Remove ._MODIFIER_ immediately before a supported image extension.
Public Function CleanAmazonImageUrl(ByVal rawValue As String) As String
    Dim trimmed As String
    Dim lowerUrl As String
    Dim regex As Object

    trimmed = Trim$(rawValue)
    CleanAmazonImageUrl = trimmed

    If Len(trimmed) = 0 Then Exit Function

    lowerUrl = LCase$(trimmed)
    If Left$(lowerUrl, 7) <> "http://" And Left$(lowerUrl, 8) <> "https://" Then
        Exit Function
    End If

    If InStr(1, lowerUrl, "amazon.", vbBinaryCompare) = 0 And _
       InStr(1, lowerUrl, "media-amazon.com", vbBinaryCompare) = 0 And _
       InStr(1, lowerUrl, "ssl-images-amazon.com", vbBinaryCompare) = 0 Then
        Exit Function
    End If

    On Error GoTo CleanFailed
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .IgnoreCase = True
        .Pattern = "\._[A-Za-z0-9_]+_(\.(jpg|jpeg|png|webp|gif))"
        CleanAmazonImageUrl = .Replace(trimmed, "$1")
    End With
    Exit Function

CleanFailed:
    CleanAmazonImageUrl = trimmed
End Function

Public Sub CleanAmazonUrlsInRange(ByVal targetRange As Range)
    Dim area As Range
    Dim originalValue As Variant
    Dim originalText As String
    Dim cleanedText As String
    Dim previousEnableEvents As Boolean
    Dim previousScreenUpdating As Boolean
    Dim dataValues As Variant
    Dim r As Long
    Dim c As Long
    Dim changed As Boolean
    Dim rowCount As Long
    Dim colCount As Long

    If targetRange Is Nothing Then Exit Sub

    previousEnableEvents = Application.EnableEvents
    previousScreenUpdating = Application.ScreenUpdating

    On Error GoTo RestoreSettings

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    For Each area In targetRange.Areas
        rowCount = area.Rows.Count
        colCount = area.Columns.Count

        If rowCount = 1 And colCount = 1 Then
            originalValue = area.Value2
            If Not IsBlankOrError(originalValue) Then
                originalText = CStr(originalValue)
                cleanedText = CleanAmazonImageUrl(originalText)
                If StrComp(originalText, cleanedText, vbBinaryCompare) <> 0 Then
                    area.Value2 = cleanedText
                End If
            End If
        Else
            dataValues = area.Value2
            changed = False

            If IsArray(dataValues) Then
                ' Excel usually returns 2D; a single row/col can still be 2D (1 x N / N x 1).
                If ArrayDimensionCount(dataValues) = 1 Then
                    ' Rare 1D array — normalize to row loop
                    For c = LBound(dataValues) To UBound(dataValues)
                        originalValue = dataValues(c)
                        If Not IsBlankOrError(originalValue) Then
                            originalText = CStr(originalValue)
                            cleanedText = CleanAmazonImageUrl(originalText)
                            If StrComp(originalText, cleanedText, vbBinaryCompare) <> 0 Then
                                dataValues(c) = cleanedText
                                changed = True
                            End If
                        End If
                    Next c
                Else
                    For r = LBound(dataValues, 1) To UBound(dataValues, 1)
                        For c = LBound(dataValues, 2) To UBound(dataValues, 2)
                            originalValue = dataValues(r, c)
                            If Not IsBlankOrError(originalValue) Then
                                originalText = CStr(originalValue)
                                cleanedText = CleanAmazonImageUrl(originalText)
                                If StrComp(originalText, cleanedText, vbBinaryCompare) <> 0 Then
                                    dataValues(r, c) = cleanedText
                                    changed = True
                                End If
                            End If
                        Next c
                    Next r
                End If

                If changed Then
                    area.Value2 = dataValues
                End If
            End If
        End If
    Next area

RestoreSettings:
    Application.EnableEvents = previousEnableEvents
    Application.ScreenUpdating = previousScreenUpdating
End Sub

' Used data block from B2 through last used row/column on the sheet.
Private Function GetCleanerDataRange(ByVal ws As Worksheet) As Range
    Dim lastRow As Long
    Dim lastCol As Long
    Dim colIndex As Long
    Dim colLastRow As Long
    Dim used As Range

    On Error Resume Next
    Set used = ws.UsedRange
    On Error GoTo 0

    lastRow = FIRST_DATA_ROW - 1
    lastCol = FIRST_IMAGE_COL

    If Not used Is Nothing Then
        lastCol = used.Column + used.Columns.Count - 1
        If lastCol < FIRST_IMAGE_COL Then lastCol = FIRST_IMAGE_COL

        For colIndex = FIRST_IMAGE_COL To lastCol
            colLastRow = ws.Cells(ws.Rows.Count, colIndex).End(xlUp).Row
            If colLastRow > lastRow Then lastRow = colLastRow
        Next colIndex
    End If

    If lastRow < FIRST_DATA_ROW Then Exit Function
    If lastCol < FIRST_IMAGE_COL Then lastCol = FIRST_IMAGE_COL

    Set GetCleanerDataRange = ws.Range( _
        ws.Cells(FIRST_DATA_ROW, FIRST_IMAGE_COL), _
        ws.Cells(lastRow, lastCol))
End Function

Private Function ArrayDimensionCount(ByVal dataValues As Variant) As Long
    Dim probe As Long

    On Error GoTo OneDimension
    probe = UBound(dataValues, 2)
    ArrayDimensionCount = 2
    Exit Function

OneDimension:
    ArrayDimensionCount = 1
End Function

Private Function IsBlankOrError(ByVal cellValue As Variant) As Boolean
    If IsError(cellValue) Or IsEmpty(cellValue) Or IsNull(cellValue) Then
        IsBlankOrError = True
        Exit Function
    End If
    If VarType(cellValue) = vbObject Or VarType(cellValue) = vbDataObject Then
        IsBlankOrError = True
        Exit Function
    End If
    If Len(Trim$(CStr(cellValue))) = 0 Then
        IsBlankOrError = True
    End If
End Function