Thursday, December 29, 2016

Excel VBA if row or column is inserted or deleted

Problem description:

Since I am addicted to Excel and VBA I was developing a database with advanced features using Excel and VBA. The idea was to save data in the form of three dimensional form and since Excel is working with 2D tables, the solution was using different sheets as 2D layers to form the 3D data storage. In this database there is one sheet visible which is basic layer and the other sheets are hidden. 

One of the challenges I faced is: when I insert or delete row in the basic sheet, the relation between this sheet and the other sheets is corrupted because of data shifting. For this reason, the solution is to automatically detect the insert or delete of row(s) and column(s) in the basic sheet and apply this change in the other sheets. The following schematic shows the concept of 3D data storage in Excel.

How row or column insertion or deletion works?

When row is inserted, the rows below are shifted down. When new column is inserted, the columns on the right (for left-to-right sheets) are shifted right. When row or column is inserted, the new inserted row or column is highlighted (as if it is selected)

How the algorithm works?

The algorithm is massive improvement for an algorithm found in this link

This algorithm depends mainly on what is called "Cell ID" or "Cell identity" which is a hidden non-user-accessible piece of data (string type) that can be set programmatically for any cell. The cell ID follow the following rules:

[1] Cell ID is not copied when cell is copied

[2] Cell ID is cleared when cell content is cleared

[3] Cell ID is cut or moved when cell is cut or moved to another place

Detecting column insert is more easier than column delete

This algorithm has the following advantages:

Works fine when one of this ranges is selected:

- Single cell
- Single entire row
- Multiple entire rows
- Single entire column
- Multiple entire columns
- Vector row
- Vector column
- Last row
- Last column

It returns the following data:

- Number of rows inserted
- Number of columns inserted
- Rows inserted before ....
- Columns inserted before ....

It does not conflict with defined names

It does not conflict with formulas

It can not be fooled by clearing contents or range cut. From one side, pressing "Delete" on certain range will clear contents and IDs as well and this could be interpreted as range delete. From the other side, cell ID is cleared on range cut. Both of these bugs are solved.

It supports different insert and delete patterns:

Successive insert -  Successive delete - Insert then delete - Delete then insert

Compatibility with:

Excel 2003, 2007,2010, 2013, and 2016

It works well either "Insert" or "Delete" commands are called from popup menu or the ribbon

❾  Cell IDs are temporary data and not saved on workbook save or close

Here you are the code to be place in the sheet code:

Dim PreviousSelection As String
Private Sub Worksheet_Change(ByVal Target As Range)
' If entire column(s) is(are) selected
If Target.Rows.Count = ActiveSheet.Rows.Count Then
If Target.Cells(1, 1).Address = Cells(Target.Row, Target.Column + Target.Columns.Count).ID Then    ' Case of inserting entire column
Debug.Print CStr(Target.Columns.Count) + " columns inserted before column " + CStr(Target.Column)
ElseIf Target.Cells(1, 1).ID = "" And Target.Item(1, 1).Address = PreviousSelection And Cells(Rows.Count, Columns.Count).ID <> Cells(Rows.Count, Columns.Count).Address Then ' Case of deleting entire column
Debug.Print "Columns deleted from column " + CStr(Target.Column) + " to column " + CStr(Target.Column + Target.Columns.Count - 1)
End If
End If
' If entire row(s) is(are) selected
If Target.Columns.Count = ActiveSheet.Columns.Count Then
If Target.Cells(1, 1).Address = Cells(Target.Row + Target.Rows.Count, Target.Column).ID Then     ' Case of inserting entire row
Debug.Print CStr(Target.Rows.Count) + " rows inserted before row " + CStr(Target.Row)
ElseIf Target.Cells(1, 1).ID = "" And Target.Item(1, 1).Address = PreviousSelection And Cells(Rows.Count, Columns.Count).ID <> Cells(Rows.Count, Columns.Count).Address Then     ' Case of deleting entire row
Debug.Print "Rows deleted from column " + CStr(Target.Row) + " to column " + CStr(Target.Row + Target.Rows.Count - 1)
End If
End If
' Emulate IDs resetting as in range selection (at the end of Worksheet_Change sub-routine)
Cells(Rows.Count, Columns.Count).ID = Cells(Rows.Count, Columns.Count).Address
Target.Cells(1, 1).ID = Target.Cells(1, 1).Address
On Error Resume Next
Cells(Target.Row, Target.Column + Target.Columns.Count).ID = ""
On Error Resume Next
Cells(Target.Row + Target.Rows.Count, Target.Column).ID = ""
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells(Rows.Count, Columns.Count).ID = Cells(Rows.Count, Columns.Count).Address     'To help not mis-recognize clear cell contents
PreviousSelection = Target.Item(1, 1).Address    'Useful in case of range cut
' A routine for clearing all IDs is needed or clear ID of the previously selected range
Target.Cells(1, 1).ID = Target.Cells(1, 1).Address
On Error Resume Next
Cells(Target.Row, Target.Column + Target.Columns.Count).ID = ""
On Error Resume Next
Cells(Target.Row + Target.Rows.Count, Target.Column).ID = ""
End Sub

