Category Archives: Excel

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.

xlCube7_1

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

 

 

 

Advertisements

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.

https://dhexcel1.wordpress.com/2018/09/30/the-new-microsoft-filter-and-sort-functions-are-great-but-what-about-the-old-ones-no-one-know-existed/

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

Range(vRange.Address).Validation.Add Type:=xlValidateList, _

Formula1:=”=” & vList.Address

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.

AddVal1

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!

AddVal2

I think you can see the myriad of possibilties that are possible by using this technique.

Free free to download the sample workbook at the link shown below. HTH!

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.

https://www.mrexcel.com/download-center/books/2018/ExcelDynamicArraysStraightToThePoint.pdf

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 to 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.

 AAA_ff01

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.

 AAA_ff02

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:

 AAA_ff03

In this case, when the SORTFUN function is entered, the desired sort of the table is performed.

AAA_ff04

The following code for both of these functions is shown below.

 AAA_ff05

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.

The Excel file can be downloaded here:

FFunction

 

 

 

 

#Excel: Generate a List of Antonyms Using an User-Defined Function (UDF)

It really is amazing what you can do with an Excel user-defined function (UDF). Here is a list (not comprehensive) of articles from my website that demonstrate clever and unusual uses of UDFs.

https://dhexcel1.wordpress.com/2017/07/12/excel-short-and-sweet-tip-26-showing-an-userform-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/07/excel-short-and-sweet-tip-25-playing-a-random-sound-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/01/automating-word-and-powerpoint-from-excel-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/19/excel-short-and-sweet-tip-23-open-windows-file-explorer-with-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/07/excel-exchange-rate-udf-with-symbol-lookup-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/23/excel-udf-using-google-api-to-return-the-elevation-of-an-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/12/excel-creating-a-udf-with-the-vba-environ-function-and-using-it-to-make-a-table-of-environmental-variables-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/29/excel-worksheet-udf-that-adds-a-comment-to-any-cell-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

In this article, I will show how to use an Excel UDF to return a delimited string of antonyms. It uses Word VBA, so in order for the code to work, you must add a reference to the Microsoft Word Object library in the VBE, as shown below.

Antonym_VBE

Then, the following code for the UDF can be placed in a general module in the VBE.

Function AllAntonyms(TheWord As String)

Dim Alist

Alist = SynonymInfo(Word:=TheWord, LanguageID:=wdEnglishUS).AntonymList

For i = 1 To UBound(Alist)

If i = UBound(Alist) Then

DList = DList & Alist(i)

Else

DList = DList & Alist(i) & “,”

End If

Next

AllAntonyms = DList

End Function

The result for using this UDF in a worksheet cell with the word “excited” as the lookup for antonyms in shown in the following figure.

Antonym1

There are a number of possibilties for extending/modifying this example to other useful UDFs. I hope that you find this useful in that regard.

You can download the workbook here.

 

Thesaurus

 

 

xlCubeSuper An Excel Game Explained – Part6

This article is Part6 of a detailed explanation of the parts that went into the making of my xlCube game.

Sub NewGame()

Over = False

IniVar

ClearBoard

DeleteOldCubes

AskForGameLevel

tdAddNamedRanges

MakeCubeMenu

End Sub

The routine AskForGameLevel was needed in the older version of this game, but adding 2 more cubes changed my mind about having this option, so it is now set to 7 in this version.

The routine tdAddNamedRanges was discussed in a previous post in this series.

 

Sub IniVar()

hint1Val = 0

hint2Val = 0

hint3Val = 0

hint4Val = 0

hint5Val = 0

hint6Val = 0

hint7Val = 0

End Sub

This procedure simply sets variables used in the hint portion of the menu.

Sub ClearBoard() ‘prepares the worksheets for another game (some code can be cleaned up here)

 

Application.ScreenUpdating = False

 

For Each wsh In Sheets

wsh.Unprotect

Next

 

Sheets(Array(“1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”, “10”, “11”, “12”, “13”, “14”, “15”, _

“16”, “17”, “18”, “19”, “20”)).Select

 

Sheets(“1”).Activate

 

With Range(“board”)

.ClearContents

.Font.ColorIndex = 1

ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Range(“board”), Type:=xlAll

End With

This changes the font color for all cells in the board back to black.

 

Range(“A21”).Select

Sheets(“10”).Select

 

For Each wsh In Sheets

If wsh.Name <> “Scores” Then

wsh.Protect

End If

Next

 

Application.ScreenUpdating = True

 

End Sub

 

Sub DeleteOldCubes()

On Error Resume Next

With ThisWorkbook

For d = 1 To 7

.Names(“cShip” & d).Delete

.Names(“centerShip” & d).Delete

Next

End With

End Sub

Since the center of each cube is a named range, they can easily be deleted by using the Names property.

 

Sub MakeCubeMenu() ‘creates menu for xlCube

Dim xlCubeMenu As CommandBarPopup

DeleteCubeMenu ‘ deletes menu if it exists

Set xlCubeMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, temporary:=True)

xlCubeMenu.Caption = “&xlCube”

Set nGameMenuItem = xlCubeMenu.Controls.Add(Type:=msoControlButton)

With nGameMenuItem

.Caption = “&New Game”

.OnAction = “NewGame”

End With

For h = 1 To 7

Set nHintMenuItem = xlCubeMenu.Controls.Add(Type:=msoControlButton)

With nHintMenuItem

If h = 1 Then

.BeginGroup = True

End If

.Caption = “Hint: xlCube &” & h

.Tag = “Hint” & h

.OnAction = HINT_MACRONAME

.Parameter = h

EDMenuItem h ‘procedure that disables specified menu item if the corresponding cube has been destroyed

End With

Next

Set scoresMenuItem = xlCubeMenu.Controls.Add(Type:=msoControlButton)

With scoresMenuItem

.BeginGroup = True

.Caption = “&Scores”

.OnAction = “TheScores”

.Parameter = “Scores”

End With

Set instructMenuItem = xlCubeMenu.Controls.Add(Type:=msoControlButton)

With instructMenuItem

.BeginGroup = True

.Caption = “&Instructions”

.OnAction = “TheScores”

.Parameter = “Instructions”

End With

Set aboutMenuItem = xlCubeMenu.Controls.Add(Type:=msoControlButton)

With aboutMenuItem

.BeginGroup = True

.Caption = “&About xlCube”

.OnAction = “AboutThisGame”

End With

End Sub

This procedure adds the custom menu. Since this is an “old-style” menu, it appears on the Add-ins section of the ribbon.

 

I hoped you have enjoyed this series of articles on how I built this game.

xlCubeSuper An Excel Game Explained – Part5

The main explanation text for this article is in black and bold font. This is the primary procedure used in xlCubeSuper. When a cell is clicked, something happens depending on where it is and what it contains. The original documentation for this procedure is highlighted in green and other procedures used are highlighted in blue. They will be discussed in Part6.

 

“Shooting” is accomplished by this event procedure.

 

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

 

If Over Then Exit Sub ‘the Over variable is set to True when a game is finished

 

If ActiveSheet.Name = “Scores” Then Exit Sub ‘just in case you want to unhide this sheet

If Target.Font.ColorIndex > 1 Then Exit Sub ‘it already contains an “X”

If ActiveCell.Address <> Target.Address Then Exit Sub ‘if it is not a single cell selection

If Intersect(Range(“board”), Target) Is Nothing Then Exit Sub ‘if the selection is not on the board

 

Each of the 5 previous If-Then statement handle situations where the shot is not “correct”.

 

Calculate

NumOfHits = Application.WorksheetFunction.Sum(Range(“sumofhits”).Value) ‘hits before entry

On Error GoTo TheEnd

ActiveSheet.Unprotect

 

Target.Value = 1 ‘entry is made

This is prehaps the most important part of this procedure, although it looks very simple. It is, but adding a 1 to the cell does several things. First, it allows the formulas described in Part4 to keep track of all of the shots and hits. So, how does an X appear on the game board? Each cell has a custom format of “X”,,, which shows an X in the cell no matter what is entered.

 

