Posts by PhasmaNoctu

    Re: Regular Expressions in VBA

    Quote from cramirez06;187758

    Thanks Richie, that site really helped. I have another question though: I have only just started learning regex so it might be a stupid one, but... Do you know how I could turn a string into a regular expression pattern? More exactly, if I have a string
    "6-methyl-5-hepten-2-one", how can I turn it into a regex withing VBA code to search just for "5-hepten-2-one" or something similar? Because I will have to do so for many such names.

    You're probably looking for something like:

    "^[0-9]-methyl-[0-9]-hepten-[0-9]-one$" to match your string but with any single number instead of 6, 5, and 2 specifically like 8-methyl-2-hepten-1-one. You can also use ^ and $ to match the beginning and end of the line. The "-" has to be included between each element.

    "[0-9]+-methyl-[0-9]+-hepten-[0-9]+-one" is the same as above but with one or more numbers instead of one like 889-methyl-4-hepten-1235347686572465235758573467-one

    "[0-9]+-[A-Za-z]+-[0-9]+-[A-Za-z]+-[0-9]+-[a-z]+" is the same as above but the words can be any combination of upper and lower case letters, except the last is only lower case like 2235-WhatEVEr-98-mOrEWORdswithOUTSPaces-098-onlylowercasehere

    "[0-9]*[02468]-[A-Za-z]+-[0-9]{3,5}-[A-Za-z]+-[0-9]{3,}-(one|two|five|nine)" matches even numbers ([0-9]* matches 0 or more but must end with even number), a word, 3-5 numbers, another word, at least 3 numbers, and then one of the words one, two, five, or nine. () groups things together and | means 'or'.

    "([0-9]+-[A-Za-z]{3,10}-?)+" matches any number, then any word 3-10 characters long, but then repeats that as many times as it can. It matches 8-hepten and even 945-hepten-9867-oxy-8976-one-987-eight-987-morewords-9842-methyl... The "?" matches 0 or 1, so the "-" won't be at the end of the last matched word. If you know it will always be a group of 6 (3 number-word combos), then you can replace the + with {3}.

    These might not be exact, but they should give an idea of how specific or general they can define your strings.

    Regular expressions make matches more precise and much more efficient to program than parsing text and using a couple wildcards. I didn't learn much about them until I did analysis outside of Excel, but I wish I had learned them earlier. This is a small contribution to introduce it to others that wouldn't come across it otherwise because it should be a more prominent tool in VBA. I start with a few strings I want to match, and some I don't want to match, and replace each part with a more general match, similar to shown above. Test it and make sure it works like expected.

    Re: Shortening the NPV formula

    Thanks Wigi! That works for that problem. It fits perfectly in my worksheet.

    I was wondering if there is something more general if there wasn't a similar function for that? An example (unless it has a similar function too) would be =average(A1,A2,A3x20times) that could be used to see the effect of additional iterations of one input on the result. In this case you could just =sum(A1,A2,A3*20)/22 and make the 20 and 22 based on a variable to get it to always work, but I'm looking for a new way to repeat variables in a formula if possible (or useful). I'm hoping I can learn a new technique and not just a solution to an immediate problem.

    I'll have to check more of the financial formulas though, I haven't had a good look at all of them. And maybe I'll have to start a new thread since Wigi answered the title...

    It would be awesome if I knew how to put multiple instances of a cell into a formula so that it would read =NPV(C6,formula(Cell,NTimes)) or something like that. In this case, I copy the formula and need one less "F7" each time.


    I'm not super familiar with array formulas, and I wonder if rept() could be used somehow.

    How would you write this formula?

    Thanks for your help!