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

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):

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

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



 

Excel VBA ListView control columns auto-fit (auto-resize)

Briefly, the subroutine code at the end of the page can be used to auto-resize (auto-fit) columns for Microsoft List View control used in user-forms in Excel, PowerPoint, Word, and the rest of MS Office tools. It is a simple, easy to understand, and short method to do this job. It can work with different font types and sizes.

The following pictures shows an actual example for list view control before and after the auto-resize subroutine (data source of table contents from:  http://www.7continents5oceans.com/ )



Column width is previously set to 100

Column width is auto-fit

' A procedure to auto-resize column width of list view control Sub AutoResizeListView(MyListView As Variant)   ' Create a dynamic label and set it invisible Set MyLabel = Me.Controls.Add("Forms.Label.1", "Test Label", True)   With MyLabel     .Font.Size = MyListView.Font.Size     .Font.Name= MyListView.Font.Name     .WordWrap = False     .AutoSize = True     .Visible = False End With   ' Auto-resize the first column MyLabel.Caption = MyListView.ColumnHeaders(1).Text MaxColumnWidth = MyLabel.Width   For i = 1 To MyListView.ListItems.Count MyLabel.Caption = MyListView.ListItems(i).Text If MyLabel.Width > MaxColumnWidth Then MaxColumnWidth = MyLabel.Width End If Next i MyListView.ColumnHeaders(1).Width = MaxColumnWidth + 8   ' Auto-resize the rest of columns For i = 1 To MyListView.ColumnHeaders.Count - 1 MyLabel.Caption = MyListView.ColumnHeaders(i + 1).Text MaxColumnWidth = MyLabel.Width For j = 1 To MyListView.ListItems.Count MyLabel.Caption = MyListView.ListItems(j).SubItems(i) If MyLabel.Width > MaxColumnWidth Then MaxColumnWidth = MyLabel.Width End If Next j     'Next cell row in the current column   MyListView.ColumnHeaders(i + 1).Width = MaxColumnWidth + 8    Next i     'Next column   Me.Controls.Remove MyLabel.Name    'Remove the dynamic label End Sub

This sub can be called using the following method inside the user-form code

Call AutoResizeListView(ListViewName)