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.

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

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


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


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


Thursday, January 19, 2017

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

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)


Tuesday, January 17, 2017

IEC 62552-3:2015 steady state SS1 iteration

A visualization for the iteration scheme to find steady state SS1 of refrigerator in energy consumption test according to IEC 62552-3:2015.


Friday, January 13, 2017

Excel VBA command button group

In this post I am going to explain an nice and handy trick to be used with Excel VBA. If you want to create a matrix of command buttons that each button will return a certain action when clicked, then this will be so useful for you. With this trick you can create calendars, calculators, and even non-editable data tables.

The trick requires the following to be done on MultiPage control:

[1] Setting tab style as button

[2] Setting MultiRow option to "True": so if the MultiPage control width is not enough to contain all tabs in single row, extra tabs will be placed to additional rows.

[3] Setting tab orientation from top, so tabs will populate from top-left to bottom-right

[4] Using fixed tab width and height

[5] Finally, setting the width and height of the Multipage control to values that just show the tabs without showing pages. You can use the following formulas to calculate the required width and height.

MultiPage control width=(Tab width+2) x No. of columns

MultiPage control height=(Tab height+2) x No. of rows

Where the value [2] is the preceding padding before each tab.

All the previous actions can be done using the properties window of the control as shown below.
Now, if you want to add buttons to MultiPage control dynamically you can follow the following simple code:

' Populate buttons on user-form show up

Private Sub UserForm_Initialize()
MultiPage1.Pages.Clear     'Clear all existing pages in the Multipage control

' Add 49 tabs (buttons)
For i = 0 To 48     '0 is the index of first page
MultiPage1.Pages(i).Caption = CStr(i)
Next i
End Sub

' Setting the action
Private Sub MultiPage1_Change()
NoteLabel.Caption = "The pressed button is " + CStr(MultiPage1.Value)
End Sub

To make it more spicy, the following picture shows an application for this trick to create a calendar control. You can download this demo from this link


Easy way to create group of command buttons

The easiest/simplest way/method to create group of command buttons

Excel VBA the easiest way to create popup menu

Excel VBA create and populate popup menu

Excel VBA popup menu in userform

Excel VBA popup menu in worksheet

Excel VBA TabStrip trick

Excel VBA MultiPage trick

Excel VBA calendar userform

Excel VBA calendar in worksheet

Monday, January 9, 2017

Egypt regional humidity probability - Annex F - IEC 62552

The following data are based on weather data log of weather station of Cairo International Airport CAI HECA weather station number 623660 found in this link

The following table is approximate humidity probability Ri for Egypt during year 2016 to be used in Annex "F" in IEC 62552-3:2015. You can download the climate data of Cairo 2016 from this link


[1] Measured temperatures are indoor

[2] Cairo weather is considered as Egypt weather

[3] 2017 weather is expected to be the same as 2016 weather

Year 2016

Relative humidity interval [%]

Nominal RH [%]

Regional humidity probability [%] at the following nominal dry bulb temperatures [°C]

16 °C

22 °C

32 °C



















































Total probability




Saturday, January 7, 2017

Top things to be done using access control units

In this post we introduce the  top things that can be done using access control units in work environment either they are used for check-in and check-out or for opening any door type for restricted area. These functions can be done only using access control units having the ability to identify or recognize the identity of the person through fingerprint, RFID tags or cards, or face detection and connected to a server that logs and takes actions.

[1] Top of the list, controlling the salary overtime and deductions. This is the traditional purpose for using check-in and check-out devices.

[2] Green spirit: Switch off lights, air conditioners, PCs, water heaters, water dispensers, and other devices when all employee in certain location had checked out. Also, it will turn on lights and devices once one employee had checked in.

[3] Project management: Update project schedules, Gantt charts, and projects critical paths in project management software based on the availability of human power.

[4] Extra security: Turn on door security alarm in restricted locations when all people had checked out

[5] Trace responsibility: For restricted access areas, and in the case of non-authorized person entering, if door is left opened, the responsible person is probably the last one opened the door.

[6] Meeting attendees management: Update MS Outlook or mail calendar for better management of meetings and conferences. If the person didn't check-in in the location, then it will be marked as not available in Outlook. 

[7] Meeting rooms management: Turn off the availability of meeting rooms if they have admins and all of the admins didn't check-in in the plant

[8] Reminders: Create on-arrival or on-departure reminder note for employee when he(she) checks in or out.

[9] Service management: In locations or plants preparing food for the employees, access control unit can be used for counting number of employees checked-in so a proper amount of food can be prepared so no food will be wasted.

[10] Emergency: Send automatic notification for emergency evacuation to mobile phones of all employee had checked-in in case of emergency (this can be a backup solution if emergency alarms didn't work).

[11] Data protection: Extra protection for PCs even if someone had entered the correct password of the PC. If this PC belongs to specific person, then it will not be unlocked until he(she) check-in.

[12] Safety check: Automatic safety check in case of bad weather conditions and explosions.

[13] Update mail address book: Alert IT to remove email of person who didn't check in for long time. This may be an indication that he/she left the company.


Top uses of door access control unit

Top benefits of biometric access control device

Top applications of fingerprint access control unit

Non conventional uses of access control unit