Runtime Error 1004

[netspeedz]

Had some problems with a computer so I had to completely reinstall Windows XP and subsequently Microsoft Office 2003. I have an Excel document that had no changes between the time I reinstalled Windows XP and Excel and opened up the Excel document to test the new Office install.

In this Excel document is a button with a macro assigned to it (which, again, has not changed for months). I click on this button and get the error, 'Runtime error 1004. Application defined or object defined error.' I have posted the macro code below (code that charts a range of cells populated with a web query - simple stuff) that the VBA debugger chokes on and have highlighted the specific line as well. It can't be anything with the VBA code or the worksheet because I can take the same Excel document to another computer with Office 2003 installed and it works fine. I'm thinking it has to be a missing Excel/VBA component in the new install - which also baffles me because when I reinstall Office, I do a complete install - getting everything.

Here's the macro code VBA debugger doesn't like:

Sub UpdateChart()


Set Currentchart = Sheets("External Data").ChartObjects(1).Chart


picnm = ThisWorkbook.Path & Application.PathSeparator & "chart.gif"


Currentchart.Export filename:=picnm, FilterName:="GIF"


Image1.Picture = LoadPicture(picnm)


End Sub

The text in red is the line VBA Debugger stops on. Is there some kind of web/internet VBA component I'm forgetting to reinstall?

Any assistance on this would be appreciated.

------------

 

[sijpie]

I think it is something else, but it is strange that the code works on a different PC. Is that on exactly the same spreadsheet? or could there be a chart missing?

