0

I'm creating a large amount of Excel test files with different values in many columns.

(Windows 7 - 64 bits system (if relevant)).

These files are going to be imported into a database later using other unknown tool, so I just have to prepare only these files filled with valid data.

Each file has different columns, so different ranges of valid characters are necessary per column.

What would I want to do?

Use a Formula witch receive a regex and generate/and fill the cell with a random string based on that regex and if possible specify how many characters the string should be.

Something like this site does: https://www.browserling.com/tools/text-from-regex

For example: For the next columns, I would like to populate cells with random text as follows:

> 
> -------------------------------------------------------------------
> | Name      | Email          | Date  | URL               | Price  |
> -------------------------------------------------------------------
> | AHygsyub  | xyz@uygsh.hyu  | 1956  | http://iadif.jyf  | 245.75 |
> -------------------------------------------------------------------

Using something like this in the Formula bar:

> =fillCellWith('([a-z]{3,10} ){1,10}')              //For Name column
> =fillCellWith('\d{4}')                             //For Date column
> =fillCellWith(RegexPattern)                        //Etc etc

Can someone guide me in the creation of a VBA Function to accomplish this purpose?

I don't have any experience in VB programming, but I can imagine something like:

Public Function fillCellWith(MyRegex As String) As String
Dim regEx As New RegExp
Dim Rand As String

'Analize the pattern and create random string satisfying the pattern needs to be valid
'Merge all parts and return as a result in Rand


End Function
Community
  • 1
  • 1
Antonycx
  • 209
  • 3
  • 14
  • 2
    Do you mean a "regex-like pattern"? Regular regex is for parsing, not for generating strings. If you mean for us to help you build a generic function which takes a regex pattern and then generates "random" strings based on that pattern, then that's some amount of coding which would be required, and there's nothing suggesting you've even *tried* to solve this so... Recommending tools is off-topic for SO. – Tim Williams Feb 28 '18 at 18:24
  • An example would be helpful. Please [edit] your question. – O. Jones Feb 28 '18 at 19:22
  • This question boils down to your code comment: `Analize the pattern and create random string satisfying the pattern needs to be valid` This is a huge undertaking. It's a novel idea what you are trying to do, but there isn't going to be a simple path to solving it (at least none that I'm aware of). In fact... If I had to solve for this comment, I would probably download a javascript library for it (since there appear to be a few of [them](https://github.com/fent/randexp.js?)), make a quick form in HTML to use it, and then webscrape my local form through VBA. – JNevill Feb 28 '18 at 20:58
  • That sounds interesting, but since I do not have experience using VBA, it seems that I'll have to do research on how to connect html with VBA code. Another option that I have seen is to use Kutools, but I do not know if it is useful for what I need. I'll search and try to resolve this. – Antonycx Feb 28 '18 at 21:13
  • interesting you want to use a regex as a generator pattern instead of how they are normally used (as a matching pattern) – Jasen Feb 28 '18 at 22:44
  • 1
    @Antonycx - It seems to me that there are far easier ways to generate random text that matches a pattern than to create a "reverse regex" tool. – Enigmativity Mar 01 '18 at 00:08
  • @Enigmativity Here, here. – QHarr Mar 01 '18 at 21:01

1 Answers1

1

Got it! Using Java language + VBA Excel and based on these links:

Microsoft Excel Macro to run Java program

https://www.ozgrid.com/forum/forum/help-forums/excel-general/142069-calling-and-executing-a-java-program-from-excel-vba-macro

Here i found lots of libraries that already generate values based on a regex.

I chose this to test.

Why?, I tested with Javascript libraries, but i found it very complicated to use along with VBA, Phyton libraries less complicated, but when i saw an example with Java was desicive.

What i did was:

1 - Download generex.jar version 1.0.2 with dependencies from here

2 - Create a Java Project with a Main class, in my case RegexToValue with the following code:

import com.mifmif.common.regex.Generex;

//args[0] receive regex to init Generex Object
public class RegexToValue {
    public static Generex generex;
    public static int attempts = 0;

    public static void main(String[] args) {
        if (args.length > 0) {
            generex = new Generex(args[0]);
        } else {
            generex = new Generex("[0-3]([a-c]|[e-g]{1,2})"); //default init
        }
        generateValue();
    }

    public static void generateValue() {
        try {
            System.out.print(generex.random());
        } catch(Error e) {
            if (attempts <= 3) {
                attempts++;
                generateValue();
            } else {
                System.out.print("Error");
            }
        }
    }
}

3 - Import the two .jars downloaded from step 1 into the project (Libraries -> Add JAR file)

4 - Generate JAR file of the project with imported dependencies from step 3, all together

5 - I put the generated JAR and the Lib folder into C:\Generex\ for simplicity

6 - Create an Excel file enabled for Macros

7 - Create a Module (Alt + F11) -> Menu Insert -> Module with the following code:

#If VBA7 Then
    '64 Bits
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    '32 Bits
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Public Function fillCellWith(regxPattern As String)
    Dim prog As Object

    '// pass the program name to java on command line, and pass any arguments after.
    Set prog = CreateObject("WScript.Shell").exec("javaw -jar ""C:\Generex\RegexToValue.jar"" " & regxPattern)
    While prog.Status = 0
        Sleep 1000 '// put thread to sleep for 1 sec.
    Wend
    fillCellWith = prog.StdOut.ReadAll
End Function

8 - Use a formula like this in a cell:

=fillCellWith("(blue|red|yellow|green)\.(oak|cedar|willow)\@(yahoo\.co\.uk|google\.com|example\.org)")

9 - See results =)

Antonycx
  • 209
  • 3
  • 14