Category Archives: Other

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.

Advertisements

xlCubeSuper An Excel Game Explained – Part2

In Part1, the new version of xlCube (now referred to as xlCubeSuper) was introduced.

https://dhexcel1.wordpress.com/2018/01/19/xlcubesuper-an-excel-game-explained-part1/

In Part2, I will describe the different techniques to make this game application work. But, before I do, I think that it is important to point out that the game is not tied down tight. For example, there is no workbook-level protection. If users wanted to, they could damage the integrity of the game by adding or deleting worksheets (if they saved the changes). So, the game is not tamper-proof and was never intended to be. Rather, it was made as a game to be enjoyed and an application to learn from. Here then is a list of the major techniques to be discussed.

  • 3D formulas
  • Defined named formulas
  • Conditional formatting formulas
  • Formulas to aggregate shots and hits
  • Use of VBA to:
  1. Add 3D ranges
  2. Shoot using VBA event procedure.
  3. Create and delete non-ribbon (old style) menus.
  4. Store and sort game scores.
  5. Control the formatting of shots and hits.
  6. Display information and results.

The backbone of this game is the 3D formulas created to define virtual cubes and the center of those cubes. We will see in a later post how these formulas are generated through VBA, but for now, here is what they look like as shown in Name Manager.

xlCS2_3Dformulas

In Part3 we will see how these formulas are created in random locations by VBA.

xlCubeSuper An Excel Game Explained – Part1

I have just recently updated/modified my xlCube game that I first started on 20 years ago. The main modification is that it now has 7 cubes that must be destroyed before the game is over. For those who have never played the game, the previous version can be found at:

https://dhexcel1.wordpress.com/2016/11/29/xlcube-an-excel-game/

The new game is called xlCubeSuper. And, like the previous release, I encourage you to dig into the details of how this application was constructed. But, I know that your time is precious, and you may feel that this would not be an effective use of your Excel time. So, this time I am going to explain in detail all of the tricks and techniques used in the making of this game. In Part 1, I am only making it available to you. The explanation will come in subsequent posts. In the meantime, have fun with it.

You can download the file here.

xlcubeSuper

 

#Excel Data Validation – Non-Contiguous Ranges and Changing Data Validation List after Picking

I recently saw this challenge for creating a data validation list from 2 non-contiguous ranges.

https://www.sumproduct.com/blog/article/monday-morning-mulling-december-challenge

Then, while looking up current information about data validation tricks, I reread this post on Debra Dalgleish’s Excel site, which showes a way to change the data validation list based on items picked.

http://www.contextures.com/xlDataVal03.html

I decided that I would try to combine both of these techniques, while at the same time creating the required data validation list without the need for helper columns. When I started on this, I was not sure that it would be possible, but that is the kind of challenge I like 😊.

I had previously published a method for combining non-contiguous ranges into a comma-delimited string.

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

Using this technique along with modifying the ranges to exclude blank values, the following formula produces a delimited string combining the elements of two ranges named List1 and List2, as shown in the figure.

DV_PIC1

TJ_TLists =TEXTJOIN(“,”,TRUE,IF(ISBLANK(List1),””,List1),IF(ISBLANK(List2),””,List2))

It is important to note here that any number of ranges (rectangular, non-contiguous or 3D) can be combined in this step to afford the data validation list in the final step. As an example, see:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

The next formula converts this delimited string into an array.

CombinedDV =TRIM(MID(SUBSTITUTE(TJ_TLists,”,”,REPT(” “,999)),ROW(INDIRECT(“1:”&LEN(TJ_TLists)-LEN(SUBSTITUTE(TJ_TLists,”,”,””))+1))*999-998,999))

Unfortunately, an array cannot be used directly as a data validation list. But, since there is more work to do to create data validation that can be used as a pick list, the following formulas are needed.

MatchArr=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),CombinedDV,””)

affords {“”;”b”;”c”;”d”;”e”;”f”;”g”;”h”;”I”;”j”;”k”;”l”;”m”;”n”;””}

MatchRow=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),””)

affords {“”;2;3;4;5;6;7;8;9;10;11;12;13;14;””}

