Run-time error'7: Out of Memory error

[Snake Eyes]

Greetings all.
I get the is error when I run the following macro in Excel 2010. I have tried a reboot and I have 5.2 GB of free Ram. Can anyone tell me what I can do to get past this error.

Sub Reset_Hyperlinks()'
' Reset_Hyperlinks Macro
'
    Dim hl As Hyperlink
    For Each hl In ActiveSheet.Hyperlinks
        hl.Address = Replace(hl.Address, "C:\Users\mayala\Application Data\Microsoft\", "G:\Data Files\VMS\")
    Next
'
End Sub

 

[Jerry Sullivan]

Hi Snake Eyes,

For what it's worth, I don't see any reason that code should cause a memory error by itself. It worked fine for me in a quick test.

As a next step, you might try adding a Debug.Print statement then step through the code executing one line at time using the F8 Key.

Sub Reset_Hyperlinks()'
' Reset_Hyperlinks Macro
'
    Dim hl As Hyperlink
    For Each hl In ActiveSheet.Hyperlinks
        [COLOR="#0000CD"][B]Debug.Print hl.Address[/B][/COLOR]
        hl.Address = Replace(hl.Address, "C:\Users\mayala\Application Data\Microsoft\", "G:\Data Files\VMS\")
    Next
'
End Sub

This may quickly isolate the problem if....

1. The code execution jumps to Event Code that is being triggered.

2. The For Each loop repeats continuously (more times than the number of hyperlinks you think you have).

3. The code performs okay until it hits one specific hyperlink that is causing the problem (eg special characters, or unexpected properties)

 

[Snake Eyes]

Thanks for the tip Jerry. I run your suggested script and nothing different happens. What output should I be expecting?

 

[Jerry Sullivan]

Here's a better explanation of what I suggested:

Open the VBE Editor then check to see if the Immediate Window pane is visible. If it isn't, then key Ctrl-G to have it displayed.

Then click your cursor in the modified Reset_Hyperlinks macro code shown in post #2. Then begin hitting the F8 key repeatedly and you'll be able to follow the execution of your code one line at a time.

The code should loop around the For Each...Next block. Each time a Debug.Print is executed, the current value of hl.Address should be written to the Immediate Window like...
C:\Users\mayala\Application Data\Microsoft\MyPDF1.pdf
C:\Users\mayala\Application Data\Microsoft\MyWordDoc.docx
....

If your code performed without error, the execution would eventually reach the End Sub statement. If there are 10 hyperlinks in the ActiveSheet, then the Immediate Window should list all 10 addresses before they were potentially changed. (note if you hit F8 again after End Sub is executed it will restart the macro, so stop keying F8 after End Sub).

What you are hoping will happen, is that while following the execution of the code, the "Out of Memory" error occurs. Seeing exactly what statement (possibly which hl.address) is being executed when that error occurs will provide a big clue as the reason for the error.

 

[Snake Eyes]

Thank You again Jerry, That did the trick. I found one out of several dozen hyperlinks that caused the error. I have since changed it so that it is no longer a problem. I appreciate you taking the time to explain the process.

 

[shg]

Good job, Jerry!

 

[Jerry Sullivan]

Good job, Jerry!

Thanks shg

Thank You again Jerry, That did the trick. I found one out of several dozen hyperlinks that caused the error. I have since changed it so that it is no longer a problem. I appreciate you taking the time to explain the process.

Snake Eyes, Could you share with us what was unusual about the one hyperlink that caused the error?

 

[Snake Eyes]

Not too sure what the problem was Jerry. Below are two Links to my companies Sharepoint site. The first link is the problem link. The second is the solution. The main difference is that the problem link is filtered to display results for my name only.

http://internal.varian.com/os/css/SWInstall/Lists/T-Box_Checklist/Default.aspx?View={8698BFBD-19F0-4F55-9DA5-EFDA4A7F2FFD}&FilterField1=T_x002d_Box_x0020_Assigned_x0020&FilterValue1=Martin%20Ayala&InitialTabId=Ribbon%2EListItem&VisibilityContext=WSSTabPersistence

 

[Snake Eyes]

Here is the second Link that does not throw the error...

[URL="http://internal.varian.com/os/css/SWInstall/Lists/T-Box_Checklist/Default.aspx?View=%7b8698BFBD-19F0-4F55-9DA5-EFDA4A7F2FFD%7d&InitialTabId=Ribbon%2EListItem&VisibilityContext=WSSTabPersistence&FilterClear=1"]
http://internal.varian.com/os/css/SWInstall/Lists/T-Box_Checklist/Default.aspx?View=%7b8698BFBD-19F0-4F55-9DA5-EFDA4A7F2FFD%7d&InitialTabId=Ribbon%2EListItem&VisibilityContext=WSSTabPersistence&FilterClear=1[/URL]