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 http://stackoverflow.com/questions/35978015/determining-when-a-row-cell-is-inserted-or-deleted-in-excel-vba

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

Tags:

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

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	Using a hyperlink in cell http://eng-shady-mohsen.blogspot.com.eg/2016/12/front-load-vs-top-load-washing-machine.html	Copied from MS Word 2013

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

Column 1	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	Copied from MS Word 2013

Tags:

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

Image courtesy: http://designlike.com/6-major-differences-between-front-loading-and-top-loading-washing-machine/

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
Concept		Twin tub converted to one tub
Washing action	Tumbling	Wobbling
Functions	Soak, wash, rinse, and spin	Soak, wash, rinse, and spin
Clothes loading and unloading direction	From front	From top
Market	Domestic and commercial	Domestic
Customer	v  Urban v  Small families	v  Country v  Large families
Weight	Large	Smaller Good for moving and cleaning under the washing machine
Geometry
Size	More compact	Higher height
Drum size (volume)	Small	Large 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	Horizontal	Vertical
Vertical stacking	Yes Can be stacked with other washing machine or tumble drier	No
Pedestal	Yes (optional)	No
Built-in configuration	Yes: can be used under worktop in kitchen	No
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
Agitator	No	Old models
Matching drier	Yes	Yes
All-in-one (combination) washer and drier	Yes: some models	No at all
Elegant decoration shape	Yes	Some models
Performance
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	More
Overall energy consumption	More (only when hot water settings are used)	Less (only if external water heater is used)
Energy efficiency	More	Less
Washing efficiency	More	Typically less
Rinsing efficiency	Less	Typically more
Spinning efficiency	More	Less
Spin speed	More	Less
Soaking	Yes	Yes
Soaking volume	Small	Large
Program duration	Long	Short, especially when it uses hot water from instant water heater
Gentle wash Imagine tomatoes are placed in both types!!!	More: Reduces cloths worn.	Less
Program interruption	No 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	Yes
Noise	High	Less
Detergent precipitation	Less	More
Washing pillows	Small ones	Large ones
Washing blankets	Baby blankets	Large blankets
Washing shoes	Yes	Yes
Hand wash	No	Some models
Requirements
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	Zero	High Requires large capacity water heater
Manual water supply	Yes Through the dispenser drawer	Yes 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)
Ergonomics
Comfort	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	Less The user has to prevent loads from falling out while loading	More
Control
Electronic	Yes	Yes
Water level	Automatic	Manual input by user depending on amount of cloths
Water temperature control	Yes	Yes (only when the two water supplies are connected)
Water temperature	Automatic based on user setting	Automatic based on user setting.
Spin speed control	Yes	Yes
Fuzzy logic	In premium models	In premium models
Control panel location	Always in forehead	Forehead or back
Safety
Control panel accessible by baby	Yes	It is almost difficult to be accessed by children
Child lock during running	Yes	Yes
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)
Reliability
Broken door operation	Not possible	Possible (with some simple hack)
Extra features and data
Monitoring	Remote	Local
Privacy	No	Yes
Motor direct drive	Some models	Some models
Transparent door	All models	Some models
Door slamming	No Unless the user did this himself	Yes due to free fall Some models have damper
Counter weight	Yes	No
Damping system	Radial	Axial
Bearing	Radial bearing	Radial + axial bearing
Detergent and softener dispenser	All models	Not in old models Yes in new models
Wrinkling	Less	More in old models Less in new models
Intercooling of drained water	Yes	No
Shipment bolts	Yes Requires a professional for installation	No
Cleanness
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	Yes	Yes
Financial
Manufacture price	High	Lower
Price (in Egypt)	Cheap	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
Reselling	Yes: most resold models are old	Yes: most resold models are new