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".


    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:

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

    Use in a sub like:

    UserAdd = Inputbox("Enter your address")
    'check if valid:
    If ValidAddress(UserAdd) Then
      'it's valid!
      'it ain't valid!
    End if

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

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

    which will do the validation for you.


    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 :)


    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:

    'declarations section of standard module:
    Public tpnb_range As Range
    'then in ThisWorkbook module:
    Private Sub Workbook_Open()
    Set tpnb_range =  Worksheets(" SQL").Range("tpnbs") 
    End Sub

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


    Re: Extract Only First Number In String


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


    Adjust cell reference to suit.


    Re: Sum Value Part Of Cell Only

    Hello Mike

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


    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:


    which would avoid the use of a helper column.


    Re: Error 1004 Porting Code From One Computer To Another


    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.


    Re: Function To Write An Array


    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?