Excel VBA pie chart in userform

Used resources: Windows 7 Professional 32 bit - MS Excel 2010 32 bit - VBA 6.0

One day I was playing around with additional controls in a VBA project and I found an ActiveX control for drawing 2D and 3D charts within the user form. The control name was "Graphics server extension" and its source file is Graph32.ocx found in System32 folder in windows directory. This control is developed by "Bits Per Second Ltd" company and it does not have any documentation over the internet at all. I have no idea about the license of this control and how to make it legal.

You can download the library file from the following link. It has the following description:

Description: GRAPH OLE Control DLL
Company: Bits Per Second Ltd
Architecture: 32 bits
File Size: 216096 Byte ( 211.03 KB )

It also have the dependencies shown below:

In this tutorial, I am going to show how simply to programmatically add data to pie chart using Excel VBA.

[1] On screen initialization, clear all data in graph:

'Reset (clear) all data in graph
Graph1.DataReset = graphAllData

[2] Define number of sectors or data points in pie chart:

'Set number of sectors (points) in pie chart
Graph1.NumPoints = 3    ' Three sectors
[3] Add data to pie chart (percentage of each sector). Make sure that the summation of data is 100%:

'Define pie chart values. Method 1: using array
Graph1.GraphData = Array(50, 20, 30)

'Define pie chart values. Method 2: one-by-one

Graph1.Data(1) = 50     ' 50% sector

Graph1.Data(2) = 20     ' 20% sector

Graph1.Data(3) = 30     ' 30% sector
[4] Change color of data

' Define data colors using the default palette
Graph1.Color(1) = 9     ' Blue color index = 9
Graph1.Color(2) = 10    ' Green color index = 10
Graph1.Color(3) = 12    ' Red color index = 12

[5] Add legend data

' Define data legend
Graph1.Legend(1) = "Sector 1"
Graph1.Legend(2) = "Sector 2"
Graph1.Legend(3) = "Sector 3"

[6] Finally, the most important step, is programmatically redraw (update) the graph after adding data to it.

Graph1.DrawMode = graphDraw     ' Update the graph

Enjoy, feel free to ask


Excel VBA pie chart in user form

Excel VBA ActiveX graph

Graphs32.ocx tutorial

Graphs32.ocx documentation

Graphs32.ocx pie chart

Extended graphics server - pie chart

Bits Per Second Ltd - Extended graphics server

Wednesday, December 21, 2016

Copy table from MS word to blogger post

Days ago, I was trying to copy a comparison table from MS Word document to a blogger post and I found that it loses format when pasted especially the widths of columns. After some investigation and trials I found the tricks to save the format and all of them are done with the table properties user form. Here you are the tips:

[1] From table properties, "Table" tab, set the preferred width of the table by checking the preferred width checkbox and writing your value in the adjacent textbox

[2] Select "Column" tab, set the preferred width of each column by checking the preferred width checkbox and set the preferred width. Make sure that the summation of column widths equals the total table width set in step [1] . The following captions show the column width for each column in my table.

[3] Finally, the most important trick: don't use a long continuous text that has length more than cell width in characters (like long web address). You can insert a hyperlink for short text instead of writing the full URL. The following will show what I mean in two possible cases:

Case 1: Writing long normal text in cell

Column 1
This is a normal text paragraph with spaces, it is not continuous so it does not cause any problems and Excel can still keep the same column width of 3 inches.
Copied from MS Word 2013

Case 2: Writing long continuous text in cell (see the effect on column 2 width)

Column 1
Copied from MS Word 2013

Another example below on Case 2 (see the effect on table width and column 2 width)

Column 1
Copied from MS Word 2013


MS Word table format problem

MS Word table column width problem

Blogger table format problem

Blogger can not adjust table or column width

Front load Vs. top load washing machine

A lot of people are always wondering and asking the common question: which is better front load or top load washing machine? The diplomatic answer will always be "It depends, on your needs", but my answer seems to push for the front load based on:

[1] Experience of some friends working in the field of fabric care machines
[2] Studying customer reviews over the internet
[3] Getting direct feedback from people who already have these types of washing machines
[4] and finally, my engineering background

This comparison seems to be different from most comparisons over the internet. The following is a comparison between front load washing machine and top load washing machine:

