Here are the EXCEL-L postings:
This has got me thinking - because I train Excel and it would be easy and great to create a sequence that contains all of the bad habits we see. It would be great to compile a list to include things like:
Formulae like =SUM(A3+A4).
Or even worse +=SUM(A3+A4) for ex lotus users.
Clicking and dragging a formula down the side of a list - instead of double-clicking the fill handle.
Building graphs without F11.
Not ALT+ENTER'ing sections in a large formula.
Linking to workbooks without using a range name.
Merged cells.
"Deleting" cell contents by entering a space.
Shared workbooks.
Ghastly colours.
Too many rows.
Too many array/SUMPRODUCT formulas.
Not using pivots.
Not using named ranges.
Pie charts.
3D charts.
Childish CF icons in 2007+.I'd disagree about F11 for graphs - the wizard does (for pre-2007) stop and make you think about the graph you are creating (mind you, it doesn't stop the little beggars from putting in negative numbers in % bar charts! Aaaaagh! ). My pet hate is the blank line between headers and the data, with merged cells coming a very close second.
(at least in part): >Merged cells.
Horizontal can go use "center across..." but vertical needs to stay untill there is a vertical version of "CA..."You brought back memories of an old(er) lady I once taught "basic" IT to, who, even after working thru all the standard "build up" exercises C&G, BTec, RSA, Clait and a few of my own (to fill the gaps left by the others) still preceded EVERY equation with =SUM on the grounds that "we are doing Sums". It would have been easier if Excel could have objected to any of her weird formulas like:
=SUM(B3%)
=SUM(B5/B4%)
=SUM(B3/B4*B5)
=SUM(B4^B3).
All gave the result you would expect (without the SUM() that is), so the question "Why?" came up a lot.There is always something on everyone's list that someone will disagree with. I disagreed with =SUM(A3+A4), I have no problem with it. Now, =SUM(A3+A4+A5+A6+A7+A8) might be a little silly. And =SUM(A24:B24+C24:D24) just plain won't work - but Excel will tell you that.
My peeves off top of my head:
Leaving #DIV/0! In cells.
Distributing worksheets whose cells contain data validation or other formulae dependent on items in closed (local) workbooks.
Leaving Calculation set to Manual in a workbook you give someone else.
Failing to turn off GetPivotTableData [hate it hate it hate it].
Failing to set these:
# worksheets to 1... and leaving empty sheets in a workbook.
Recently Used Files to 9 (or whatever is max).
Always use Full Menus.
Put the Web address bar in the tool bars.
Buying and using Excel 2007 without a gun to your head or a client likewise.
Forgetting to reset the Delimiter when using Text to Columns.
Not partnering your Excelling experience with a good book and Google.
Using IsEmpty to test a Cell instead of testing the cell's formula length.
Leaving Circular Reference in the status bar unattended.
Mixing too many formats of various kinds.
Naming files with a single quote in the file name (legal but silly).
Using Range("A1") without s worksheet qualification.
Likewise for .Cells.
Creating workbook level names for ranges on a sheet (that might get replicated on another sheet) ... makes coding very difficult, always include the sheet name.
Leaving macro recorder code uninspected / without being made generic.
Deleting rows in a forward counting loop (VBA).
Messing with OWC 10.0 or OWC 11.0 spreadsheet tool AT ALL. (undocumented and lost a month of my life and crashed Excel numerous times).Disagree. I put the value in all the cells then use CF to determine the middle one and hide all others. Merged cells are evil.
=SUM(A3+A4) is dumb, you are over-working Excel.
Cell merging is not a "don't do" I claim it is a bug.
Password protecting internal documents that will be used over years and not letting someone else know the password.
Sharing large WBs that also have links.
VBA solutions for where simple formulas would work
Using complex array formulas where simple VBA could be used.
Trying to use Excel as a database when a database would be the better solution.
I see you're in the UK, so this may be difficult to comprehend:
If CA is Canada (on the Internet), it's horizontal.
If CA is California (in the USA), it's vertical.
So you see that there is a vertical version of CA...Just check out a map.
;-)hard-coding variables in formulas
> Formulae like =SUM(A3+A4)
>
Well, =SUM(A3,A5) has some application compared to simple =A3+A5 when the data can contain mixed text and numbers.> Or even worse +=SUM(A3+A4) for ex Lotus users.
>
=+SUM(A3+A4)
I still come across workbooks with Lotus compatibility setting on worksheets. AFAIK it only affects lookups, but it may have other effects - can anyone remember?And financial modellers still use +name in a formula when they want it to be interpreted by implied intersection.
> Clicking and dragging a formula down the side of a list - instead of
> double-clicking the fill handle.
>
Needed when the list has gaps or is just in the wrong place for Excel to know where to fill to.> Building graphs without F11.
>
What's so bad about that?> Not ALT+ENTER'ing sections in a large formula.
>
Nice .. easier to read in 2007+ than earlier.> Linking to workbooks without using a range name.
>
Deffo.Ahem ... http://www.amazon.com/dp/190540400X <GD&R>
Although I think the OP was looking more for scenarios when Excel is being used inappropriately for analysis and communication rather than technical flaws. e.g. http://caseelse.net/2008/08/06/why-not-to-use-excel-for-data-gathering/.
There's some discussion on the Eusprig yahoogroup about its use in statistics and there are a few skeptics in this field. Patrick Burns' Spreadsheet Addiction page and THE UNRELIABLITY OF EXCEL'S STATISTICAL PROCEDURES
Dumb or not, I use it from time to time when I want all formulas to appear (in terms of formula text) identically in a column, for easy eye-scan / auditing when I turn Formulas on, and other formulas begin with SUM for a not-so-simple reason, so everything appears uniform and I can see symmetry where I want to see it. That way I know for sure that all my formulas are similar. My methods have been called worse than dumb before!
Enough already !!!! Wow - this is certainly a sure-fired way to press some react buttons ! I didn't know we were so passionate about certain things in Excel - and the hatred towards merged cells - I love them! I've compiled a list and I'm sure it will keep growing - so when I get the time......
I don't understand the CA Canada thing - yes I'm in the UK so I must be missing something obvious.
Thanks for the contributions guys...
Someone used the abbreviation CA for Centre Across and then said there was no vertical equivalent. CA is the domain name country element for Canada but is also the abbreviation (zip code etc) for the state of California. California is definitely vertically stacked where as Canada is Horizontal when you take into account their ratios of depth (latitude) to width (longitude).
And they both have a place called Ontario!
For my own peeves:
Recording a macro without understanding what it does, and then claiming to be an expert in Excel macros (the same goes for PowerPoint and Word, I guess).
Trying to use more columns than be comfortably scrolled or viewed on a single screen.
As per any electronic publication, unattractive fonts such as Comic Sans Serif, or a mishmash of fonts that I think of as "ransom note typography".As a trainer/developer/Jack of all trades, 3 of the things that I always emphasise (so in essence is reverse-bad habits) are:
1) Don't be reliant on mouse, i.e. LEARN the short-cut keys (I'm not talking about the usual suspects c,x v, but ones like CTRL+ or CTRL*).
2) understand the basic relationships between Word And Excel (lines, paragraphs, tabs, cells).As to databases, I agree that Excel shouldn't be used as a DB, but should be always considered as the front-end to a DB. (Most companies nowadays prohibit their users from using Access - if they even have it installed at all!)
> As to databases ...
>
There's my top tip just there. Don't be tempted to use Excel as a database, but don't think that means you need to use A****s.But you can still use .db even without Access.
Great topic, love the answers. Don't think I saw: MEGA-FORMULAS.
Formatting whole row/s and/or column/s when only a few cells would suffice and ending with huge files as a result and then complaining about the size.
Writing a 200 line macro when a built-in Excel function is available to do the same thing.
Blank columns between data columns.
I'd disagree about F11 for graphs - the wizard does (for pre-2007) stop and make you think about the graph you are creating (mind you it doesn't stop the little beggars from putting in negative numbers in % bar charts! Aaaaaghg!) My pet hate is the blank line between headers and the data, with merged cells coming a very close second.
Reminds me of the shared workbook that was growing to insane size, because one of the muppets was highlighting whole rows for formatting , then his co-muppet was selecting said rows and ALL THE WAY DOWN TO 65536 then deleting the rows, thus storing 60,000+ rows in the tracked changes. Repeat a few times and see how big that sucker got!!! Users should not be trusted to write their names! With a 2 inch brush! On a barn door!
Yeap, that's the trick. My boss loves it. And he includes formulas. If he needs a formula for eleven rows and a little formatting he copies it to the end of the sheet. Then he calls me when the file is 18 Mb and hangs up his runs and tells me "will you take a look at this? It doesn't work any more." Then he shakes his head and goes "maaaan . . . this Excel thingy . . ."
Tell him it's either a wetware problem or a PBKAC error
:-)I've been told a number of times that Excel and Access are the same program. Yes, they were all in management.
That's what I call job security. Something going on here with the "Excel macro thingy". Nobody knows or wants to know and that makes us all happy.
And this is the other half of the reason good programmers never document their work. If it was hard to write, it should be hard to read! :-D
>Disagree. I put the value in all the cells then use CF to determine the
>middle one and hide all others. Merged cells are evil.Nice work-around, but not what I meant, I rotate text 90' and then center across a number of rows, your solution would just leave my text centered in one (large height row).
>I see you're in the UK, so this may be difficult to comprehend:
> If CA is Canada (on the Internet), it's horizontal.
> If CA is California (in the USA), it's vertical.>So you see that there is a vertical version of CA...Just check out a map.
>;-)I meant CenterAcross (vertically).
I have . . . I have . . . together with a few other things that can not be repeated in mixed company.
Don't forget ID10T and ESTO.
>Great topic, love the answers. Don't think I saw: MEGA-FORMULAS.
Oh go on we all love the odd mega-formula even if only to keep our brains working.
>Reminds me of the shared workbook that was growing to insane size,
>because one of the muppets was highlighting whole rows for formatting ,
>then his co-muppet was selecting said rows and ALL THE WAY DOWN TO 65536
>then deleting the rows, thus storing 60,000+ rows in the tracked
>changes. Repeat a few times and see how big that sucker got!!!
>Users should not be trusted to write their names! With a 2 inch brush!
>On a barn door!Nice idea but the EndUser does need some input, now if only they were consulted at the planning stage in place of their bosses who may not (don't) know what is required...
>And they both have a place called Ontario!
>
>For my own peeves:
>...
>As per any electronic publication, unattractive fonts such as Comic Sans
>Serif, or a mishmash of fonts that I think of as "ransom note
>typography".Ahh no not the Font Police, Some users react better to a serif font (and some don't).
>>
>> As to databases, I agree that Excel shouldn't be used as
>> a DB, but
>> should be always considered as the front-end to a DB
>> (Most companies nowadays prohibit their user from using
>> Access - if they
>> even have it installed at all!)
>>
>>There's my top tip just there. Don't be tempted to use Excel as a database,
I found it a good starting point (once you understand NormalForm(s)) certainly gave me a foot up on SQL.
>but don't think that means you need to use A****s.And that from someone who sneers on the use of DreamWaver, FrontPage and the like in favour of a good PlainText editor...
I had a client that did that, formatted all rows to allow for growth. It was a multiple sheet spreadsheet and also got huge. When they migrated to 2007, they found some of the workbooks just didn't open. Nice little job fixing that.
Well as Nick Hodge maintains, Access is an Excel addin.
How about "Accept labels in formulas" aka "natural language formulas", so if a column heading is, say, 2010, and xlColumnLabels option is set, when you enter =SUM(2010) it silently changes it to =SUM('2010') and shows the sum of that column.
We can't document it, we don't understand it. Don't you realise it is all magic?
Refer to specific cell references in the VBA like
.Range("C10")
"Why not? The recorder does..."Use the same "text string" in lots of places inside the VBA Put lots of code in the _SelectionChange event.
Use lots and lots of borders, especially different kinds and colors.
>> Use lots and lots of borders, especially different kinds and colors.
OK, I did not want to write in again because I have already done my bit here, but had to add to someone's point above.
... So - What not to do: what someone wrote about *plus* keep every legacy sheet you've copied from itself so that whatever format changes you're making on the current sheet is sure to yield different formats than the legacy sheets, just *asking* for Excel's 4000-ish style limitation to come round and bite ya.
And then there is making Comment shapes grow and shrink with the workbook, I love that one.
And let's not forget leave little shapes with 0-height so that no one can see them -- especially hanging around areas with no data, that one might otherwise like to delete.
Thanks to all for the advice.
So many things to include in my next project (before I change employers) and Zero height shapes -neat0 :)
I really hate to see this thread keep wrapping around the world a second time but I have to ask.
How is that you refer to a range when you need to do so? And, what is wrong with referring to a range like .Range("A10")? Is there a better way and not just a better way from a personal preference?
And, depending on how the recorder options are set then you may or may not have the recorder record references like .Range("A10"), right? :)
Data tables that have been "prettified" in any way make me just plain grumpy, unless they are one-offs. Do the summarizing and beautifying on another sheet.
I prefer Cells(1, 1) then Range("A1"), but that is because I am usually working with variable row / column numbers 99% of the time, e.g. Cells(RowNum1, 1).
I really only use Range for where I know it is always going to be the same chunk every single time.
I'm with you on this one.
I want my raw data to be exactly that: raw. No number formatting, no nothing. Ok, I don't mind if there are a few unobtrusive cells with different colors to help me or someone else stumbling through the data identify specific cells or rows, but I -don't- want to have my numbers truncated, etc!
Give Microsoft 3 more versions and they will be the same. Why do you think we have a million rows, now? Too many people using Excel as a database and then complaining to MS that they do not have enough room for all their records.
Raw data isn't what Excel is about. Raw data belongs in a data store, maybe a database, maybe a text file, maybe another Excel workbook, but without a clear presentation of the data, it is impossible for data consumers to get the information they need.
Just remember that that 'capability' was nixed as of Excel 2007. So at Least MS got some things right with '07!
Don't pad values (especially, but not only, text) in cells with spaces.
I preach and preach and preach about this I know, but I have to keep insisting that no one should post examples of code calling Range without a worksheet qualifier. Assuming the Activesheet is dangerous. I hope you were just using shorthand.
It's not a problem if the code follows something like
Sheets("Whatever").Activate
then a reference to Sheets("Whatever").Range("A1") is actually slower than simply Range("A1")
Those billionths of a second add up, you know!Don't cram them into a single cell either.
As we all do, I suspect, I too work with variable row / column numbers 99% of the time.
I prefer to use Range("A1").Offset(RowNum1, ColNum1), which, to me is really the same thing as using Cells(x,y). It is just a different method, at least to me.
Seems to me it is 6 of one and half a dozen the other and again just a matter of preference and how you first learned and understood VBA. :)
I've refrained from adding as my list was far toooo long, and I thought most would be covered anyway. Here are a few I think haven't been suggested.
Copying worksheets with range names - thereby creating hidden links.
Using recorded code - UNMODIFIED. (macro1, macro2, macro ... to infinity).
Having oooodddles of workbooks with COPIES of same code.
Not using Add-Ins for your code.
And my fav - workbooks with strange properties set.As for the XL interface, I've seen sheets with data on alternate rows and columns.
And a 30,000 rows of data with several columns with only 3 entries each.
One worksheet had pink handwriting size 8 font on a red and yellow background with a 75% zoom - I got an instant headache!Lastly, 'please forgive me' - USING XL12.
Is there any general benefit in Activating a worksheet before using VBA on it?
I really thought there would be 10 replies as to the .Range("D10") comment.
So maybe this is an advanced "Do Not", but let me explain because it is a BIG DO NOT in any application wtih any shelf life.
Excel is wonderful at keeping track of references so that when you add rows and columns, it adjusts the formula references automtically. It even redefines the definition of the named ranges, including when you rename the sheet.
Do not be under the illusion that VBA code is as smart! If you give it wsInput2.range("D10") to get a value, It will get the value from cell "D10" even after you have inserted 3 rows at row 3 for neat page header.
The proper way is the give each critical cell location a named range such as "IN2_PartNum"
In the code you useDim rngPartNum as range
Set rngPartNum = Thisworkbook.Names("In2_PartNum").RefersToRange 'set the range variable to the named range.vPartNum = rngPartNum 'Assign the PartNum to a variant from the range.
This way, no matter where you move the cell with the named range "In2_PartNum", the VBA code will reference the correct cell.
I will use .Offset(i,j) only under conditions when I know the offset is good. In particular, I use a range Name "Tbl6_Header" for the labeled header block of a data table (either from an advanced filter or .CopyFromRecordset. The "Tbl6_Header" give me my anchor, .offset(1,0) gives me the first row of data in that record.
Back in the early '80s, I read a programming book recounting a wise programmer telling a junior, "Always use named variables to hold constant values. The only constants that should ever appear in your code are 0 and 1... and I'm not those two are ok, either."
Especially if the code in question is in a worksheet code module!!
I agree with critical cell ranges being named, but I'm moving more and more away from multiple cells in a range to using a single cell (named) as a datum point and using a combination of Offsets and Resizes, together with .currentregion to build the required objects before using them. (Which also negates having to mess around with screen updating).
That presumes you are doing something with an existing sheet though. It is clearly not always unreasonable to use a fixed address in your code. :).
Never use Sheets("Sheet1").Dosomething
Use Sheet1.DoSomethingUse the code name of the sheet rather than the name.
OK, since we are getting technical about safest way to refer to ranges. May I suggest never using workbook level names unless they are calculations or other use only pointers. And, prequalifying every named range with the sheet name as a variable to make sure it is at sheet level:
When initializing the range for the first time
sRefersTo = Thisworkbook.worksheets(Activesheet.Name).Range("A1")
Set N = wb.Names.Add _
(Name:="'" & Activesheet.Name & "'!IN2_PartNum", RefersTo:=sRefersTo)Later:
Set rngPartNum = Thisworkbook.Names(Name:= _
"'" & Activesheet.Name & "'!IN2_PartNum").RefersToRangeOtherwise woe betide the one who copies portions of the sheet (only) and pastes into a new sheet -- expecting the formulas referring to said ranges to be locally consistent.
Well, close (to err is humid)...
WRONG >> sRefersTo = Thisworkbook.worksheets(Activesheet.Name).Range("A1")
Anyway, just make sure sRefersTo is a valid address on the sheet.
If possible...
I hate the word never (I never use it myself) and I don't like using code name of worksheets and will never change my mind. All it took was doing a massive search and replace on similar (code)named sheets and affecting more than I bargained for, to teach me a lesson I have never forgotten. And it was so subtle I didn't know it happened until much later.
With Sheet1
Do Whatever
Loop
End WithSearch and Replace Sheet1 with Sheet2 and Heaven Help ya if you also have Sheet10 as a code name with some code in it:
With Sheet10 <<< bye bye Sheet10, hello Sheet20
Do Whatever else
Loop
End WithSearch and replace on a name inside a pair of double-quotes and you can't go wrong. Ah, the beauty of it
Oh, and since CodeName is read only, how are you going to handle deletion and reading of sheets on which operations are to be performed, if your VBA code refers to CodeName?
[I just know some expert is gonna set me straight here! I love it when I get to learn, even at the price of eating crow.]
Serves you right for using the default sheet names!
I tend to use the codename when possible, but assign it to a variable so you'd only need to alter it in one place if it alters. And I try to avoid using the default Sheet1, Sheet2 etc. ;).
You can change the name of the VBComponent programmatically.
Hey, that was an example only
Using Sheet1, Sheet2, etc may be problematic and the source of your frustrating experience. However, if you give your sheets meaningful codenames at the outset and then use those rather than the standard sheet names, your users can rename sheets, reposition them, etc and it will not affect your code. Also, by setting a local object in your code at the beginning of a procedure to the actual codenamed sheet then you can use that local variable in your with/end with constructs, limiting the problem to only where you set the local variable.
Sub mySub()
Dim myLocalSheet as Worksheet
Set myLocalSheet = myMeaningfullyCodenamedSheet
With myLocalSheet
... do stuff
End With
End SubThis trivial example may seem to have extra unnecessary overhead, but in more complex scenarios I have found it useful. Also, if a calling sub calls multiple subs that would use the same sheet, then pass it as an argument instead, SETting it only once in the calling procedure.
The codename CAN be changed at runtime... even though help says it cannot.
OK, I get it already, and have explained that I was using an example.
Obviously a very careful developer would probably not run into this situation. And yes, I do agree that using a worksheet's name instead of the code name leads to really big problems also, but I like having a reason to slap a user's wrist anyway ("I named the sheet that way for a reason!!!").
As for code name, nevertheless: Some day one might have a code name which *is* well-thought-out but happens to also be a substring of another well-thought-out codename ... Finding it using the Find dialog box is pain without the benefit of quotes; the longer name keeps coming up as well.
And find-and-replace, oh so dangerous.
I will not change my coding practice, I'm just a stubbin' mule :~!
Aaaah, great point. I forgot about that Caveat. In my case it wasn't a deal breaker, but it usually would be. I think that is one of the main reasons why I ended up using the sheetname instead in most instances. That and it is a fair bit of poking around to make it work. I also found it could make Excel unstable occasionally.
Don't put multiple disparate blocks of data into a single worksheet, rather place them in separate worksheets.
I don't disagree, but my users frequently want to have (limited) raw data sets available immediately with their charts, reports, etc. I don't have access to a better data store like Oracle, so I'm limited in my options.
Really? I thought Excel was an Access addin to crunch numbers and draw pretty charts...
You mean it's NOT?!
So give them a pivot linked to the raw data.
I've done that in many cases too, but sometimes the final presentation is in such a format that that's not the best option.
That's one I was going to pick up on too. We are forever preaching non selection wherever possible by setting objects to an object variable and manipulating the variable or using the With Sheets("Whatever") construct.
I asked someone this yesterday (offline) and he mentioned these two:
Freezepanes and setting CF formulas with relative references are the two I can think of offhand.
I seem to think you have to have a sheet Active in order to Paste to it (or maybe it was PasteSpecial.
Not really sure.
Yes, I was just using shorthand. I ALWAYS fully qualify a range even to the workbook level.
But, since I don't use Cells that much then I have to ask, wouldn't you still fall into the same pit if you don't fully qualify Cells and will Cells not also assume the activeworksheet if not qualified?
re:"Finding it using the Find dialog box is pain without the benefit of quotes;":
Doesn't 'Find whole word only' deal properly with this?
No, because the codename might be just a qualifier with a property next to it.
Again, FOR ILLUSTRATION ONLY - NO BARBS PLS!!!
With Sheet1
Blah Blah
End With
With Sheet10
Blah Blah
End With
Sheet1.Name = " Sample1"
Sheet10.Name = "Sample2"Now try to find all instances of Sheet1 without also finding Sheet10, using Whole Word Only.
<<<Wiping egg off face>>>
OK, I had just tested that before I posted it ... and I swear VBA did not find sheet1 in
Sheet1.Name = " Sample1"Now I tested it again and it did.
OK, Find Whole Word Only pauses for periods, I thought it would not - and tested it and it (seemingly) did not ... but has decided to, after all!
Use the Find/Replace of MZTools?
Hahahaha!! I have seen some pukey UI results when people have changed their colour index around, put in lots of candy colours to show stuff and then emailed the workbook to someone using the default (or different) colour grid. UGLY-A-RAMA.
Whilst on "Stupid things people do with Excel"...
I am always amazed by folks who have their font size set to 12 points (or bigger), but then put the zoom on 75% (or less) so they can see all the data on the screen better. They often also print stuff zoomed down (or fit to page) otherwise "it won't fit on the page"... I see this *all the time* at work.
Why don't they just don't make their default font (say) 9pt and leave the zoom at 100%? As an added bonus it will also fit on the darn page!! I mean, that is not rocket science folks.
The other one that made me laugh. Got a call from a woman wanting to know who to "insert a table into Excel". I told her the whole thing *is* a table to start with and what did she mean? She was adamant she need to insert a table of x row and y columns as she did in MS Word to make Excel work. I showed her how it worked but she seemed hopeless out of her depth - No hope for that one... Actually she quit not long after that episode.
I would say generally no, however there have been one or two things in Excel where I have need the sheet to be active and visible for the code to work. Can't recall for the life of me exactly what they were, but I know that occasionally that has been the only way to get the code to work.
As a rule though, Activating and selecting is completely redundant and (IMHO) slows things down a lot - especially if screenupdating is not turned off.
Agreed 99% of the time using sheet code names absolutely is the way to go. There have been some rare instances where using the sheet name has been a better approach. besides if you really need the sheet name you can call it back anyway from the sheetcodename.
Dim strShtName as string
strShtName = Sheet1.Name
Worksheets(strShtName).DoSomethingOf course you wouldn't really use it that way, but you get the idea.
Cheeky!... Generally this (codically removing and inserting sheets) is one of the issues where using the sheet name can work better (or at least easier), however, I have used code from Chip Pearson to remove an existing worksheet and replace it with a new worksheet and then programically reset the sheetcodename of the inserted sheet to be what I need it to be. :) Can be done but it is a bit more stuffing around. See: http://www.cpearson.com/excel/vbe.aspx if you are curious.
> I have used code from Chip Pearson to remove an existing
> worksheet and replace it with a new worksheet and then
> programically reset the sheetcodename of the inserted
> sheet to be what I need it to be. :) Can be done but it
> is a bit more stuffing around.
> See: http://www.cpearson.com/excel/vbe.aspx if you are
> curious.
Boilerplate warning: Doesn't work if the VB project is protected (as it should be).I have always had trouble using the sheets code name when I am doing things between 2 workbooks like:
ThisWorkbook.Bk3Sheet1.Range("D6").Copy
Workbooks("Book2").Bk2sheet1.Range("D6").PasteSpecial Paste:=xlPasteValues
I am sure there is a logical explanation, but I don't know it.You would need to set a reference to the other workbook's project before you could use codenames for its sheets. (at least, that's the only way I know of)
Are Bk2sheet1 and Bk3Sheet1 worksheet objects? IF so, why are you using the workbook qualifier as well?
Sounds like referring to codename'd sheets in two different workbooks will be a problem, generally speaking, if they have the same codename?
And quite frankly, even if they have different codenames, without the reference of which someone speaks, to the projects in question ... using the codename of an object outside of the active project would fail?
Maybe this is one-half of someone's 1 percent leftover in favor of the Name property?
I would put using the "sheet code name" in the "Not to use" category.
Anytime you code on the bare metal you are asking for trouble.
Abstract your conceptsDim wsBk3Sheet1 as Worksheet
Dim wbBook3 as workbookSet wbBook3 = Workbooks("Book3.xlsm")
Set wsBk3Sheet1 = wbBook3.Worksheets("Sheet1") 'or = wbBook3.Sheet1 if you want to commit the sin only oncenow wsBk3Sheet1 is a valid reference to a specific sheet (assuming the above executes without error)
wsBk3Sheet1.DoSomething 'worksNow abstract further: why use Excel's name for the sheet. Use your own so you know what you (and others) are talking about.
Dim wsOilGasYield as worksheet
Set wsOilGasYield = wbBook3.Sheet1 'or as I prefer = wbBook3.Worksheets("OilGasYield")At this point, I want it issue another NOT to Use point.
>> DO NOT FORGET to use Option Explicit at the top of each module. <<
Declare your variables. It might be a pain, but it beats searching for misspellings deep in your code.
Finally, let Intellisense work for you.
Type in "wsO" then Ctrl-Space and you get the list of all variables begining with wsO.
Select wsOilGasYield .
Type a "." and now you have the list of all Worksheet Properties and methods.
If you don't get the list, you know you have a bug.If you use "Workbooks("Book2").Bk2sheet1. "
Intellisense cannot assist.>>
Agreed 99% of the time using sheet code names absolutely is the way to go. There have been some rare instances where using the sheet name has been a better approach. besides if you really need the sheet name you can call it back anyway from the sheetcodename.Dim strShtName as string
strShtName = Sheet1.Name
Worksheets(strShtName).DoSomethingOf course you wouldn't really use it that way, but you get the idea.
"Set wsOilGasYield = wbBook3.Sheet1".
Yes... This is exactly the technique I usually take when dealing with Xlsheets in the VBE. As for why I use prefer sheetcodename. Simply the code will not fail if the user changes the sheet name. Use SET as per above is the optimal way IMHO and recommended.
The issue with using "wbBook3.Worksheets("OilGasYield")" rather than "Set wsOilGasYield = wbBook3.Sheet1" is this code will fail if a user changes the sheet name - and that happens a lot in my experience.
-
Never Call another sub without using Call.
Call SubName is better than SubName. -
Good point, I get around that with defining the range first, then the sheet.
Set rngGasYield = wbBook3.names("GasYield").refersToRange
Set wsOilGasYield = rngGasYield.Parent(I just didn't add the extra step in the previous post)
However, I now see the benefit in your method. I'm not sure I'd adopt it, but it has advantages. It beats a For Each ws1 in Worksheets() looking for something key on a renamed sheet.
-
> Never Call another sub without using Call.
> Call SubName is better than SubName.
>
Why on earth? -
Because Mr Phelps said so....
I've always thought, getting everything right with XL was a mission imposible.
-
I agree with you on why you prefer sheet code names (I prefer too for that same reason), but I am still unable to use sheet code names when I want to do something between 2 workbooks i.e. Copy Test1 (below) does not work, but Test2 does
Stephen I believe you said Test1 would work (even if you wouldn't use it), but I cannot get it to work
Sub Test1()
Dim Sht1 As Worksheet, Sht2 As Worksheet
Set Sht1 = Workbooks("Book2").Sheet1
Set Sht2 = Workbooks("Book3").Sheet1
Sht1.[D6].Copy
Sht2.[D6].PasteSpecial Paste:=xlPasteValues
End Sub
Sub Test2()
Dim Sht1 As Worksheet, Sht2 As Worksheet
Set Sht1 = Workbooks("Book2").Sheets("Sheet1")
Set Sht2 = Workbooks("Book3").Sheets("Sheet1")
Sht1.[D6].Copy
Sht2.[D6].PasteSpecial Paste:=xlPasteValues
End Sub -
>> I believe you said Test1 would work (even if you wouldn't use it), but I cannot get it to work.
Sub Test1() Dim Sht1 As Worksheet, Sht2 As Worksheet
Set Sht1 = Workbooks("Book2").Sheet1
Set Sht2 = Workbooks("Book3").Sheet1
Sht1.[D6].Copy
Sht2.[D6].PasteSpecial Paste:=xlPasteValues
End Sub
<<Don't look at me. I was taking him at his word. Using XL2007 SP2, I cannot get it to work either.
With Set wb2 = Workbooks("Book2.xlsm")
In the Watch Window for
wb2.Sheet1 I get "Object doesn't support this property or method"
wb2.Sheets(1) resolves as a worksheet and
wb2.Sheets(1).codename = "Sheet1"wb2.Sheets("Sheet1") works fine, of course.
strTargetSheet = "Sheet1"
wb2.Sheets(strTargetSheet) resolves to "Sheet1", and I prefer this symmetry.
should I delete Sheet1 then
wb2.sheets(2).codename = "Sheet3"Personally, for me wbX.Sheet11 syntax is a Do NOT Use.
Two more issues to bring up:
I have learned that .Copy; .PasteSpecial xlValues is unnecessary.
I believe that your
Sht1.[D6].Copy
Sht2.[D6].PasteSpecial Paste:=xlPasteValuescould be rewritten as
sht2.[D6] = Sht1[D6].valueswhich has the following advantages:
1. Only 1 line of code without breaking a sweat
2. It doesn't use the Clipboard which can be extreamly useful in multitasking
3. it is far faster
4. PasteSpecial requires the target sheet to be active, this method doesn't
5. It works on arrays, too. sht2.[D6:E10] = Sht1[D7:E11].valuesIt's been a long time since I've used wsX.[cellrange] syntax. I have to put that in the Not to Use category for me:
1. It is putting specific cell references in the code,
I'm a stickler on using range names to protect myself from later added rows and columns.2. You cannot use the syntax for strings carrying range names or addresses
str2 = "D6"
ws2.[str2] doesn't refer to column 4 row 6,
it refers to column 13382, row 2... ws2.Range("STR2") 'welcome to XL07!3. That [address] syntax is commonly used in Access for FieldNames, though I prefer using the syntax: rsX!FieldName or rsX.Fields("FieldName") I want to keep that mental barrier.
This has been a good topic. I've learned a few things and have reexamined others.
-
Try the below
Sub CallTest()
Call Test(Sheet1, Sheet1.Range("A1"))
'Try without the Call statement
End SubSub Test(wSht As Worksheet, Rng As Range)
MsgBox wSht.Name MsgBox Rng.Value
End Sub -
Yes I copy using range = range.value also. I was just using copy as an example of simple code that didn't work for using sheet code names between workbooks.
-
BksSheet1 and Bk3Sheet1 are the sheet code names of 2 different workbooks.
Thanks -
Sorry, I should have answered the rest of your question as well. I use the workbook qualifier in case the sheet code names in 2 different books are the same. It doesn't really matter because neither work.
-
Correction (left out a "." )
sht2.[D6] = Sht1[D6].values
should be
Sht2.[D6] = Sht1.[D6].Values
but regardless of syntax
rngB = rngA.Values 'will work to "paste values" -
I'm an idiot. Excuse my error.
rngB = rngA.Value 'Not ValueS Although it works for arrays of thousands of values. -
Not allowed to call yourself an idiot. Violates Listserv rules!
-
> Try the below
>
>
> Sub CallTest()
> Call Test(Sheet1, Sheet1.Range("A1"))
> 'Try without the Call statement
> End Sub
>
> Sub Test(wSht As Worksheet, Rng As Range)
> MsgBox wSht.Name MsgBox Rng.Value
> End Sub
>
Sub NoNeedForCallTest()
Test Sheet1, Sheet1.Range("A1")
End Sub
Sub Test(wSht As Worksheet, Rng As Range)
MsgBox wSht.Name & " " & Rng.Value
End Sub
HTH Well first of all this line won't work anyway:
MsgBox wSht.Name MsgBox Rng.Value
And next, telling someone that there is one particular value somewhere on a
sheet is not very good information, so let's add the address,
MsgBox "sheet: " & wSht.Name & _
" Value of Cell: " & rng.address & " = " & Rng.Value
And next, better test that Rng is only one cell, or take just its first cell
MsgBox "sheet: " & wSht.Name & _
" Value of Cell: " & rng.Cells(1,1).address & _
" = " & Rng.Cells(1,1).Value
And last: well, now we come full circle back to the thread which says you
never need to use SUB in the first place:
Function CallTest()
MsgBox Test(Sheet1, Sheet1.Range("A1"))
'Try without the Call statement? Just did!
End Function
Function Test(wSht As Worksheet, Rng As Range) as string
Test = "sheet: " & wSht.Name & _
" Value of Cell: " & rng.Cells(1,1).address & _
" = " & Rng.Cells(1,1).Value
End Function
This works as well
Sub CopyBtwBooks()
Dim SorSht As Worksheet, DestSht As Worksheet Dim SorWb As Workbook, DestWb
As Workbook
Set SorWb = ThisWorkbook
Set SorSht = Sheet1
Workbooks.Add
Set DestWb = ActiveWorkbook
Set DestSht = DestWb.Sheets("Sheet1")
SorSht.Range("A1:C10").Copy DestSht.Range("A1")
End Sub
No comments:
Post a Comment