Quick Steps

CVC Generator documentation

5 Simple Steps to start working

  1. Press the 'Clear All' Button on the "CVC Generator" Worksheet
  2. Copy and Paste your required CVC Codes and/or Descriptions into the "CVC Generator" Worksheet
  3. Tweak any of the options on the "Options" Worksheet, for example you may wish to add a separate Column of specific data
  4. Highlight the required CVC combinations and press the Generate Combinations Button
  5. Copy the generated CVC's into APO or use for any other kind of generation where combinations need to be spawned en masse

Acknowledgement

CVC Generator was written by Mark Kubiszyn

  • the latest version is version 4.2.1
  • as per terms of the Software License, you should leave the following code attribution in situ

' © Copyright/Author:
' Mark Kubiszyn 2012-2017. All Rights Reserved
' Website/Follow:
' http://www.kubiszyn.co.uk/
' https://www.facebook.com/Kubiszyn.co.uk/
						

Purchase

You can Purchase CVC Generator for £2.99 via FastSpring by clicking here

The Options Worksheet

The "Options" Worksheet is used to set or edit any available options for the CVC Generator. Available options are:

Options Table

Highlight Colours - used to set the Fill Colour and the Font Colour for the highlighted Cells in the Table on the "CVC Generator" Worksheet. No other Formats are picked up, but this would be easy to modify in the Code should you wish to change the Font Name for example

Column Output Order - (protected from deletion) used to specify how the output is made for the selected Combinations and the Descriptions. This setting is configured in the 'Lists Table' and you can then select one of your lists from the Drop-down in the Cell next to the option

Column Output Headers - (auto-populated from the 'Lists Table' and protected from deletion) used to specify the Headers for the output Columns. This setting is configured in the 'Lists Table' and a lookup Formula then retrieves the setting automatically, making it easy to switch settings whenever you like

Text List - (auto-populated from the 'Lists Table' and protected from deletion) used to specify the Text Fields whenever you 'insert' Columns into the 'Column Output Order' list. This setting is configured in the 'Lists Table' and a lookup Formula then retrieves the setting automatically, making it easy to switch settings whenever you like

Sort - (auto-populated from the 'Lists Table' and protected from deletion) used to specify how you would like to Sort the output data by Column with Sort direction. This setting is configured in the 'Lists Table' and a lookup Formula then retrieves the setting automatically, making it easy to switch settings whenever you like

Auto Column Fit - On/Off toggle. If this option is ticked then the output Columns will be automatically re-sized to fit the data and Headers width. If this option is not ticked, Column re-sizing will not be applied

Lists Table

Column Output Order List - used to store Column output lists that can be selected for the 'Column Output Order' option, detailed above. These can be lists of Columns. For example, you may want to change the order that the Colums are output or you may want to include Blank Columns. To do this you can set up a comma-delimited list like this '1,1,blank,3,5' which means output Column 1 from the Table for the first Column of the output. Then repeat this Column again for the second Column of the output. Then insert a Blank Column. Then output Column 3 from the Table as the fourth Column of the output. Lastly output Column 5 from the Table as the fith and last Column of the output. You can also use the 'insert' keyword to tell the Code that it needs to insert another Column at the point specified - whenever you do this remember to pass in a Text Field in the 'Text List' Option detailed below

Column Output Headers List - used to store Column output Header lists that will be automatically selected for the 'Column Output Headers' option, detailed above. If you only enter one Header, then that will be used for the first Column only. If you enter a comma-delimited list of Headers like this 'Product,CPG,Depot' then the first Column for the output will have the Header 'Product', the next Column for the ouput will have the Header 'CPG' and the last Column of the output will have the Header 'Depot'

Text List - (new for version 4.2) used to store a comma-delimited list of Text Fields. You only need to add these if you use the 'insert' keyword in the 'Column Output Order List' ie. "1,1,blank,3,5,insert" whereby you then need to pass a single Text Field, for example: B001

Sort - (new for version 4.2) used to Sort the final output of the data by Column as a pipe '|' and comma-delimited list - you can specify the Sort direction ie. "1|Ascending,5|Ascending" meaning Sort Column A, Ascending then Column E, Ascending

Pivot Table Slicer Buttons

