[SOLVED] VBA hyperlink replace function out of memory

VBA hyperlink replace function out of memory

Im trying to replace hyperlinks in my excel worksheet with new links but I keep getting Run-time error '7' out of memory . I have 8gb of free memory and im using 64bit excel 365.

I tried reducing the number of lines to 50 lines but still the same error. it shows the code stops at "h.Address = Replace(h.Address, sOld, sNew)" even if I remove the if statement I searched and used multiple versions of this code:

Sub test()
    Dim h As Hyperlink
    Dim sOld As String
    Dim sNew As String

    sOld = "http://portal.something.local/"
    sNew = "WorkDammit"
    For Each h In ActiveSheet.Hyperlinks
        If InStr(1, h.Address, sOld) Then
            h.Address = Replace(h.Address, sOld, sNew)
        End If
    Next h
End Sub

Solution

You might try deleting then adding.

Sub test()
    Dim h As Hyperlink
    Dim sOld As String
    Dim sNew As String

    sOld = "http://portal.motovantage.local/"
    sNew = "WorkDammit"
    For Each h In ActiveSheet.Hyperlinks
        If InStr(1, h.Address, sOld) Then
            CellAddr = h.Range.Address
            Range(CellAddr).Hyperlinks.Delete
            Range(CellAddr).Hyperlinks.Add anchor:=Range(CellAddr), Address:=sNew
        End If
    Next h
End Sub