Then in cell H2 is entered the formula =INDEX(MatchArr,SMALL(MatchRow,ROW()-1)),which is filled down until a formula returns an error. This is the range to be used as a data validation list.

As shown in the figure, the range where data validation is applied

SelectDV=DVSheet!$B$2:$B$16

contains an “a” and an “o”.

Then, the formula used for the data validation list is

DVList=DVSheet!$H$2:INDEX(DVSheet!$H$1:$H$16,MATCH(TRUE,ISERROR(DVSheet!$H$1:$H$16),0)-1)

So, when the data validation is used in its current state, the list will not contain those two letters.

I hope that you find this useful.

The example file can be downloaded here.

DV_TwoLists

#Excel: Extracting an Array of Words From a Sentence

 

The following formula will create an array of words from a string (sentence).

= ArrayFromSDS(TEXTJOIN(“”,,IF(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “,” “,IFERROR(CHAR(64+MATCH(MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“),ROW(INDIRECT(“1:”&LEN(A1))),1),CHAR(64+ROW(INDIRECT(“$1:$26″))),0)),””))))

Here is an explanation of how it works.

The array used in the 3rd argument of the TEXTJOIN function starts with the 1st part of the IF formula, shown below.

IF(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “,” “,

which keeps any space from the string in cell A1. The rest of the IF formula

IFERROR(CHAR(64+MATCH(MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“),ROW(INDIRECT(“1:”&LEN(A1))),1),CHAR(64+ROW(INDIRECT(“$1:$26″))),0)),””)

returns only letters from the string in A1. The 1st argument of the MATCH function in this construction,

MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“), ROW(INDIRECT(“1:”&LEN(A1))),1)

is very similar to the 1st part of the IF function, but it has one important difference.

Instead of using the string from cell A1, the formula SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“) is used instead. The reason for doing this is that the MATCH function recognizes the * and ? symbols as wildcard searches. So, if the 2nd argument of the MATCH function does NOT contain a * or ?, the character “A” will be returned instead (if not removed from the core string).

The 2nd argument of the MATCH function is

CHAR(64+ROW(INDIRECT(“$1:$26”)))

which returns an array of letters from A TO Z.

The result of the MATCH function is an array with numbers from 1-26 for positions in the string with letters and “” if not. For example, if the string in A1 is “ AAx,d a.”x~y*z”. c?e! ”, then the MATCH array will return

{1;1;24;#N/A;4;#N/A;1;#N/A;#N/A;24;#N/A;25;#N/A;26;#N/A;#N/A;#N/A;3;#N/A;5;#N/A}

This array is particularly useful in this specific case, since the numbers can be converted to the letters in the string by using the CHAR function (along with the IFERROR function to turn errors to an empty string). That converts the array to

{“A”;”A”;”X”;””;”D”;” “;”A”;””;””;”X”;””;”Y”;””;”Z”;””;””;” “;”C”;””;”E”;””}

Now, this array can be used as the main argument in the TEXTJOIN function to afford the string

AAXD AXYZ CE

Now, using the ArrayFromSDS user-defined function (shown below)

Function ArrayFromSDS(MyString As String)

ArrayFromSDS = Split(MyString, ” “)

End Function

produces this array of words.

{“AAXD”,”AXYZ”,”CE”}

HTH!

 

#Excel: Remove Multiple Characters From a String Using The TEXTJOIN Function and Without Using the SUBSTITUTE or REPLACE Function

 

There are a number of examples of the removal of characters from a string which utilize nested SUBSTITUTE or REPLACE functions. However, they are hard-coded in that the formulas are built with a set number of characters to remove based on the times that the SUBSTITUTE function is used. The formula methodology I am presenting here is more flexible and robust than previous solutions.

In this example, I am trying to remove all punctuation from a string, specifically the one shown below from cell A1. You will note that this string contains five different punctuation symbols, several occurring more than once.

x,da.”xyz”.c?e!

The following array formula removes those symbols

=TEXTJOIN(“”,TRUE,IF(ISERROR(MATCH(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),{“,”;”.”;”?”;”!”;””””},0)),MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),””))

and affords the desired string shown below.

xdaxyzce

HTH!