In certain situations, such as systematically generating computer code for analyzing data, it is necessary to create a combination of a variety of factors. Creating factorial combinations is easy when there are only a few factors and levels, but in more complicated situations the task is tedious and prone to error. This Excel file uses VBA to create full-factor combinations with up to 15 factors, each with up to 100 levels. An optional delimiter (such as ” AND “) can be inserted between each level in the combination.
Continue reading “VBA code for creating factorial combinations”
VBA code for creating text files based on data fields
I needed to create a lot of text files that were similar to one another except they were altered depending on a simple database. It would be something like an old-school MS Word “mail merge,” except it would create separate text files. I wanted an easy-to-use tool, so I put together something in Excel using VBA. The tool could create thousands of text files that vary on (up to) 15 different fields (i.e., dimensions). For example, this tool could create many HTML files that are all alike except for a product description, a photo, and a URL link. My original purpose for creating the tool was to setup the coarse-grained parallelization of a CPU-intensive Monte Carlo simulation.
Continue reading “VBA code for creating text files based on data fields”
VBA code for performing many searches-and-replaces in an Excel file
I had a long list of search-and-replace tasks that I needed to do on a text file. I wanted an easy-to-use tool, so I put together something in Excel using VBA. The tool starts with a “BEFORE replacing” worksheet and then creates an “AFTER replacing” worksheet. The replacements are performed based on a “Definitions of Replacements” worksheet, which directs the macro to replace everything in the first column with a paired item in the second column.
Continue reading “VBA code for performing many searches-and-replaces in an Excel file”
Word VBA code for moving the cursor with a ‘find’
The following VBA code will …
- Search a Word document for “asdf”
- Position the cursor immediately after “asdf”
- Add the letters “jkl;” at the new cursor point
- Move the cursor back four spaces, leaving it immediately after “asdf”
Continue reading “Word VBA code for moving the cursor with a ‘find’”