Posts by RichardSchollar

    Re: "excel 4.0 Macro" Warning


    There are a number of other XLM4 functions that can be used in defined names like EVALUATE and LINKS (in fact there are an awful lot of them). Are you also searching in hidden workbook names? If not, then checking any such names may be worthwhile too...

    Re: "excel 4.0 Macro" Warning


    Do you have any defined names using XLM4 functions? Such names could use XLM4 like GET.CELL and GET.DOCUMENT. If you want to search in code, then you just need to search for the text "ExecuteExcel4".


    Richard

    Re: Professional Development: The Definitive Guide ...


    Now I feel a little differently on Excel 2007 Programmer's Ref: it's a good book, but I would rather have PED. Maybe this is because I also have 2002 Programmer's Ref and don't use Excel 2007 a great deal? I currently don't view the 2007 version of Programmer's Ref as being essential...

    Re: Verify Whether Cell Address Is Valid


    Hi Raja


    Here's an example function you could use:


    Code
    1. Function ValidAddress(strAddress As String) As Boolean
    2. Dim r As Range
    3. On Error Resume Next
    4. Set r = Worksheets(1).Range(strAddress)
    5. If Not r Is Nothing Then ValidAddress = True
    6. End Function


    Use in a sub like:


    Code
    1. UserAdd = Inputbox("Enter your address")
    2. 'check if valid:
    3. If ValidAddress(UserAdd) Then
    4. 'it's valid!
    5. Else
    6. 'it ain't valid!
    7. End if


    Richard[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Of course you could just get the address via:


    Code
    1. s = Application.InputBox("Enter address", , , , , , , 8)


    which will do the validation for you.


    Richard

    Re: VBE Color Pallet


    Are userform colors (once suitably modified in the Properties window in the VBE) also restricted to the 56 color pallet of the workbook in which they reside? Anyone know?

    Re: Value Error When Linking A Countif Function


    Yep, that's a limitation with both Sumif/Countif - the source workbook needs to be open. You can get around this by using Sumproduct instead - Chris in your case this would be:


    =SUMPRODUCT(('C:\Users\Chris Edwards\Documents\Work\New Stuff\July\Team 1\[Anna.xls]Input Sheet'!AP$22:AP$671=B6)+0)


    Now, I see from your profile you are using xl97. I can't guarantee that this will definitely work in 97 (I'm pretty sure it will) - you will just have to give it a try :)


    Richard

    Re: Set Named Range As Constant


    Hi Joe


    I'm afraid Constants can only hold simple data types eg like a string or a numeric value. Perhaps you could just use a Public variable instead eg declare:


    Code
    1. 'declarations section of standard module:
    2. Public tpnb_range As Range
    3. 'then in ThisWorkbook module:
    4. Private Sub Workbook_Open()
    5. Set tpnb_range = Worksheets(" SQL").Range("tpnbs")
    6. End Sub


    You can then refer to Worksheets(" SQL").Range("tpnbs") by using tpnb_range.


    Richard

    Re: Extract Only First Number In String


    Hi


    Given those values in a cell here is a potential native formula solution:


    =LOOKUP(9.999E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0,"-"},A1&"1234567890-")),ROW(INDIRECT("1:"&LEN(A1)))))


    Adjust cell reference to suit.


    Richard

    Re: Sum Value Part Of Cell Only


    Hello Mike


    Your easiest route would be to use a helper column with a formula like:


    =MID(A1,2,255)+0


    copied down the rows to extract the numeric portion out of the cell and then sum the results of this.


    An alternative would be to use a single formula like:


    =SUMPRODUCT(MID(A1:A1000,2,255)+0)


    which would avoid the use of a helper column.


    Richard

    Re: Error 1004 Porting Code From One Computer To Another


    Hi


    When at home with this file you go (within the VBE) Tools>References do you spot any references labelled MISSING? Something like this has happened to me before moving from one version of Excel to another (because the type libraries are different). However, you are staying within the same version onf Excel/Office, so this may be of no help.


    Richard

    Re: Function To Write An Array


    Hi


    This Board (as well as many others) generally don't like to hand hold when what is being asked looks like homework. With this in mind, I won't do the work for you. But I can give pointers.


    What is the source of the 2D array of doubles? Is it an existing excel range or is it coming from somewhere else? From my understanding, you just need to take the array and iterate thru it comparing the values at each step and replacing the value with the cap or the floor if it is outside of the limits. Is this correct?


    Richard