Link to home
Create AccountLog in
Avatar of AVSteve

asked on


I'm running office 2010 and recently added visual studio pro 2008 (requried by a project I am starting).  I've also kept up with Microsoft updates so I've made some of those too.  How my excel VBA macros don't work.  A snippet of code which stops with "application defined or object-defined error on the last line.

Range("end_end").value = activerows + 5
ActiveWorkbook.Names("Master").RefersTo = "=pricing!$f$6:$bn$" & LTrim(Str(activerows + 5))
ActiveWorkbook.Names("Dealers").RefersTo = "personal.xls!dealers"
noter = 0

pr = "$a$1:$bn$" & LTrim(Str(activerows + nr + 6))
ActiveSheet.PageSetup.PrintArea = pr

Code worked before but not now
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

What is the value of pr at this stage?
You may need to post your xlsx files (or an excerpt representation of them). Trying to reproduce your code is not possible, considering it is guesswork to set up the Master, Dealers, Pricing and Personal.xls references.

Other than that, I got no error on the line: ActiveSheet.PageSetup.PrintArea = pr
However, I fudged the value in "pr".
Avatar of Norie

Where does the error occur?

Have you stepped through the code to check the values of the variables?

By the way, why are you using LTrim and Str?

You shouldn't need thten when concatenating.
Avatar of AVSteve


ok, I've learned some more.  My macros come (for the most part) from a personal.xls which is xlstart so it always there.  the file is hidden so it doesn't confuse a user,  All used to work.  Now with the updates it doesn't work --- sortof.

If I open the macro file itself and run the macro (no hidden macro file), it works.  if I remove the hidden macro file, start a new program copy the macro in -- it works.

The problem is that the macro comes from what I would call a macro library and when running is confused about what an "actitivesheet" is.   Is there a different way to do a macro library?  A way to ensure the right context?  A way to say activesheet (and similar) without saying it?
Avatar of AVSteve


Apparently the righter-way for a macro library is an add-in.  I tried that.  Still no joy.  And the problem also effects the selection object as well as the active sheet one.
ActiveSheet used in the Personal.xlsx file will work fine. I suspect the error lies elsewhere. Please try what Norie stated and step through the macro and check the values of "activerows" and "nr" (hover over them to view their values)
Avatar of AVSteve

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of AVSteve


The other answers might have led to this resolution but they never followed up their initial posts.