Mixed level factorial design algorithm

Assume you have a set of factors let's say [A,B,C] and each factor may be assigned different values (let's call these values "levels"). For the purpose of testing (using general full factorial design) you want to try all combinations of all factor levels with each other knowing that each factor may have number of levels different from the other factor. The following table will be used as an example:

Theoretically, to solve this problem, nested "For" loops will be needed where number of loops equals number of factors. This may be impractical to implement specially when number of factors is not constant.

The proposed algorithm simply mimics the mechanical analog counter (or more specifically Tally counter) like the one shown in the picture below but with a slight difference in implementation. In analog counter, the rightmost digit wheel is the driving wheel and when it increases above 9 it will reset back to 0 and the next digit one the left will increment by one. The same rule applies for all other digits: when the digit value increases above 9 it resets to 0 and increment the next digit on the left side by one.  

Reference: https://www.shutterstock.com/video/clip-4902245-analog-mechanical-counter-numbers-that-turned-quickly

In mechanical analog counter, all digits have an upper value of 9 and lower value of 0. On the other side, in this algorithm, each digit will have its own upper value which equals the number of levels while the lower value will be 1.

Let's assume that factor A will be represented by the digit wheel on the leftmost side and factor C will be on the rightmost side so that factor C will be the driving wheel.

By knowing the number of levels of each factor we can calculate the total number of combinations N by multiplying all factor levels (N=3*2*4=24 in this example).

Now, we will increment factor C for N times and after each increment we will do a check for all factors (digits), if any factor exceed its upper bound, it will reset to 1 and increment the next digit on the left side by one.

The counting process of the previous example will look like the simple animation shown below:

This algorithm is very efficient and -theoretically- has no limitation on number of factors or number of levels.

The following VBA code can be used to implement the previous algorithm

Sub CreateTreatments() Const NoOfFactors = 3 Dim MaxLevels(NoOfFactors) As Integer Dim FactorLevels(NoOfFactors) As Integer Dim Letters(NoOfFactors) As String ' Define factor letters Letters(0) = " A" Letters(1) = " B" Letters(2) = " C" ' Define number of levels of each factor MaxLevels(0) = 3 MaxLevels(1) = 2 MaxLevels(2) = 4 ' Reset all factors to the minimum level FactorLevels(0) = 1 FactorLevels(1) = 1 FactorLevels(2) = 1 ' Calculate number of runs (combinations) NoOfRuns = 1 For i = 0 To NoOfFactors - 1 NoOfRuns = NoOfRuns * MaxLevels(i) Next i For i = 1 To NoOfRuns ' Create combination text CombinationString = "" For j = NoOfFactors To 1 Step -1 CombinationString = Letters(j - 1) + CStr(FactorLevels(j - 1)) + CombinationString Next j FactorLevels(NoOfFactors - 1) = FactorLevels(NoOfFactors - 1) + 1    'Increment rightmost digit For j = NoOfFactors To 2 Step -1    'For all digits 'If factor level is higher than number of levels, then reset it and increment the left digit If FactorLevels(j - 1) > MaxLevels(j - 1) Then FactorLevels(j - 1) = 1 FactorLevels(j - 2) = FactorLevels(j - 2) + 1 End If Next j Debug.Print CombinationString Next i End Sub

This code will output the following lines in debug window:

A1 B1 C1 A1 B1 C2 A1 B1 C3 A1 B1 C4 A1 B2 C1 A1 B2 C2 A1 B2 C3 A1 B2 C4 A2 B1 C1 A2 B1 C2 A2 B1 C3 A2 B1 C4 A2 B2 C1 A2 B2 C2 A2 B2 C3 A2 B2 C4 A3 B1 C1 A3 B1 C2 A3 B1 C3 A3 B1 C4 A3 B2 C1 A3 B2 C2 A3 B2 C3 A3 B2 C4

Keywords:

Mixed level factorial design algorithm

Excel VBA create combinations

If you have a set of elements let's say {A,B,C,D,E} that you want to create all possible non-repeating combinations for, then here you are two simple and straight forward methods. In some cases you may need to know the all possible combinations rather than counting or calculating their count like in the Design of Experiments DOE.

Unfortunately, in this post I am not going to share a copy-and-paste code, but I will introduce two algorithms to implement them easily using any programming language in any suitable way.

As an example, there are 31 possible combinations for the previous set which is hard to calculate manually and the problem can be more serious with higher number of elements.

In both methods we will use letter-coded elements and will rely on some string processing functions. For example, if you have five billiard balls with five different colors, the following coding may be applied:

Method 1:

In this method we will create (initialize) an empty two-dimensional string matrix having number of rows (M) equals the number of elements (n) and number of columns (N) equals the maximum number of combinations for a given group size. Let's name this matrix "Combinations". This matrix will contain -when it is completely filled- all possible combinations for the given set of elements. Each row of this matrix will contain the combinations of (i) number of elements where (i) is the row number of "Combinations". The main advantage of this matrix structure is to contain data in a compact form instead of having long one-dimensional array. This structure also makes it easy to calculate the combinations manually on paper or in spreadsheet.

For the example shown above, the "Combinations" matrix will look like the following image:

The method pseudo code looks like the following sequence:

Define the elements {"A","B","C","D","E"}

First of all, add all elements to the first row of "Combinations" matrix

For each row (i) of "Combinations" matrix starting from first row to the row before the last row

Set column_counter=0

For each non-empty combination in column (j) in the current row (i) of "Combinations" matrix

For each element in elements

If the element is not contained in the current combination string then

If the element alphabetical order is higher than the alphabetical order of the last element in the combination string (ASCII code of element is larger than the ASCII code of the last element in the combination) then

Create new combination by string concatenation = current combination + element 

Increment column_counter by one 

Add this combination to the first empty cell in the next row of "Combinations" where row number equals i+1 and column number equals column_counter

End If

End If

Next Element

Next Combination

Next Row of "Combinations"


The evolution of "Combinations" matrix during the solution process looks like the following animated .GIF file (frame each 5 seconds)

Method 2:

This method is definitely more simple than the previous method. The core principle of this method is making a good use of binary number representation that it is made of zeros and ones 00101. Each bit in this binary format will represent the existence of the element in this combination; zero (0) means not-exist while one (1) means exist.

To apply this method, we create one-dimensional array for the elements of size (n), one-dimensional array for the binary number representation of size (n) as well, and one dimensional array of size (2^n)-1 for the generated combinations.

The method will go in the following workflow:

For each decimal number starting from 1 to (2^n)-1

Convert the decimal number to n-bit binary format

Multiply each element letter by the corresponding bit value in the binary array and join the results. Note that this is a symbolic multiplication which will return a string where  A * 1 = "A" and A * 0 ="".

The resulting string is a possible combination which will be added to the combinations array.

Next decimal number

Since this method produces combinations in a non-sorted way, alphabetical sorting for combinations may be required for better display of data.

To better understand the method, the following is an animated .GIF file (frame each 5 seconds) which shows how the method works.

Key words:

Unique non-repeating combinations algorithm

Combinations using binary number representation

Combinations programming

Combinations pseudo code C++ and VBA

Efficient combinations algorithm

Compute combinations

How to compute combinations

Excel VBA combinations of cells

Combinations matrix

Excel VBA read LabView date time stamp

To read a binary file you have to know the structure of it. In my case I was trying to read binary file written by LabVIEW-based software which records measurement data. The file represent a one-dimensional array of clusters, the cluster has two elements: TimeDate stamp and double precision number.

According to the following link http://www.ni.com/tutorial/7900/en/

LabVIEW 7.0 or earlier used a 64-bit double (DBL) to represent time, yielding 15 digits of precision. The number of seconds between 1st Jan 1904 (the time stamp Epoch or year zero) to 1st Jan 2000 is 3027456000. Representing this as a DBL would use 10 out of the 15 digits of precision. That leaves a very small resolution space to perform hardware timings using most of the resolution by simply going from 1904 to today.  Representing time as a DBL was not ideal since it did not meet industry requirements.

In MS office the date reference is year 1900, while LabVIEW date reference is year 1904. So, in calculations we will compensate this date reference difference. Number of days difference is 1462 days.