Point of comparison
Front loading
Top loading
Twin tub converted to one tub
Washing action
Soak, wash, rinse, and spin
Soak, wash, rinse, and spin
Clothes loading and unloading direction
From front
From top
Domestic and commercial
v  Urban
v  Small families
v  Country
v  Large families
Good for moving and cleaning under the washing machine
More compact
Higher height
Drum size (volume)
One of the best uses of this feature is soaking and spinning of large blankets after being manually washed or washed in this washing machine
Dry weight capacity [kg]
Small because of smaller drum size
Large because of larger drum size
Drum axis
Vertical stacking
Can be stacked with other washing machine or tumble drier
Yes (optional)
Built-in configuration
Yes: can be used under worktop in kitchen
Door opening direction
Requires washing machine to be placed in mid side or left side of the wall. If it is placed adjacent to right side of wall it will be difficult to use
Can be placed in any location relative to rear wall
Old models
Matching drier
All-in-one (combination) washer and drier
Yes: some models
No at all
Elegant decoration shape
Some models
Washing capacity [kg]
Same as declared
Almost half of the declared. In most cases the wobbler (motor) stalls at half load
Water consumption per 1 kg of cloths
Less: good where the water is pricy
Overall energy consumption
More (only when hot water settings are used)
Less (only if external water heater is used)
Energy efficiency
Washing efficiency
Typically less
Rinsing efficiency
Typically more
Spinning efficiency
Spin speed
Soaking volume
Program duration
Short, especially when it uses hot water from instant water heater
Gentle wash
Imagine tomatoes are placed in both types!!!
More: Reduces cloths worn.
Program interruption
Imagine a water bottle filled with water and you want to open it while it is horizontal. If you open it water will spill out. For this reason, you can not interrupt the program. Some advanced models allow you to add small cloth pieces during program like AddWash™ technology introduced by Samsung
Detergent precipitation
Washing pillows
Small ones
Large ones
Washing blankets
Baby blankets
Large blankets
Washing shoes
Hand wash
Some models
Detergent type
Automatic (high efficiency)
Automatic or manual
Water supply
One cold water supply
Two: cold and hot (optional). The installation location needs to be prepared. Using external hot water supply will save much time and energy if instant gas water heater is used or in case of central heating systems
Hot water demand
Requires large capacity water heater
Manual water supply
Through the dispenser drawer
Water can be added directly from top lid opening. This is a very useful feature if there is no water pipe network, the city water pressure is so low, or the city water is so hard to be used for washing and a distilled or mineral water will be used instead.
Water quality
Soft water (especially if hot temperature setting is used frequently. Hard water may destroy the electric heater inside after time. This may be overcome by running a no-load hot wash program and using anti-scale agent)
Soft or hard water (for sure hard water will result in low washing efficiency)
Less comfortable as the user has to bend or knee down. Good for short people and non-adults. Good for people with impairment in their legs
More comfortable. Because of deep drum, it is not good for short people as they need to stand in tiptoes or use step stool to reach small items in the bottom
Loading easiness
The user has to prevent loads from falling out while loading
Water level
Manual input by user depending on amount of cloths
Water temperature control
Yes (only when the two water supplies are connected)
Water temperature
Automatic based on user setting
Automatic based on user setting.
Spin speed control
Fuzzy logic
In premium models
In premium models
Control panel location
Always in forehead
Forehead or back
Control panel accessible by baby
It is almost difficult to be accessed by children
Child lock during running
Door open while it is powered off
Yes: in certain cases, if door left open this may be non-safe for babies or pets
Yes: but it is difficult to be accessed by babies
Opening detergent dispenser during program
Possible (unsafe)
Not possible (safe)
Losing small cloths
Small cloths, socks, coins, rings can be lost between internal and external drum.
No losing
Door lock during operation
Yes (mandatory)
No (optional)
Broken door operation
Not possible
Possible (with some simple hack)
Extra features and data
Motor direct drive
Some models
Some models
Transparent door
All models
Some models
Door slamming
Unless the user did this himself
Yes due to free fall
Some models have damper
Counter weight
Damping system
Radial bearing
Radial + axial bearing
Detergent and softener dispenser
All models
Not in old models
Yes in new models
More in old models
Less in new models
Intercooling of drained water
Shipment bolts
Requires a professional for installation
Mold formation
More likeable to take place at door gasket and other locations because of low water consumption, lack of light ingress to the drum, trapped moisture which are the best place for mold to grow up
Less likeable
Bad smell
More likeable. For more info visit this link
Less likeable
Matching drier
Manufacture price
Price (in Egypt)
Pricy for the same performance of equivalent front load because most of these models of this type are  imported from outside
Serviceability (in Egypt)
A lot of professional technicians can service this kind of washing machines
Rare professionals can service this type
Spare parts
Available and cheap
Not available and pricy
Yes: most resold models are old
Yes: most resold models are new