1

I have an Excel 2007 Macro that adds a chart based on data from another sheet. The problem is that it works on all the computers in the office but one.

All office and excel settings are the same on all computers.

To test the problem I have the most simple macro that should Add a chart but on this installation of Excel 2007 nothing happens.

Sub Macro7()
 Charts.Add
  ActiveChart.Name = "Earnings Chart"
End Sub

This should just create a chart and name it.

The problem is the code line errors at ActiveChart the object does not exist.

Run-Time error '91'

Object variable or With Blovk variable not set

The strange thing is that this works on all other computers in the office accept this one.

I have tried an uninstall and reinstall of office, matched the settings in office to that of a computer that it works fine on.

It seems like the Charts.Add is completely missing from this specific install of excel.

Any suggestions would be greatly appreciated.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2785314
  • 11
  • 3
  • Is the `.add` method showing in lowercase on the affected machine? That would seem to indicate something wrong. Valid methods should be automatically capitalized I think. – David Zemens Sep 16 '13 at 20:39
  • Also, check to see if this resolves it: `Charts.Add().Name = "Earnings Chart"` – David Zemens Sep 16 '13 at 20:42
  • That was a typo in the question. Yes it is Charts.Add showing in the editor. – user2785314 Sep 16 '13 at 20:46
  • A shot in the dark: Do you have any variable, class, or procedure in a module which is named `Charts`? – Siddharth Rout Sep 16 '13 at 20:47
  • Nope, as mentioned this Macro works perfectly other machines in the office. – user2785314 Sep 16 '13 at 21:11
  • Does David's code work? If you add `Debug.Assert ActiveChart <> Nothing` right after the `Charts.Add` line, does the macro stop? – Ioannis Sep 16 '13 at 21:22
  • David's code changed the name of the current sheet to "Earnings Chart". No chart was created though. – user2785314 Sep 16 '13 at 21:49
  • the `Debug.Assert ActiveChart <> Nothing` line gave me a compile error saying `illegal use of object` and highlighted `Nothing` – user2785314 Sep 16 '13 at 22:02
  • It was my typo, it should have been `Debug.Assert Not ActiveChart Is Nothing`. Any add-ins installed on this PC only? – Ioannis Sep 16 '13 at 22:19
  • No, the PC's all have the same add-ins and references. The code stopped at the debug line. – user2785314 Sep 16 '13 at 22:23
  • not sure.. Maybe some Add-ins have Windows API calls that cause conflicts with that PC, but I will stop the conjectures here - there will be real experts who have a lot more to say :) – Ioannis Sep 16 '13 at 22:52
  • Your code works for me. Try this, Add `Doevents` after `Charts.Add` Does it help? BTW, I always suggest not to use ACTIVE sheet/chart/cell. Directly work with objects like @TimWilliams has done in his answer below. You might also want to see [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179). – Siddharth Rout Sep 17 '13 at 05:10
  • I suspect your correct loannis. I don't think it's a coding problem since it works on the 3 other computers. I think it's a software conflict, registry error, corrupt file or other unknown problem. – user2785314 Sep 17 '13 at 13:57
  • `DoEvents` didn't do anything. I always try to use select and active statements as little as possible. When i code it as TimWilliams example I get a type missmatch error on the `set cht = Charts.Add` – user2785314 Sep 17 '13 at 14:00

1 Answers1

1
Sub Macro7()
    Dim cht as Chart
    Set cht = ActiveWorkbook.Charts.Add 'or ThisWorkbook?
    cht.Name = "Earnings Chart"
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125