' Function to convert binary to decimal Function BinaryToDecimal(ByVal Binary As String) As Double Dim BinaryNum As Double Dim BitCount As Integer For BitCount = 1 To Len(Binary) BinaryNum = BinaryNum + (CDbl(Mid(Binary, Len(Binary) - BitCount + 1, 1)) * (2 ^ (BitCount - 1))) Next BitCount BinaryToDecimal = BinaryNum End Function

' Function to convert 64-bit binary to double-precision float Function BinaryStringToDouble(ByVal BinaryString As String) As Double Dim i, Sign, Exponent, BitCounter As Integer Dim Fraction, DoubleNo As Double 'Read number sign Sign = (-1) ^ CLng(Mid(BinaryString, 1, 1))     'Most-significant bit ' Read exponent Exponent = BinaryToDecimal(Mid(BinaryString, 2, 11)) ' Read the fraction Fraction = 0 BitCounter = 0 For i = 13 To Len(BinaryString) BitCounter = BitCounter + 1 Fraction = Fraction + (2 ^ (-BitCounter)) * CDbl(Mid(BinaryString, i, 1)) Next i BinaryStringToDouble = Sign * (1 + Fraction) * 2 ^ (Exponent - 1023) End Function

' Function to convert LabView date-time-stamp to string date and time Function DoubleToDateTime(ByVal LVDateTimeStamp As Double) As String    ' input LabVIEW DateTime stamp (64-bit double precision number) Dim RefOffset As Double Dim MSDateTimeStamp As Double Dim MSDate As Double Dim DateString As String Dim DayElapsedTime_sec, Hours, Minutes, Seconds As Double RefOffset = 126316800     'Reference offset in seconds 1462[days]*24[h/day]*60[Min/h]*60[sec/Min] 'Convert it to Microsoft DateTime stamp: number of seconds from 1-Jan-1900 MSDateTimeStamp = LVDateTimeStamp + RefOffset MSDate = Application.WorksheetFunction.Floor(MSDateTimeStamp / 86400, 1)    ' number of days from 1900 ' 86400: number of seconds per day DateString = CStr(CDate(MSDate)) DayElapsedTime_sec = MSDateTimeStamp - MSDate * 86400+7200    'Egypt time = UTC time + 2 hours (7200 sec) Hours = Application.WorksheetFunction.Floor(DayElapsedTime_sec / 3600, 1) Minutes = Application.WorksheetFunction.Floor((DayElapsedTime_sec - Hours* 3600) / 60, 1) Seconds = DayElapsedTime_sec - Hours * 3600 - Minutes * 60 DoubleToDateTime = DateString + " " + CStr(Hours) + ":" + CStr(Minutes) + ":" + CStr(Round(Seconds, 0)) End Function

References:

http://www.binaryconvert.com/

https://en.wikipedia.org/wiki/Double-precision_floating-point_format

Keywords:


VBA Binary file read

Read binary file written by LabVIEW

Parse (parsing) binary file

Read LabVIEW binary file using Excel VBA

Read data in binary file

Read IEEE754 Double precision 64-bit number from binary file

LabVIEW DateTime stamp to binary

Insert transparent picture in Excel dialog sheet

Since there is no straight-forward method to insert a transparent-background picture (like .png) in Excel dialog sheet form; this is how to insert transparent picture in Excel dialog sheet:

Two transparent pictures in dialog form

[1] In a normal worksheet, insert the transparent picture

[2] Copy the picture from the worksheet

[3] Open the dialog sheet

[4] In the ribbon, select "Home" tab

[5] As shown below, click on dropdown arrow of "Paste" button, then select "Paste special"

[6] Select paste as "Picture (PNG)"

Now, it is done ...

Create fancy looking VBA user forms

 

PowerPoint VBA: play sound file programatically

On the run, In this post I will show a very simple way to run a sound file programmatically in PowerPoint VBA through the following steps:

[1] Insert an action button of type "Custom". Place it outside the slide area, so it will not be visible during the presentation.

[2] Right click the action button, a popup menu will appear. Select "Hyperlink..." and a new window named "Action settings" will appear.

[3] In "Action settings" window, check the option "Play sound" and select the .wav sound file you want to play from your computer. In most cases you will not have a .wav file so you have to convert to this format. You can do this online through this link Online converter

[4] Rename your shape from selection pane: select the action button, select "Format" tab in the ribbon, go to "Arrange" group and click on "Selection pane" button. In my case, I named the button "Dummy Button".

