# xlCubeSuper7: An Unexpected Major Update – New Download – UDF Power

Due to some recent activity in the playing of this game, I started to think about any enhancements that would make the game easier to play. I realized that if a column was added to the scoring that showed the number of hits on each individual sheet for each cube, it would provide additional information to the player. So, my goal was to add that column, as shown in the figure below. Initially, I thought that this would be a relatively simple task. However, I was wrong. The cube ships are laid out randomly as 3-D ranges. It is difficult or impossible to return the 2-D range associated with the 3-D range with normal worksheet formulas due to the limited ways to operate on 3-D ranges with worksheet functions. Thus, I realized that a UDF would be needed, since I did not want to modify the original programming. Then, I determined that the information that the UDF would require is:

1. The 3-D range reference string.
2. The “number” of the cube.
3. The position(s) of the desired information in a 3-D range reference string.
4. The first and last sheet of the 3-D range.
5. The 2-D range associated with the 3-D range.
6. The sheetname where it is being called from.

The complete code for the UDF is shown below.

Function SumSheetsHits()

Application.Volatile True

sRangeRaw = ThisWorkbook.Names(“cShip” & Application.Caller.Row – 5).RefersTo

sExclamation = Application.Find(“!”, sRangeRaw)

sColon = Application.Find(“:”, sRangeRaw)

sTick = InStr(sColon, sRangeRaw, “‘”)

If sColon = 4 Then

iFirst = Val(Mid(sRangeRaw, 3, 1))

Else

iFirst = Val(Mid(sRangeRaw, 3, 2))

End If

If sTick – sColon = 2 Then

iSecond = Val(Mid(sRangeRaw, sColon + 1, 1))

Else

iSecond = Val(Mid(sRangeRaw, sColon + 1, 2))

End If

aSheetNum = Val(ActiveSheet.Name)

srange = Mid(sRangeRaw, Application.Find(“!”, sRangeRaw) + 1, 255)

If aSheetNum < iFirst Or aSheetNum > iSecond Then

Else

SumSheetsHits = Application.Sum(ActiveSheet.Range(srange))

End If

End Function

So, the function =SumSheetsHits() is entered in column Y beginning on row 6. The line of code ThisWorkbook.Names(“cShip” & Application.Caller.Row – 5).RefersTo returns the 3-D formula string for cube 1 when entered in Y6. Application.Caller.Row is in row 6 in this example, so ThisWorkbook.Names(“cShip” & 6 – 5).RefersTo cShip1 and returns “ =’6:8’!\$B\$5:\$D\$7 “ which is the 3x3x3 cube 3-D reference.

Next, the 3 important characters for finding the desired information in the string are located. Note that both the FIND function and the InStr function are to find those positions, but really the InStr function could have been used for all 3 lookups.

sExclamation = Application.Find(“!”, sRangeRaw)

sColon = Application.Find(“:”, sRangeRaw)

sTick = InStr(sColon, sRangeRaw, “‘”)

The variable sTick shows the location of the 2nd tick in the string, since the search is made after the colon.

In order to find the first sheetname in the string (which by definition is a 1 or 2 digit number), this simple if, End if logic block of code is used.

If sColon = 4 Then

iFirst = Val(Mid(sRangeRaw, 3, 1))

Else

iFirst = Val(Mid(sRangeRaw, 3, 2))

End If

Note the use of the Val function, which converts the numeric string to an actual number. The find the second sheetname, the difference of sTick and sColon are used to define whether a 1 or 2 digit number is used.

If sTick – sColon = 2 Then

iSecond = Val(Mid(sRangeRaw, sColon + 1, 1))

Else

iSecond = Val(Mid(sRangeRaw, sColon + 1, 2))

End If

The sheetname is returned by the following code.

aSheetNum = Val(ActiveSheet.Name)

Since (fortuitously) the sheet names are consecutive numbers, that information makes the job of bounding the 3-D range much easier.

The 2-D range part of the formula is:

srange = Mid(sRangeRaw, Application.Find(“!”, sRangeRaw) + 1, 255)

and it is used with the final block of code.

If aSheetNum < iFirst Or aSheetNum > iSecond Then

Else

