Saturday, April 1, 2017

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

Sunday, March 5, 2017

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



Friday, March 3, 2017

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.


Monday, February 20, 2017

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

 

Sunday, February 12, 2017

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

Monday, January 30, 2017

Daily demand of ice cubes calculator

Use the following calculator for calculation of daily demand of ice for Arabic Republic of Egypt "mass of water turned into ice per day in kg/day - this is a regional factor - " to be used in equation (46) in IEC 62552-3:2015 Annex F.

Inputs:
Ice is commonly used in summer season only

Family behavior is assumed to be constant

Each family owns only one refrigerating appliance

Ramadan month is a peak season for using ice

No. of summer days:


Ice cubes are used to cool drinks and juices brought from temperature [°C]


All thermal energy is dissipated from liquid drink to ice cubes

Drink to be cooled is thermally insulated (no heat gain)

Heat generated because of stirring is ignored

The average fizzy drink can size equals [mL]:


Average No. of persons of the Egyptian family is:


Each person drinks 1 fizzy drink can per day at home (so ice is needed from the domestic refrigerator)

The comfort drink temperature is [°C]:


Drinks specific heat equals that of water [J/kg.K]:


Specific heat of ice (frozen) [J/kg.K]:


Drinks density equals water density

Water density equals [kg/m3]:


Latent heat of fusion of ice equals [J/kg]:


Used ice temperature [°C]:



Outputs:
Total mass of drinks to be cooled equals [kg]:


Total energy to be removed from hot drinks [J]:


Daily demand of ice (Maximum required mass of ice) [Kg]:


Additional refrigerator energy in [W.h] assuming 100% efficiency (per summer day):


Additional refrigerator energy in [W.h] assuming 100% efficiency (per year):


Thursday, January 26, 2017

Excel VBA hexadecimal color code






Tags:

Excel, Word, PowerPoint VBA hexadecimal color codes

Excel, Word, PowerPoint VBA hexadecimal color format

Excel, Word, PowerPoint VBA RGB to hexadecimal

Excel, Word, PowerPoint VBA Red Green Blue to hexadecimal



Monday, January 23, 2017

Excel VBA color bar


Color scale or color bar is a color gradient color bar. The VBA code in the attached file can be used to create 3-colors color bar and 2-colors color bar. It also can generate horizontal and vertical color bars as well.

In this function you have define the following:

[1] Orientation of the color bar: horizontal "H" or vertical "V"
[2] The parent frame which will be the color bar container
[3] The element size (step) in pixels. The smaller the element size, the smoother gradient is achieved

[4] Red channel value of color 1
[5] Green channel value of color 1
[6] Blue channel value of color 1

[7] Red channel value of color 2
[8] Green channel value of color 2
[9] Blue channel value of color 2

[10] Red channel value of color 3
[11] Green channel value of color 3
[12] Blue channel value of color 3

[13] Color 2 position as a fraction of the color bar size

The color bar is simply created by creating dynamic labels with interpolated color. Each color channel is interpolated using linear interpolation.





The following are some color bars generated using the function


You can download the code from this link


Tags:

Excel, PowerPoint, Word VBA color bar

Excel VBA 2-colors bar

Excel VBA 3-colors bar

Excel VBA color gradient

Excel VBA user form color scale

Color bar interpolation

Color scale interpolation

Color gradient interpolation

Excel VBA rectangle with color gradient

Excel VBA progress bar with color gradient