[5] Finally, It is the code time. You can play the sound with only a single line of code.

' Place this code in slide code ' Sub to play your pre-loaded sound file programmatically Sub PlayMySound() Shapes("Dummy Button").ActionSettings(1).SoundEffect.Play End Sub

Or, instead of step 3, you can also load the sound file programmatically like the next code:

' Place this code in slide code ' Sub to load and play sound file programmatically Sub LoadPlayMySound() Shapes("Dummy Button").ActionSettings(1).SoundEffect.ImportFromFile ("C:\Users\Shady\Desktop\Finger-snap.wav") Shapes("Dummy Button").ActionSettings(1).SoundEffect.Play End Sub

Done...

Set and get slide Activex controls properties from module

For PowerPoint VBA, there are two ways to control (access) ActiveX controls in certain slide from standard module.

Assume we want to get or set the value of toggle button, then we can do this using one of the following worked examples:

---

Example 1:

In module code, use the following code:

' Declare a public variable for toggle button value in module code Public ToggleBtnValue As Boolean ' Sub-routine to call another sub-routine in slide 1 Sub SetToggleBtnValue() ToggleBtnValue=True Call Slide1.UpdateToggleBtn End Sub

In slide 1 code, use the following code:

' In slide 1 code Sub UpdateToggleBtn()     'Don't use "Private Sub" MyToggleBtn.Value= ToggleBtnValue End Sub

---

Example 2:

In module code, use the following code:

' Read toggle button value in slide number 1 ToggleBtnValue=SlideShowWindows(1).Presentation.Slides(1).Shapes("MyToggleBtn").OLEFormat.Object.value ' Set value of toggle button in slide number 1 SlideShowWindows(1).Presentation.Slides(1).Shapes("MyToggleBtn").OLEFormat.Object.value=False

---

The previous methods can be used to read or write any of the ActiveX control properties like back color, font name, font size, ... etc.

Excel VBA array of collections

'Try this code in module   Dim ArrayOfCollections(100) As Collection   Sub AssignValues()   Dim Collection1 As New Collection    'Declare the first collection   Collection1.Add "Element [1] in collection [1]" Collection1.Add "Element [2] in collection [1]"   ' Set array value to the predefined collection Set ArrayOfCollections(1) = Collection1 'ArrayOfCollections(1) = Collection1     'This code will return error, don't use it   ' Show values of the first array element For Each Element In ArrayOfCollections(1) MsgBox (Element) Next Element   Dim Collection2 As New Collection    'Declare the second collection   Collection2.Add "Element [1] in collection [2]" Collection2.Add "Element [2] in collection [2]" Collection2.Add "Element [3] in collection [2]"   Set ArrayOfCollections(2) = Collection2   ' Show values of second array element For i = 1 To ArrayOfCollections(2).Count MsgBox (ArrayOfCollections(2).Item(i)) Next i   End Sub

Tags:

Access-Excel-Power Point-Word VBA array of collections

Access-Excel-Power Point-Word VBA collection of collections

Access-Excel-Power Point-Word VBA non-uniform array

Access-Excel-Power Point-Word VBA structured list

 

Battleship game

Probability matrix:

Probability matrix is a two-dimensional matrix that represents the probability of each cell to be a member of any ship size. In other words, the probability of any cell is the possible number of times it can be a member of any ship. During the game the probability matrix is updating. The minimum value of probability is zero while the maximum value is 34.

The image below shows the probability matrix at the start of the game before any shooting.

As an example, the following animated gif image shows how the probability of cell A1 is calculated for the probability matrix shown above:

5-cell ship:
Number of probabilities to be placed horizontally in single row=6
Number of probabilities to be placed horizontally=6*10=60
Number of probabilities to be placed vertically=6*10=60
Total number of probabilities=120

4-cell ship:
Number of probabilities to be placed horizontally in single row=7
Number of probabilities to be placed horizontally=6*10=70
Number of probabilities to be placed vertically=6*10=70
Total number of probabilities=140

3-cell ship:
Number of probabilities to be placed horizontally in single row=8
Number of probabilities to be placed horizontally=6*10=80
Number of probabilities to be placed vertically=6*10=80
Total number of probabilities=160

