Posts by colin_l

    Re: Static Date & Time Corresponding To Cell Change

    I understand where you are coming from and I'm all for keeping things simple, but introducing the notion that it is effectively the same as the offset property only confuses the situation. People who are learning from this thread would be learning incorrectly and will run into problems later on. These two properties are different to each other, just as they are different to the cells property, and each should be treated separately.

    Taking a simple example:

    Private Sub Worksheet_Change(ByVal Target As Range)
        MsgBox "item " & Target(1).Address
        MsgBox "offset " & Target.Offset(0).Address
    End Sub

    If one pastes this into a sheet class module and then selects a multicell range, say A1:A10, types in the formula =1 and completes the entry with CTRL+SHIFT+ENTER, the two properties yield different results. They are not effectively the same.


    Re: Static Date & Time Corresponding To Cell Change

    Just to be clear on this, it's not an abbreviated form of the offset property. It's an abbreviated form of the item property. The reason this syntax is permissable is that the default property of the range object is the item property....

    That might sound pedantic, but there are clear differences.

    As you mentioned, the item property is 1-based.

    Also, the item property will only ever return a single cell range, whereas the offset property may return a multi cell range. For example, compare these two:

    Sub foo()
        MsgBox Range("a1:c1")(2).Address
        MsgBox Range("a1:c1").Offset(1).Address
    End Sub

    Also, note that these two return different results:

    MsgBox Range("a1:d1")(2).Address
        MsgBox Range("a1:a4")(2).Address

    The differences are less apparent when working with a single-cell parent range, but I think that precise understanding is required to avoid confusion.

    MsgBox Target(1, 1).Address

    Is the same as/shorthand for:

    MsgBox Target.Item(1, 1).Address


    Re: Add More Columns?

    No, that is the maximum number of columns in all versions of Excel prior to 2007.

    You do have up to 65536 rows you can use though.


    Re: Minverse Num! Error

    Hi Mark,

    I think the clue to this is in the Excel helpfiles for the MINVERSE worksheet function:

    Quote from Helpfile

    Some square matrices cannot be inverted and will return the #NUM! error value with MINVERSE. The determinant for a noninvertable matrix is 0.

    Do you get the same error if you directly use the MINVERSE worksheet function within a formula within the worksheet?


    Re: Professional Development: The Definitive Guide ...

    If you want an Excel VBA reference book then (IMHO) the best one around is Excel 2007 VBA Programmer's Reference by Green, Bullen, Bovey and Alexander. It's the complete package and is much more comprehensive than PED as a reference book.
    Warning: The 2003 version of this book is awful.

    Professional Excel Development (PED) focuses on many advanced areas - as it says on the synopsis, it goes beyond other books. Unless your Excel/Excel VBA is already very competant then you will not be able to follow most of the material since it is written with the assumption that you do.

    So should you buy PED? That totally depends on what you're after....

    Just my 2c.... [I own both these books]

    Re: Pull Each Each Digit From Number Into Single Cells

    Here's another alternative.

    Set up an named array constant called Arr which is defined as:

    Then select cells C3:K3 and complete this array range formula with CTRL+SHIFT+ENTER:

    Then fill the formula down the table.

    Example attached.


    Re: Create List Depending On Lookup Value

    Why can't you use a pivot table to create the list?

    To correct your formula:
    =IF(ISERROR(INDEX('NEW stores'!$A$2:$E$436,SMALL(IF('NEW stores'!$A$2:$A$436=Report!$R$4,ROW('NEW stores'!$A$2:$A$436)-1),ROW(1:1)),4)),"",(INDEX('NEW stores'!$A$2:$E$436,SMALL(IF('NEW stores'!$A$2:$A$436=Report!$R$4,ROW('NEW stores'!$A$2:$A$436)-1),ROW(1:1)),4)))


    Re: Numeric Validation Of User Form Textbox


    You'll need an identifier so that you can exclude them. For example, you could use the textbox's tag property and extend the If condition in the loop, or you could group the numeric/string textboxes in different frames and only loop through the textboxes in the relevant frame.


    Re: Find A Few Values At Once Time


    This should give you the general idea:


    Re: Now() Updating Without Closing File

    The value will be updated each time the worksheet is recalculated. (Press F9 to force this manually).

    Change the format of the cell to seconds precision if you want to physically see the value change more frequently than each minute.

    Re: Numeric Validation Of User Form Textbox


    If you have a lot of textboxes then I'd do it like this:

    Code in a Class Module called cTextboxes:

    Code in your Userform Class Module:

    This code was adapted from here:

    Also, as it stands I have used the communal 'data validation' procedure you provided in your post. Personally I don't think it's overly user-friendly because if a user types in a 6 digit number and then accidentally catches a letter, your procedure wipes the entire entry and the user has to start over again. Instead, you might want to consider the approach demonstrated by Chip Pearson here which traps the textbox's KeyPress event: