I want to introduce a very useful, but relatively unknown function that is made available to us in Microvellum. The RangeToString() function has a very simple purpose, but can be exploited to operate in a lot of useful ways.
The basic function of RangeToString() is to join several cell values into one text string, delimited by a specified character. In plain English, it allows us to take a range of cells, and join their values into a single string of text, but separated by a character of our choosing.
Let's look at the syntax of the function: RangeToString(Range,Text,[Logical Test])
Range - is the range that contains the cells.
Text - is a character used to delimit the string.
[Logical Test] - is the direction, False for Horizontal, True or Omitted for Vertical.
The built in formula editor is a useful way to build, or edit this function.
The beauty of this function is that each parameter can contain logic and is not limited to fixed or hard values. Let's take a look at some examples.
Example 1
The first example use is creating combo box lists. Using the RangeToString() function, we can specify our range to be a list of values that we need to include in a combo box list, and the function will delimit them by a pipe ("|") symbol. An example of this is the Door Library combo box in the cabinet library. Using a named range from the Door Wizard, the combo box list is created using this function: =RANGETOSTRING(D!Door_Library_Names,"|")
As you can see, the function outputs a perfectly formatted combo box list for the local prompt to use.
This can be further enhanced to use a dynamic range that changes as you add or remove values to a LookUpTable, for instance. In the standard frameless library, we use LookUpTables to add all of the necessary information for hardware pulls. Anytime we add a pull to the library, we must add a row to the LookUpTablePullType, and then also add the name of the Pull to a global list, exactly as we formatted it in the LookUpTable.
We can eliminate that last step, and instead build the global list dynamically from the LookUpTable directly using this function:
=RangeToString(OFFSET(LookUpTablePullType,0,0,ROWS(LookUpTablePullType),1),"|")
Since we already have a defined range in the LookUpTable, I just needed a way to limit that range to the first column. We do this using OFFSET, which allows you to modify a range down to a defined number of row and columns. We know we only need 1 column, but it is the Rows that needs to be dynamic. Using the ROWS() function we can count how many rows are in the LookUpTablePullType range.
Example 2
We can also use this function to create a dynamic dowel pattern list for a HOLES machining token. Often we need the pattern to add or remove dowels based on the width of the part, and normally this is handled through a series of local prompts, and formulae looking to other prompts, that look to globals. Using this function, we can significantly simplify this process.
First we need a list of dowel hole locations. In this example, we are working with a fixed dowel hole layout, where the spacing doesn't change, and we simply add or remove holes from the end based on the size of the part. For simplicity, I created local prompts for each of my 11 holes, each looking to the corresponding global.
Quick Tip
Here is a quick tip, within a tip: to quickly create a list of prompts like this, I try to keep the name short, and logical. I decided to go with 'Dowel Hole' and then number them. Putting the number at the end of the name, allowed me to write the first two names, and then drag the cells down, automatically populating the rest of the names, and sequentially numbering them.
For the value, I needed to reference two globals - G!Dim_To_First_Dowel_Hole and G!Distance_Between_Dowels_Small_Pattern. For each subsequent hole, I needed to add G!Distance_Between_Dowels_Small_Pattern to the previous value. Instead of actually adding the same global to the end of the formula each time, I decided to multiply it by the prompts position in the list using (ROW()-ROW(First_Dowel_Hole)). This way I am referencing the first prompt, so if any rows are added or removed, it doesn't break, and also, I can drag or copy/paste this formula for each row, and it works.
Okay, so now that we have our list of dowel location, we can use RangeToString() to create the dynamic pattern referencing the part width. Since we don't have a defined range in this instance, we can enter the range in a different format:
RangeToString(Dowel_Hole_1:Dowel_Hole_11,",")
This gives us the whole list, but we need to shorten the list to only the dowel location that fit on the part. In this case, our part width is determined by the prompt Rear_Wall_Cleat_Height. Using the MATCH() function, we can determine the row in our range that has the highest value that is less than our part width:
MATCH(Rear_Wall_Cleat_Height,Dowel_Hole_1:Dowel_Hole_11,1)
Since we are limiting our range to the list of dowel holes, it will return a value that matches the Dowel Hole #. Base on our naming convention, we can now use the INDIRECT() function to reference the correct prompt for the end of our range:
INDIRECT("Dowel_Hole_"&MATCH(Rear_Wall_Cleat_Height,Dowel_Hole_1:Dowel_Hole_11,1))
Now putting this altogether:
RANGETOSTRING(Dowel_Hole_1:INDIRECT("Dowel_Hole_"&MATCH(Rear_Wall_Cleat_Height,Dowel_Hole_1:Dowel_Hole_11,1)),",")
Now we have a dynamic Dowel Pattern to reference in the machining token.
Example 3
The last quick example I wanted to show is in creating PLINE vector lists. In an early post, I walked through a Formatting Trick for PLINE Vector Lists and I explained how to use the VECTOR() function to create that list. Using RangeToString() we can create the same list, but with a shorter formula.
If you have a list of PNT() location in a range, like I lined out in that original post linked above, you can simply reference those prompts as a range, and delimit the list by a pipe ("|") symbol:
RangeToString(PNT_1:PNT_4,"|")
That is it for now, but I hope you find this post helpful. If you have any questions, please reach out to me from my Contact page.