Monthly Archives: April 2017

#Excel: Using Conditional Formatting to Highlight Cells Containing User-Defined Functions by David Hager

Conditional fomatting (CF) in Excel is a powerful tool for highlighting cells that meet certain criteria based on a formula. However, to my knowledge a worksheet formula has never been used with CF to highlight cells containing user-defined functions (UDF). Presented here is a method to accomplish this.

In order to lookup whether a formula is a UDF, a lookup table of all Excel worksheet functions is required. I found the list for this at this Microsoft site.

https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

The list is in the range A2:A472 in the example workbook. It was defined as xlFunctionList. I tried to just name an array of function names, but it exceeded the number of characters allowed.

So, with E2 as the active cell, I created the following defined name formula:

IsUDF=ISERROR(MATCH(MID(FORMULATEXT(E2),2,FIND(“(“,FORMULATEXT(E2))-2),xlFunctionList,0))

The formula =MID(FORMULATEXT(E2),2,FIND(“(“,FORMULATEXT(E2))-2) locates the first occurrence of the left parens. This value in used by the MID function to return a string with the function name. Then, the MATCH function looks up whether that string is in the function list. If it is not there the ISERROR function will return True. Thus, when applied as conditional formatting to cells E2:F2, E2 has a yellow highlight, indicating that a UDF function is in that cell. F2, containing a native function, is not highlighted. Note, though, that this technique only works if the function is at the beginning of the formula. Perhaps a followup to this article will deal with that issue.

isudf1

You can download the example file here:

IsUDF

#Excel Super Links #11 – shared by David Hager

I am happy to see that y’all are finding these links useful.

In Cell Charting

http://dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/

Calculate Start and End Dates in Power Query

http://www.excelguru.ca/blog/2017/03/14/calculate-start-end-dates/

3 Helpful Power BI Features You Might Not be Using

https://www.blue-granite.com/blog/3-helpful-power-bi-features-you-might-not-be-using

Formula Forensics 042: Reverse Text – A Formulaic Solution Using TEXTJOIN

http://chandoo.org/wp/2017/01/04/formula-forensics-042-reverse-text-a-formulaic-solution/

Excel Irregular Banding on a Filtered List

https://dhexcel1.wordpress.com/2014/01/18/excel-irregular-banding-on-a-filtered-list/

 

#Excel Super Links #10 – shared by David Hager

Here is the 10th issue of ESL. One of the links in each Excel Super Links will be to articles on my web site from now on.

How to Solve an Equation in Excel

http://chandoo.org/wp/2013/09/19/how-to-solve-an-equation-in-excel/

Excel Matrix Multiplication – Replacing MMULT with Power Query

http://datachant.com/2016/06/01/excel-matrix-multiplication-replacing-mmult-with-power-query/

Parent-Child Hierarchies in DAX

http://www.daxpatterns.com/parent-child-hierarchies/

User-defined Excel Chart Tooltips

http://www.clearlyandsimply.com/clearly_and_simply/2016/09/user-defined-excel-chart-tooltips.html#more

BINGO! An #Excel Game – Random Numbers W/O Repetition Using Worksheet Formulas

https://dhexcel1.wordpress.com/2015/03/31/bingo/

 

#Excel Short and Sweep Tip #3 (Freeze Values) by David Hager

Highlight a worksheet range that contains formulas or a mixture of formulas and values. Grab the edge of the range with right-click and pull away, then immediately place back in the same range location. The right-click menu will appear. Click the option labelled “Copy here as Values only”. Every formula in the range will be converted to its underlying value.

 

#Excel Super Links #9 – shared by David Hager

Issue #9 is here! Starting wth issue #10, I will start presenting some of my blog posts.

Excel Magic Trick 1403: DAX Formulas: Explicit or Implicit Measures? Why Explicit is Better

https://www.youtube.com/watch?v=OaVTPas2x8Y

Solving Business Problems – Power Query Does That

http://www.excelguru.ca/blog/2017/02/02/solving-business-problems/

The Art & Science of Creating An Excel Dashboard (With Examples & Templates)

https://trumpexcel.com/creating-excel-dashboard/

Prevent Users Ctrl Break (ing) Your VBA Code During Execution

http://www.databison.com/prevent-users-ctrl-break-ing-your-vba-code-during-execution/

Sorting a List Alphabetically (Without Filters) with Excel Formulas

https://excelxor.com/2015/01/22/sorting-a-list-alphabetically-without-filters/#more-4166

#Excel: Modifying Shapes From An UDF in a Worksheet Cell by David Hager

It is well-known that an used-defined function created with VBA and entered as a worksheet formula cannot alter the worksheet. But, it is not true. A number of examples where this can occur have been discussed and documented. In particular, a post by John Walkenbach referred to the altering of worksheet shapes (see link below) with an UDF.

http://dailydoseofexcel.com/archives/2007/01/12/modifying-shapes-and-charts-with-udfs/

Since this technique has not gained popularity in use, few people know about it. So, I have made an user-friendly model to demonstrate its utility.

The biggest deficiency in working with shape properties is the lack of a good source of lookup information on what the index numbers refer to. I iterated (manually) through all 181 index numbers for shape type (assigned the shapes names based on what I decided, they can be changed is desired), and the table I created can be seen in the ShapeLists worksheet in the example workbook. I defined a list of shape names and used it in a Data Validation List for cell B2. Then, in cell B3, this formula looks up the correct shape index number for the selected shape. That result is used by the ModifyShape UDF in cell G2.

=INDEX(ShapeIndex,MATCH($B$2,ShapeName,0)) ‘in B3

=ModifyShape(1,$B$3) ‘in G2

 

Here is the ModifyShape VBA function.

 

Function ModifyShape(ShapeNumber, ShapeType, Optional Vis As Boolean = True)

With ActiveSheet.Shapes(ShapeNumber)

.AutoShapeType = ShapeType

.Visible = Vis

End With

End Function

Note that it has an optional 3rd argument. It is TRUE by default, but if is set to FALSE, the shape is invisible. And, all of this occurs simply by the recalculation of the UDF.

There are a number of shape properties that can be manipuated by an UDF. One fuction that I specifically designed for this article is the ModifyShapeColor function.

Function ModifyShapeColor(ShapeNumber, rColor, gColor, bColor)

With ActiveSheet.Shapes(ShapeNumber)

.Fill.ForeColor.RGB = RGB(rColor, gColor, bColor)

End With

End Function

But, for this example to be fully effective, I needed a reference table of RGB colors that could be utilized by this function. I was able to find such a table at this source:

http://rapidtables.com/web/color/RGB_Color.htm

and I pulled this information into the workbook by using Power Query. Then, I copy/pasted it to the ShapeLists sheet and deleted the query (no need to refresh static information). I defined a list of shape colors and names and used the Shape color name in a Data Validation List for cell C2. Then, in cells C3:C5, these formulas look up the correct RGB numbers for the selected color.

=INDEX(Red,MATCH($C$2,ColorName,0)) ‘in cell C3 etc.

=ModifyShapeColor(1,$C$3,$C$4,$C$5) ‘in cell G3.

You can see the resulting shape and color in the following figure.

ModShape1

Now, realize that the worksheets can have multiple shapes controlled by the UDFs pointing at each shape. This opens up a new way to visualize data. I hope that you can incorporate this technique into your projects.

You can download the workbook here:

ModShape