Calculate

fShtPos = 0

TheRange = “”

 

The following loop checks to see whether the active cell is where any of the 7 centers of the cubes is located. If so, it will disable the hint in the menu associated with that cube and place a blue at that spot. Next, if the game is over (z=7) any hint costs will be summed and then added to the curent # of shots.

 

For p = 1 To 7

 

On Error Resume Next

 

tdRangeParse “centerShip” & p

 

If ActiveSheet.Name & Target.Address = fShtPos & TheRange Then ‘if a center of a cube has been hit

 

Target.Font.ColorIndex = 32

MsgBox “You just destroyed xlCube” & p & “!”, , “DESTROYED!”

Names(“cShip” & p).RefersTo = Range(“sums” & p).Value ‘changes name from reference to a value (# of hits)

Names(“centerShip” & p).Delete

Set cBar = Application.CommandBars.FindControl(Tag:=”Hint” & p)

cBar.Enabled = False ‘disables hint since corresponding cube is gone

 

For z = 1 To 7

 

If Mid(Names(“cShip” & z).RefersTo, 2, 1) = “‘” Then Exit For

 

If z = 7 Then

pScore = hint1Val + hint2Val + hint3Val + hint4Val + hint5Val + hint6Val + hint7Val ‘sums penalty score for hints

sScore = Range(“x15”).Value

fScore = pScore + sScore

MsgBox “The game is over! Your score is ” & fScore & “. ” & Chr(10) & Chr(13) & Level(fScore), , “GAME OVER!”

EnterInfo fScore

SortTable

ThisWorkbook.Save ‘saves changes made to records table

NewGame

Exit Sub

End If

 

Next

 

‘ActiveSheet.Protect

Exit Sub

End If

 

Next

If NumOfHits < Application.WorksheetFunction.Sum(Range(“sumofhits”).Value) Then

Target.Font.ColorIndex = 3 ‘turns red if hits > before event

End If

 

Finally, if the the shot is a hit, it will change the font color to red.

 

ActiveSheet.Protect

TheEnd:

End Sub

 

Although I have not explained every aspect of this procedure, I hope that this helps!

xlCubeSuper An Excel Game Explained – Part4

This is the 4th post to explain in detail how my xlCube game application was constructed. To read the previous post, go to:

https://dhexcel1.wordpress.com/2018/01/21/xlcubesuper-an-excel-game-explained-part3/

The number of hits is recorded by this formula

=SUM(BigBoard)

Where BigBoard is the 3D range representing the playing board.

The number of shots is recorded by this type of formula for each of the 7 cubes.

=IF(ISNA(ERROR.TYPE(cShip7)),SUM(cShip7),IF(ERROR.TYPE(cShip7)=5,0,SUM(cShip7)))

For explanation of the ERROR.TYPE function, see:

https://support.office.com/en-us/article/ERROR-TYPE-function-10958677-7c8d-44f7-ae77-b9a9ee6eefaa?NS=EXCEL&Version=16&SysLcid=1033&UiLcid=1033&AppVer=ZXL160&HelpId=xlmain11.chm60309&ui=en-US&rs=en-US&ad=US

See the figure for each area discussed.

 xlCS4_Board

The following formulas use the EVALUATE function, which is an old-style xlm macro function. It can only be used in a defined name formula. If you have noticed an issue opening the game application, this may be the cause. You would then have to place the file in a trusted location to open it. They are used in the conditional formatting of the 7 hits cells.

gbool1 =ERROR.TYPE(EVALUATE(“cShip”&ROW()-5))=5

This formula is really just there in case there is no ship center for that specific ship. Previously, the user was allowed to select the number of ships to be created, so this conditional formatting formula is really a legacy feature, not affecting the game functionality.

gbool2 =NOT(ISERROR(EVALUATE(“cShip”&ROW()-5)))

If the cell for cShip1 (in row 6) has a black color from conditional formatting, it means that this formula is TRUE, cShip1 does not exist. It has been destroyed.

The next post will focus on “taking a shot”.