2-cell ship:
Number of probabilities to be placed horizontally in single row=9
Number of probabilities to be placed horizontally=6*10=90
Number of probabilities to be placed vertically=6*10=90
Total number of probabilities=180
Number of combinations of placement of ships in Battleship game= 180*160*160*140*120=77,414,400,000

Efficiency measures:

The minimum number of shots possible to finish battleship game for the luckiest person on earth is 17 (20 if the ship's will not be declared).

The maximum number of shots possible to finish battleship game for the most stupid person on earth is 100.

Small ships consumes more shots to detect. So, the strategy aims to hit the largest ships first.

The maximum number of shots to get the first correct hit should be 20. If your technique reach the first hit after this number of iterations then it is not efficient. You can randomly select one of the predefined cells below to shorten number of iterations. The following is valid if all cells of this technique are shot:

Probability to hit ship
Size in blocks	Probability
5	100%
4	80%
3	60%
2	40%

The following cells pattern can be used to detect 4-blocks ship size with the given probabilities:

Probability to hit ship
Size in blocks	Probability
5	100%
4	100%
3	75%
2	51.11%

The following cells pattern can be used to detect 3-blocks ship size with the following probabilities:

Probability to hit ship
Size in blocks	Probability
5	100%
4	100%
3	100%
2	66.67%

The maximum number of shots for the worst-luck person to finish a game should be 75 shot (using checker board strategy of 50 shots, with no ships placed on borders, no ships adjacent to each other, ships detected 2,3,3,4,5 successively). If number of shots is more than 75, then the technique is stupid. For the versions where the player should also recognize the ship size (ship size is not declared), the number should be 79.


The following is valid if all cells in checker board  strategy are shot:

[1] The probability of first hit of 5-blocks ship is 100%
[2] The probability of first hit of 4-blocks ship is 100%
[3] The probability of first hit of 3-blocks ship is 100%
[4] The probability of first hit of 2-blocks ship is 100%

Cell status:

The possible status for any cell in this game is shown below:

Unknown: a cell that has not been shot yet

Missed: a cell that has been shot, but it is empty

Hit: a cell that has been shot and it is not empty

Sunk: a cell that is a part of sunk ship

Blocked: a cell that lies in the surrounding of sunk ship (this status is used in game version where the ship size is not declared)

Game ID:

The game identifier is a unique identifier that describes the game. It describes where the ships were positioned, their orientations, and the locations of shots. For the purpose of benchmarking of the code, I created this identifier for each game played, so I can build results based on unique games.

The following image shows the maximum possible number of shots (iterations) for each ship size once one block is hit:

If one shot is correct then, set the status to "Partial hit" select the neighboring cell with maximum probability. Shot neighboring cells until the whole ship is sunk.

If two adjacent cells are correct hit, then the orientation of ship is known. Select the neighboring cell with maximum probability on the same row or column

The ship is then sunk when one of the following takes place:

[1] All neighboring cells are missed or blocked

[2] Number of hit cells equals the maximum size of unknown ships

[3] Total probability of neighboring cell equals zero

After ship is sunk, get its size, and remove it from the unknown ship sizes.

After ship is detected (and not declared), mark the cells around as blocked.

After a ship is detected, you can define cells that will detect the maximum available ship size.

Number of vertical probabilities for a given ship size to pass through certain cell=Minimum(No. of allowed cells on top+1, No. of allowed cells on bottom+1, Ship size-No. of hit cells).

Number of horizontal probabilities for a given ship size to pass through certain cell=Minimum(No. of allowed cells on left +1, No. of allowed cells on right +1, Ship size-No. of hit cells).

The total probability for a give ship size=Vertical probabilities+Horizontal probabilities

The total probability for all unknown ship sizes is then the summation of total probabilities for all unknown ship sizes.

Where the acronym "allowed cells" are those cells having the status "Unknown" or "Hit".

Note: the previous formula is applicable where ship size is higher than or equal the number of hit cells.


The following is an animated gif image for one random game using the previously mentioned algorithm:

Key words:

Battleship board game

Battleship game best strategy

Battleship game strategies

Battleship game probability function

Battleship game probability matrix

Battleship game number of combinations

Battleship game solver

Battleship game best efficiency

Battleship game patterns