Add a few lines to the code as follows (I have also Dim'd your variables, bad form not to tell VBA what they are...)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> UpdateChart()<br><SPAN style="color:#00007F">Dim</SPAN> currentchart <SPAN style="color:#00007F">As</SPAN> Chart<br><SPAN style="color:#00007F">Dim</SPAN> picnm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, image1<br><br><SPAN style="color:#00007F">Set</SPAN> currentchart = Sheets("External Data").ChartObjects(1).Chart<br><br><br>picnm = ThisWorkbook.Path & Application.PathSeparator & "chart.gif"<br><br><SPAN style="color:#00007F">With</SPAN> currentchart<br>    Debug.Print .Name<br>    Debug.Print .ChartTitle<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>currentchart.Export Filename:=picnm, FilterName:="GIF"<br><br><br>image1.Picture = LoadPicture(picnm)<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Now click in the grey border just to the left of 'With Currentchart'. A red dot should appear and the line highlighted. (You have created a bookmark)

Now run the macro as usual. It will stop here at the bookmark. Press the F8 key to step through the code. In the little left panel belo the code window (called immediate window, if it isn't there press Ctrl-G) You should see the name of the chart. Or VBA may complain that the object is required, in which case the chart is not assigned to Currentchart in the line
Set Currentchart = sheets(...)

let me know the results

 

[netspeedz]

I really don't want to change any of the code as the same exact spreadsheet works on another system.

------------

I think it is something else, but it is strange that the code works on a different PC. Is that on exactly the same spreadsheet? or could there be a chart missing?

Add a few lines to the code as follows (I have also Dim'd your variables, bad form not to tell VBA what they are...)

Sub UpdateChart()
Dim currentchart As Chart
Dim picnm As String, image1

Set currentchart = Sheets("External Data").ChartObjects(1).Chart


picnm = ThisWorkbook.Path & Application.PathSeparator & "chart.gif"

With currentchart
Debug.Print .Name
Debug.Print .ChartTitle
End With

currentchart.Export Filename:=picnm, FilterName:="GIF"


image1.Picture = LoadPicture(picnm)


End Sub


Now click in the grey border just to the left of 'With Currentchart'. A red dot should appear and the line highlighted. (You have created a bookmark)

Now run the macro as usual. It will stop here at the bookmark. Press the F8 key to step through the code. In the little left panel belo the code window (called immediate window, if it isn't there press Ctrl-G) You should see the name of the chart. Or VBA may complain that the object is required, in which case the chart is not assigned to Currentchart in the line
Set Currentchart = sheets(...)

let me know the results

 

[chuckchuckit]

This may not apply to your situation but I recently had problems with my computer also and re-installed Excel to try to fix it. Some of my code would still not work on certain lines that used to work just fine as I was getting object error 438 I think it was. Tried Malware and virus checking which turned up nothing. But I was also getting other symptoms like slower computer booting and occasional booting lockups.

What it turned out to be was my CPU had been overheating due to dust buildup around it. It fried the CPU and when I had it tested it was failing certain CPU math tests. Using compressed air on the CPU to rid all the dust can lower the CPU temperature significantly. But in my case the CPU damage was done and it was fried. Replaced the CPU chip and VBA code worked again.

I do not have the program to test the CPU as I had to take it in to a computer repair place. There might be some people here that could direct you to software to self test your CPU etc. Luckily my motherboard was not fried, just the CPU.

 

[netspeedz]

Appreciate the reply. I'm meticulous about maintenance on my system (A Dell Latitude D630). It's the system I develop Excel apps on as well as troubleshoot for routers and switches (as I'm a network engineer). Good-none-the-less. In any case, I'll put a can of dust on it and see what happens. It's been a solid performer over the past several years so it deserves the extra treatment.

Your signature line also contains some good advise so your response worked some 'double duty'

What it turned out to be was my CPU had been overheating due to dust buildup around it. It fried the CPU and when I had it tested it was failing certain CPU math tests. Using compressed air on the CPU to rid all the dust can lower the CPU temperature significantly. But in my case the CPU damage was done and it was fried. Replaced the CPU chip and VBA code worked again.

 

[chuckchuckit]

Thanks. I now monitor my CPU all 4 cores temps since my old i7 CPU 1st generation is getting difficult to find. Various core temp monitoring free software out there but I use this free one:

Core Temp

If you download it may want to "be sure" to opt out of all the various things they will try to download with it, but checking the right boxes to opt out will just download it by itself. I don't use the add-ons that are additional downloads but some might be useful.

Runs well and Windows7 taskbar option can have just the core temps displayed on the task bar. So over many months if the CPU core temps at idle are getting slowly higher than previous, means time to use compressed air on the CPU dust again (while computer power is off of course).

 

[netspeedz]

I have used Core Temp in the past. I run XP SP3 so I don't keep it running full time but have used it periodically. I typically clean the processor on my laptop when I hear the fan go on for extended periods. Then I know its time for maintenance.

------------

Thanks. I now monitor my CPU all 4 cores temps since my old i7 CPU 1st generation is getting difficult to find. Various core temp monitoring free software out there but I use this free one:

Core Temp

 

[chuckchuckit]

I have used Core Temp in the past. I run XP SP3 so I don't keep it running full time but have used it periodically. I typically clean the processor on my laptop when I hear the fan go on for extended periods. Then I know its time for maintenance.

------------

That is a good way to keep in mind.

When they installed my replacement CPU it was a faster GHZ but motherboard not designed for it so turbo mode would put fans on constant and CPU temp would go over 100 deg centegrade (mine is maxed at 105). So would have burned out soon again. So for anyone using Windows7 to turn off turbo mode: via Control Panel, Performance and Info, Advanced Power Settings, Maximum Allowable Processor State, set to 99% instead of 100% and turbo never kicks in again. Computer OK now and still same GHZ as before without turbo with this faster CPU. No problems so far anyway.

 

[netspeedz]

Good tip. Eventually, someone will find this via a Google search

------------

When they installed my replacement CPU it was a faster GHZ but motherboard not designed for it so turbo mode would put fans on constant and CPU temp would go over 100 deg centegrade (mine is maxed at 105). So would have burned out soon again. So for anyone using Windows7 to turn off turbo mode: via Control Panel, Performance and Info, Advanced Power Settings, Maximum Allowable Processor State, set to 99% instead of 100% and turbo never kicks in again. Computer OK now and still same GHZ as before without turbo with this faster CPU. No problems so far anyway.

 

[chuckchuckit]

Good tip. Eventually, someone will find this via a Google search

------------

Yes, my Google searching comes back to MrExcel a lot.

So to perhaps benefit others: Took me a while to figure out how to turn off the CPU Turbo Mode without having to go into the complicated and sometimes risky BIOS settings. Just setting that above mentioned state to 99% will stop the processor from kicking on the turbo, as it must see 100% CPU speed being used before it will then turn on the Turbo. Which of course heats the CPU more and stresses it more. My CPU runs at around 65 degrees C at idle. My wifes newer computer Asus MB with 3rd generation i5 Intel designed CPU at idle runs at only around 28 C which is not much more than room temp. Newer CPU Chips faster and better designed.