running a Macro in 2007 which was created in 2003

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Hi there, I hope you can help, I am not VB literate unfortunately but I am
trying to run a macro with the following line of code which creates an error:

LastRow = Master_Wksht.Range("N" & Rows.Count).End(xlup).Row

Is there a change between versions which means this line won't work any more?
 
Simon,

That line is fine in E2007 provided you have 'SET' Master_Wksht but if you
hadn't done that it wouldn't have run in earlier versions of Excel

Set Master_Wksht = Sheets("Sheet1")

What have you dimmed LastRow as it should be long.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Hmm,

I forgot the obvious question. What is the error message?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Hi Simon, you've got me, being new to 2007 too. No doubt someone will soon
give you a definitive answer soon, but until then...

I had a problem running a macro that tripped on the line,
"Windows("Accounting.xls").Activate" which was merely to switch between 2
windows in the one instance of Excel. It tripped the first time giving an
error message, so I went into debugging, stepping over all the commands up to
that one and then "stepped into" from there. It found its way through without
an issue and then worked perfectly after that. So its possible that if you do
the same, yours too may "settle in" (OK, some Excel geek can ridicule the
terminology, but it worked ;-). )

Remember that any commands before the one that tripped will have been
executed, so you may not want them to execute again! Turn on the "debug"
toolbar and use the "step over" and "step into" buttons. Be aware that the
"Continue" button (right-pointing triangle) will run from the point you're at
right to the end.
 
Kevryl,

I think it highly unlikely a macro would 'settle in' and learn to live with
an error. You note you were working in 2007 and called another workbook using

Windows("Accounting.xls").Activate

If it was a 2007 workbook then that line will fail because of an incorrect
file extension which should be

Windows("Accounting.xlsx").Activate

or another appropriate extension for the type of 2007 workbook
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
This is a guess since you didn't say what error occurred.

xl2007 can open older versions of files in compatibility mode -- where the
number of rows is 64k (not 1 meg).

So depending on what workbook/worksheet is active, that unqualified Rows.count
could be 64k or 1Meg.

I'd qualify that range:

with Master_Wksht
lastrow = .range("N" & .rows.count).end(xlup).row
end with
 
If the code fails when run at full speed but works when stepped through,
all else being equal, it usually means Excel has some housekeeping to
do, that it hasn't finished before the command.

Inserting a one-word line

DoEvents

before the line that shows the error will often give Excel a chance to
do all that background stuff. Excel 2007 is more prone to this issue
than 2003.

- Jon
 
G'day Mike,

I do understand your skepticism, but I've had similar where something has
fouled up the first time but then "settled in". No, I can't explain it.

I'm still using the .xls format on these 2 files (save as 97-2003) as I'm
not totally convinced yet that I want to stay with 2007. I did save an
inventory file as .xlsm because from what I've read I thought I had to to
preserve the macros, but that.s misleading because my macros work ok in .xls.

I was about to save the accounting files as a .xlsm too, but I re-thought
that quickly because it wanted me to put an undersore in front of my
range-names - a couple of hundred of them, many referred to in macros of
which I also have dozens.

My decision now is whether to (A) stay with Win 7 Office 2007 and weather
it, (hoping Office 2010 will address all the slow-down issues), or (B) roll
back to XP & Office 2000, or (C) start a tedious switch to Linux (prob
Ubuntu?) and Open Office.org and eventually become Microsoft independant.

Any thoughts on these choices would be appreciated. :-)
 
Back
Top