+ Reply to Thread
Results 1 to 9 of 9

Out of Memory Error

  1. #1
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Out of Memory Error

    Hello,
    I am making a top 150 selling products report. It shows the stock number, the name of the product and then some sales info. I am using the following code to set a comment in the cell with the product name to show an image of the product. The code sets the image is based on a path that is defined in a different cell in the same row. It runs fine for half or more of the report, then it errors out saying it is out of memory. How can I fix this?

    Here is my code:

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Out of Memory Error

    Just wondering about the image and whether that could be a problem.. I'd try commenting out the .Fill.UserPicture line, just to see if that is the problem. If the image is large and being put into the comments of many cells it could be an issue.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Out of Memory Error

    Quote Originally Posted by Arkadi View Post
    Just wondering about the image and whether that could be a problem.. I'd try commenting out the .Fill.UserPicture line, just to see if that is the problem. If the image is large and being put into the comments of many cells it could be an issue.
    Thanks, I'll try that, although without that part, the code is kind of useless, because the only thing in the comment is the picture. If that does end up being the problem is there a way to optimized that? At the moment, its just grabbing them off the internet. Is there a way/would it be better to have the code save the images locally before getting them?

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Out of Memory Error

    I only meant it as a test to see what you need to troubleshoot, I know a solution would be needed afterwards, but it gives a starting point is all

    Making the images smaller would be a way of reducing the space they take up.

  5. #5
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Out of Memory Error

    Gotcha, well that line is definitely the issue. Unfourtanately I can't reduce the size of the images, but they are all under 200KB, so they are not very big to start with.

    I dont know much about Error Handling, but I added an 'On Error Resume Next' statement before the '.Fill.UserPicture' line. It seems to have fixed the problem, but is there some reason why using this would be bad?

    Thanks so much for your help!

    Here is my code:

    Please Login or Register  to view this content.

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Out of Memory Error

    Well that depends on what you want.. but what on error resume next means is that it does not execute the line giving the error and moves on.

    Follow up question: Does it always error at the same row of data? if you leave out that row by skipping it or deleting it temporarily, does the code work for the next line? What I'm getting at with that is that it *could* just be the one picture causing the issue?

  7. #7
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Out of Memory Error

    It looks like it always errors out when i = 116, if I have it skip row 116 then it finishes fine.

    The weird thing is that if I use 'On Error Resume Next', when it is finished row 116 has the picture it is supposed to have. So do all of the rows around 116.

  8. #8
    Forum Contributor
    Join Date
    08-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: Out of Memory Error

    Nevermind. It looks like the problem was that the row in question had an incorrect path. Fixing this fixed the problem without having to use error handling. It is a little strange that it the error was Out of Memory, but oh well.

    Thanks again for your help.

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Out of Memory Error

    Great chaddug Thanks for marking the post as solved! And for the rep as well!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-08-2011, 08:52 AM
  2. Out of Memory error
    By ingineu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2006, 08:08 PM
  3. [SOLVED] Out of Memory Error
    By Gregory Kip in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2006, 06:35 PM
  4. [SOLVED] How to handle error 8007000e Memory Error
    By L. A. M. in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-27-2005, 11:05 PM
  5. [SOLVED] Out of Memory Error 7
    By Paul Black in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-24-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1