+ Reply to Thread
Results 1 to 17 of 17

String and Value workbook reference

Hybrid View

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    Irving, Texas
    MS-Off Ver
    Office 2003-07-10-13
    Posts
    34

    Re: String and Value workbook reference

    ok, I've had some time to re-examine your posts and code and have a few first comments and questions.

    first of all, in your code posted today, there is a line:
    Cells.Select
    This code selects all cells on a worksheet. ALL CELLS
    For the XLS format, this means 16,777,216 cells
    For the XLSX, XLSB, and XLSM format, this means 17,179,869,184 cells
    Some quick math will tell you that the newer format supports 1,024 times MORE CELLS.

    Others may disagree, but I can share with you first hand, this can be VERY DANGEROUS to successful completion of your code.
    Naturally, this does depend on how much actual content lives in the sheet, it's format(s), its functions and calculation state and many other factors.
    I can personally attest that I have seen hundreds of developers use this step in XLS format and then attempt to run the code in the newer applications ans watch Excel crash at worst, or error out at best.

    If you, using this small example as an example, get the mistaken impression you can use it in any scenario, you may find out the hard way you can't.
    Please don't misunderstand, I fully expect, respect, and accept, you're using it because it may well be that is what someone showed you, or it was contained in some other code you copied.
    None the less, in a future post, I'll provide an example of how you can gather the necessary information to avoid using it.

    Moving on:
    The For/Next Loop is ok, but it does nothing to delete any unnecessary columns that may have the misfortune to be placed to the right of the last listed column in the For/Next Loop.
    Here too, I'll work into an example workbook the ability to correct this situation.

    The code seems to make the assumption that the far left column will always receive the "Cut Off" heading (hard coded) but the code also seems to imply the data pasted from LAS Load may actually have a different heading.
    Are these correct assumptions?

    Repetitive Programmatic Copy/Pasting can cause problems with the Office Clipboard.
    There is no way to programmatically empty the Office Clipboard, and it only empties itself when the last current instance of an Office application is closed.
    While I certainly understand the use of copy/paste when performing steps manually, things can get carried away when code is used to do the same thing.
    Frequently, I've seen people record a macro to make repetitive tasks easier, but when that repetitive task is copy/paste, I've also seen Excel crash and even adversely impact Windows and the kernel.
    Granted, that can be an extreme result, but it does happen.
    As an alternative, one can use some database functionality to create a "connection" to data and then append the data to another location in the workbook without ever using copy/paste.
    As extra benefits:
    You can have the option to select only those columns you really want from the original records,
    Rename any of the columns as you specify
    Avoid additional steps to delete unneeded columns
    Can re-specify new or different columns on subsequent runs of data.
    If desired, you can also filter data based on criteria you can specify.
    All of these parameters can be externally set by you very simply by placing values in cells in a parameters sheet.
    while this may seem like fairly advanced programming, it really is not that difficult.
    If that opportunity seems appealing to you, I could also post a sample of that.

  2. #2
    Registered User
    Join Date
    10-24-2011
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: String and Value workbook reference

    I appreciate your comments, and I am very aware of how inefficient and how sensitive my code is. In addition to hard coded names and values that often need to be changed, by recording macros the code often points to specific column or cell locations rather than to a name or type of data. If anything is in the wrong place failure is certain.

    Your assumptions are correct, the Cutoff channel is the result of an IF statement, operating on other columns, that populates the column with 0/1 values. Then another macro deletes all rows with a 1. Like I said, if the cuttof channel is not in col 1, the Cutoof macro won't find it.

    Selecting the columns is a simple matter and a very small part of the operation. I'm sure you'd have a good laugh at how ugly the total code is, but it works. I can use it because I built it and know what breaks it, after I complete the functionality I need to make it useable for others.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 06-01-2013, 05:04 AM
  2. [SOLVED] copy active sheet, create new workbook, name new workbook based on cell reference
    By jm0392 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2012, 07:12 PM
  3. Replies: 2
    Last Post: 05-01-2009, 07:06 PM
  4. use a string to reference a sheet
    By AustinBrister in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2008, 02:00 PM
  5. Replies: 1
    Last Post: 02-03-2006, 12:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1