SumSheetsHits = Application.Sum(ActiveSheet.Range(srange))

End If

Since the 3-D range is bounded by these 2 sheets, any sheets outside would not be capable of recording any hits on this ship.

You might question the use of the SUM function here, when a hit on a ship appears as an “X”. Actually, when a shot is made, a 1 is placed in the cell, and the appearance of the “X” occurs through custom fomatting.

xlCubeSuper is now available with this new game functionaity, and can be downloaded by clicking the following link.

xlcubeSuper

# Adding Data Validation to ANY Cell Using an Excel User-Defined Function

In previous articles I have written about the connection between Excel UDFs and objects, I have noted that almost any object can be invoked through its use. So, it was with interest that I read a recent article on Debra Dalgleish’s website on the accidental deletion of dropdown arrows in cells with data validation lists when running macros.

https://contexturesblog.com/archives/2018/10/11/missing-data-validation-arrows/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+contextures%2FdCfy+%28Contextures+Blog%29

In the article, she stated

“If you run a macro that deletes shapes on a worksheet, it might also delete the drop down arrow. Excel sees that arrow as a worksheet shape.”

Well, I just wrote this article where a UDF invoked filter arrows to emulate the properties of Excel new FILTER function.

So, I figured that, just maybe, a UDF could do the same thing with data validation. It CAN! And, it can add the data validation to any desired worksheet cell.

The following code is for the AddValidation VBA UDF:

Function AddValidation(vRange As Range, vList As Range)

On Error Resume Next

End Function

Its use can be seen in the following figures. In the first, the formula

=AddValidation(A3,letters) is in cell F5. Actually, in the figure the 2nd argument in the UDF is J1:J3, but that is equivalent to the defined name range called letters. The first argument points to cell A3 as the target for adding the data validation list. In the 2nd figure, you can see that the data validation drowdown arrow appears in A3. Pretty amazing! I think you can see the myriad of possibilties that are possible by using this technique.

ValidationUDF

# Excel Filter and Sort UDFs – Amazing!

In late September 2018, Microsoft revealed a number of fascinating new Excel worksheet functions.

https://techcommunity.microsoft.com/t5/Excel-Blog/Preview-of-Dynamic-Arrays-in-Excel/ba-p/252944

Bill Jelen has churned out an amazing 66-page ebook on the new functions.

I strongly recommend that you look at the links listed above. Only then will you be able to appreciate what is presented in this article.

But, I don’t have immediate access to looking at these functions in the newest versions of Excel. So, that started me thinking: Could something similar to this work in a user-defined function (UDF)? My latest UDF creation, along with links to other interesting uses of UDFs can be viewed here.

https://dhexcel1.wordpress.com/2018/05/26/excel-generate-a-list-of-antonyms-using-an-user-defined-function-udf/

Well, to make a long story short, it can and furthermore they operate on the ORIGINAL DATA. This means they can filter a dataset in place and sort data in place instead of creating a duplicate dataset or subset like the new Excel functions do. You may ask how a UDF, entered in a worksheet cell remote for the data, can filter and/sort that table of data, and even I did not think that it was possible, or I would have exploited this long ago.

So, the FILTERFUN function presented here (I would have called it FILTER but I would not want it to conflict with the new Excel functions if used alongside them) can filter a table of data. In the figure shown below, the FILTERFUN function is shown along with a set of data. This UDF has 2 arguments, the field in the table to be filtered and the criteria to be used for the filter. If you are familiar with using Excel’s advanced data filter, you will note that the criterial in the 2nd argument uses the same syntax and has wildcard filtering abilities. The result for entering this formula can be seen in the next figure. The code for the FILTERFUN function fixes the location for the table to start in cell A1, but that can easily be modified, as can be seen in the code for the next magical UDF, the SORTFUN function.

This UDF has 3 arguments: the table range, the field to be used as the sort key, and the desired sort order, as shown: In this case, when the SORTFUN function is entered, the desired sort of the table is performed. The following code for both of these functions is shown below. These functions provide a utility that can be made to mostly emulate the new Excel FILTER and SORT functions, while also allowing the desired result without creating new data tables.

I hope you find this technique useful. If so, share it with your Excel friends and colleagues.