The Buttons shown in the "CVC Generator" Worksheet use Slicers connected to the Pivot Tables in the "Options" Worksheet. The Buttons use a Cell to store the Name of the Button and a Cell to slightly reformat the Name to include padding to center the Text better in the Slicers. These Cells are protected but you can unprotect them in the Code and edit them if you wish (go into the Code Editor and into the Code Module for the "Options" Worksheet - comment out the Code). They also contain Data Validation which you must also clear before editing them. When you have edited a Button Cell, you will need to click in a Cell in the respective Table and use right-click, refresh to refresh the Pivot Table. The Code to capture Pivot Table Slicer clicks is in the 'ThisWorkbook' Code Module - it is fairly simple to understand and each click will run the appropriate Subroutine in the 'mdlSubroutines' Code Module

The CVC Generator Worksheet

The "CVC Generator" Worksheet is where you add your own specific Combinations of CVC's. These may be Products, Customer Planning Groups and Depots. A Table is provided for you to do this. The Table can be moved anywhere in the Worksheet and the Code will still work

The way that you create the Combinations is by selecting the Odd Column Cells for the Combinations that you want to generate permutations. So for instance in the first image at the top of this article I selected or highlighted 2 CVC1's (or Products - these Products can be in any order or be disparate). I then selected 3 CVC2's (or Customer Planning Groups) and then 5 CVC3's (or Depots). So I will be generating 2 x 3 x 5 = 30 Combinations

Once you have made your selections, press the 'Generate Combinations' Button to generate the Combinations. There are 'Clear' Buttons to clear the highlighted Cells in each Odd Column and a 'Clear All' Button to clear all highlighted Cells within the entire data Table. You can change the highlight Fill Colour and the highlight Font Colour on the "Options" Worksheet

The CVC's Worksheet

The "CVC'S" Worksheet is used for the output of the selected CVC's and the resulting Combinations. The first Row is set to Bold to highlight the Headers and has Freeze Panes set (you can remove this if you like). The data output can be formatted with Blank Columns and / or repeated Columns of generated Combinations. The headers can be renamed to anything that you like (they can even be the same name). The output can have Columns set to Autofit the width of the data and Headers. You can adjust the Zoom level if required and do some other formatting to this Worksheet, just remember each time Combinations are generated, the Cells contents will be cleared (I do not clear the formatting)

Screen Shots

The main interface (list items can be highlighted using the Mouse):

CVC Generator - The main interface

The options interface. You can store lots of different setup lists here - shown on the right-hand side 'Output List Setup' Table. You pick the list in the Cell next to the 'Column Output Order' configuration on the left-hand side underneath the 'Options Table'. I have picked the list '1,1,blank,3,5,insert' - see below for the generated combinations:

CVC Generator - The options interface

The generated combinations built from the highlighted items shown above. See how the combinations have been generated - I have chosen CVC1, then I have chosen to repeat CVC1, then add in a Blank Column, then CVC2 and CVC3 and lastly I have chosen to include a Column with repeated text 'Hello World'. This is the type of flexibility you have to generate huge lists of combinations:

CVC Generator - The generated combinations

Support

Support is only given to Purchased Software. You should only contact me on the Support Email address supplied by FastSpring - please provide proof of purchase ie. Email address or Order Reference

Changelog

20.04.2016 - (Version 4.2.1) removed some obsolete Defined Names. Added some informative Text Boxes on the "Options" Worksheet

08.03.2016 - (Version 4.2) Changed the GUI slightly as almost half of the Screen in Excel is lost when users enlarge their Screen resolution due to eye-sight issues. Also removed some of the over-use of Colour Fill for the Titles & reduced the default Zoom level rendering a much nicer GUI. As per Q & A from Website - added the ability to Sort the Columns post output and insert Columns into the output where users may require additional Columns populated with a field value that will be copied down the output Range (modified SortInsertArrayColumns() in Multiplex Class). Protection applied to the Table Cells in the Options Table where you select your Column Output Order List to prevent deletion of the Formula

03.03.2016 - (Version 4.1) Added code to turn off screen drawing whilst outputting the cvc's. Use Cells.ClearContents as opposed to selecting the Cells first & then clearing the Selection. Use Cells.EntireColumn.AutoFit as opposed to selecting the Cells first & then performing autofit on the Column Selections. Tweak to the main loop used to generate the CVC Combinations - by using the Lenb() function to check for Empty array slots & then exiting each For loop, we speed up the main loop efficiency by up to 50%...

22.02.2016 - (Version 4) released