Posts by lecxe

    Re: Placing Vba Objects In Worksheet

    Hi googlebot

    Bob is telling you how to insert an ActiveX Textbox.

    From what I understood you want to insert the Textbox that you used to access in the Design Toolbar in previous versions. That one you can access in xl2007 in the Insert tab, in the Text group.


    Re: Save Diagram As Gif Image

    Hi maxald
    Welcome to the board

    Check the help on the method Export of the Chart object.

    This is the example in the help:

    Worksheets("Sheet1").ChartObjects(1) _
            .Chart.Export _
            FileName:="current_sales.gif", FilterName:="GIF"

    Re: Maco Functions For Parsing Sub-Strings

    Hi pike

    Replace the pattern in Jindon's code by:

    .Pattern = "([A-Z]+)\D*(\d{1,2}:\d{1,2}:\d{1,2}).*"

    And then use Jindon's instructions:
    - Select B1:C1
    - Enter =rapha(A1) in the formula bar
    - Then confirm with Ctrl + Shift + Enter (array formula entry)


    Re: Extract Specific Numbers From Text Strings

    Another one, equivalent to Jindon's:

    Function GetNumbers(s As String, i As Integer) as String
        With CreateObject("vbscript.regexp")
            .Pattern = "\D+|(" & i & "\d*)?\d*"
            .Global = True
            GetNumbers = Replace(Application.Trim(.Replace(s, "$1 ")), " ", ",")
        End With
    End Function

    Use it like this:


    Re: Retrieve Chart Field Values

    1 - You cannot write the series formula directly in the cell as a formula because excel tries to interpret it as a worksheet formula, and there's no worksheet formula Series().

    You have to write the formula as text. You either format the cell as text before writing the series formula, or you use the text prefix "'".


    Analysis Charts List.xls").Worksheets("Range by Chart").Range("write_range") = "'" &  .SeriesCollection(1).Formula

    2 - to write the appropriate number of series formulas, use the SeriesCollection .Count property, like

    Dim lSeries as Long
    For lSeries=1 to .SeriesCollection.Count
        ... = "'" & .SeriesCollection(lSeries).Formula
    Next l

    Remark: There's one thing I don't understand in your code, why do you write 2 times in the same cell, Range("write_range") ??? The second time you overwrite the existing data, no? Please check it.


    Edit: in the first code box I noticed that the browser added a space before the single quote. I'ts "'" (double quote, single quote, double quote), no space.

    Re: Retrieve Chart Field Values

    Hi Tai

    The Values and XValues properties only give you the actual values and not their source. You'll be better off if you print the series formulas.

    In case you're not familiar with it, a series formula has usually this syntax


    and the parameters are: Name, Category (X axis), Values (Y values), (series) Order.

    I believe you have all the information you need in the series formula.

    In your case you'd write .SeriesCollection(1).Formula in your code.

    If you think this solves your problem and have problems with the implementation post back with details.

    Kind regards

    Re: Function To Write An Array

    I agree with you, in this case it makes more sense to use a Sub. I was merely pointing out the fact that vba functions running in vba context can alter the environment. Like you said, however, in this case he's not returning any value and so a Sub makes more sense.

    kind regards

    Re: Function To Write An Array

    Hi Mike


    Functions return values, they don't write to cells.

    I think you are talking about UDFs (context worksheet) and not vba functions (context vba). If you call a UDF from a formula in a worksheet you have restrictions that prevent you from changing the environment, like writing to cells, hiding columns, etc. Here, however, that's not the case. The function is called from a Sub, we are in vba context, and so the vba function has no restrictions, it's just vba code.

    This doesn't mean that I don't agree that it makes more sense to use a Sub in this case, since you are not returning a result.

    Kind regards

    Re: Text- To Rows ( In 1 Column)

    Hi wollyka

    here's another option:

    Re: Validate X Only Has All Text Or All Numbers, Not Both

    Similar UDF's but using the Like operator, instead of RegEx's:

    Re: Minimum/Maximum Associated Within Rate Range

    Hi nbostic

    All the questions seem to be variations on the same theme, so this is the solution to the first one and you can adapt it to the other questions.


    - Date in column A
    - Price in column B
    - You want to know the smallest non-zero price for the year 2007


    confirmed with CTRL-SHIFT-ENTER

    Adjust the ranges.

    Hope this helps

    Re: Reduce/Refine Row Number String Addresses

    Mike: I tried it but the string didn't change

    shg: Great! I didn't know that intersect would combine contiguous ranges.
    I also tried it with .Union with success.

    A new one I learnt today. Thanks.

    Re: Filter By Multiple Conditions/Criteria

    Hi jonny

    3 remarks

    - a regular expression is a big gun for a simple pattern comparison like this

    - I think you forgot something before the "7" in you example. Didn't you mean "ABC1234XYZ7"? Else, the pattern has one "\D" too much.

    - Looking at your example the pattern you chose doesn't seem adequate. It accepts "AB,1234!%Z7". Is this really what you want? Don't you mean just digits and letters?

    Solution 1

    This is an example of a function that tests if a string matches the pattern.

    I assumed this pattern:

    3 letters then 4 digits then 3 letters then 1 digit then, optionally, some more digits

    Function TestS(s As String) As Boolean
    If Len(s) > 10 Then _
        TestS = s Like "[A-Z][A-Z][A-Z]####[A-Z][A-Z][A-Z]" & String(Len(s) - 10, "#")
    End Function

    If you want to have a case insensitive comparison include as the first line in the module:
    Option Compare Text

    Solution 2

    If this was just an example and you do need regular expressions, for more complex patterns, this is an example with the same assumptions for the pattern:

    Function TestS(s As String) As Boolean
    With CreateObject("vbscript.regexp")
        .Pattern = "^[A-Z]{3}\d{4}[A-Z]{3}\d+$"
        .IgnoreCase = True ' or don't include this statement
        TestS = .Test(s)
    End With
    End Function


    Re: Reduce/Refine Row Number String Addresses

    Hi Eric


    Function CombineRange(s As String) As String
    Dim vS As Variant, l As Long
    vS = Split(s, ":")
    For l = 0 To UBound(vS) - 1
        If Evaluate(Replace(vS(l), ",", "-")) = -1 Then vS(l) = ""
    Next l
    CombineRange = Replace(Application.Trim(Join(vS)), " ", ":")
    End Function


    Re: Multiply & Add Cells In Range With Set Cells

    Hi Eddiie

    You can for ex. use the worksheet function Sumproduct().

    Adapt this example:

    Re: Subtracting Given Values From Number

    Hi Risto85

    Assuming a layout similar to your example with:

    - Headers in Row 1 starting in column A
    - Stock in column B
    - Forecast for April-December in columns C:K


    Number of months covered in N2:


    Out of stock month in M2:


    "OK" means you have enough stock for the all months forecasted.

    lecxe[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi again



    Thanks for your quick relpy. I seem to have some sort of problem getting this formula to work, do you have idea what could be wrong?

    I've just tried the solution on a newly created workbook and it worked.

    I'm sorry but I don't see what can be the problem.
    In some contries the parameters of a function are separed by a semicolon ";" instead of by a comma as in the English version.

    Using the formula in N2 I get the value 2 which seems correct. The total of the first 2 months is 2100, still covered by the stock. The total of the first 3 months is 6100, no longer covered by the stock.

    Maybe someone else can try it and give feedback?