Improving readability of worksheet data in Excel

It can be a big help if your data in Excel is easy to read (avoiding errors in calculations and or data analysis). I know there are more ways than one to achieve that goal, in this article I’ll show you some code that handles the solution without using conditional formatting. It’s a rough and simple solution…

To address the problem visually look at the differences between the images below…

The original layout above.

The new and improved layout below 😉

 

For those of you that are not familiar with VBA / macro’s use the steps below…

  • If you use the latest version of Excel make sure you’ve got the “Developer” tab in Excel
  • Next press the button “Record Macro”
  • Then press the button “Stop Recording”
  • Press ALT + F8
  • Choose “Edit”
  • Copy and paste the code below (or download a text file containing the code here…)
  • Save your Excel !!!
  • Press ALT + F8 and Run the Macro

 

Sub ImproveReadability()

‘ ImproveReadability Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko at winko-erades.nl
‘ Developed: 10-10-2011
‘ Modified: 10-10-2011
‘ Version: 1.0

‘ Description: Improve the readability of your Excel worksheet data by using colors’Declare variables
Dim iRow As Long
Dim iCol As Long
Dim HeaderRow as Integer’Constant values
HeaderRow = InputBox(“Specify the row that contains the header information:”) ‘Create Input Box to ask the user where the header starts
iCol = ActiveSheet.UsedRange.Columns.Count ‘Determine how many columns are in use

‘Change de header lay-out
ActiveSheet.Range(ActiveSheet.Cells(HeaderRow, 1), ActiveSheet.Cells(HeaderRow, iCol)).Select
With Selection.Interior ‘Fill Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
.PatternTintAndShade = 0
End With

With Selection.Font ‘Font Color
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True ‘Font Bold

‘Start highlighting alternate rows
For iRow = HeaderRow + 2 To ActiveSheet.UsedRange.Rows.Count Step 2
ActiveSheet.Range(ActiveSheet.Cells(iRow, 1), ActiveSheet.Cells(iRow, iCol)).Select
With Selection.Interior ‘Fill Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.75
.PatternTintAndShade = 0
End With
Next iRow

End Sub

 

Suggestions for improving this article are welcome, please let me know and drop me a line.

Prevent RSI and automate mouse clicks by using excel

Sometimes one has to do a lot of mouse clicking… By repetitive clicking and small movements of your hands and other joints you could end up with some nasty RSI (Repetitive Strain Injury) and that ain’t worth it!!! So beat RSI (or your enemy while gaming) by using VBA and Excel to automate some excessive mouse clicks!

As mentioned in the intro, this article is about automating mouse clicks in Excel. Therefor I wrote a macro in Excel as you can see in the image below…

Look at the three big buttons that enable you to Left Click, Middle Click or Right Click.
Also noteworthy are the adjustable “Start Delay” and “Click Interval”.

 

For those of you that are not familiar with VBA / macro’s use the steps below…

  • If you use the latest version of Excel make sure you’ve got the “Developer” tab in Excel
  • Next press the button “Record Macro”
  • Then press the button “Stop Recording”
  • Press ALT + F8
  • Choose “Edit”
  • Copy and paste the code below (or download a text file containing the code here…)
  • Save your Excel !!!
  • Press ALT + F8 and Run the Macro

Download the Excelsheet (as can be seen above) here

Private Declare Sub mouse_event Lib “user32” (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Const MOUSEEVENTF_LEFTDOWN = &H2
Const MOUSEEVENTF_LEFTUP = &H4
Const MOUSEEVENTF_MIDDLEDOWN = &H20
Const MOUSEEVENTF_MIDDLEUP = &H40
Const MOUSEEVENTF_MOVE = &H1
Const MOUSEEVENTF_ABSOLUTE = &H8000
Const MOUSEEVENTF_RIGHTDOWN = &H8
Const MOUSEEVENTF_RIGHTUP = &H10

 

Sub AutomaticMouseClickLeft()
‘ Automatic Mouse Click Left Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko@winko-erades.nl
‘ Developed: 27-03-2012
‘ Modified: 28-03-2012
‘ Version: 1.0

‘ Description: Automatic Mouse Click Left

AantalKlikken = InputBox(“Enter the number of Clicks to be executed :”, “KlikkerDieKlikBox”) ‘Ask the number of Clicks to be executedApplication.Wait (Now + Sheets(“Sheet1”).Cells(5, 2)) ‘Get the delay from the sheet before starting to click – hh:mm:ss
For Klik = 1 To AantalKlikken ‘Start counting the Clicks
‘Do mouse click on cursor position
mouse_event MOUSEEVENTF_LEFTDOWN Or MOUSEEVENTF_LEFTUP, 0&, 0&, cButt, dwEI
DoEvents
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 3)) ‘Delay interval between Clicks – hh:mm:ss
Next Klik ‘Next Click
‘End
End Sub

 

Sub AutomaticMouseMiddleClick()
‘ Automatic Mouse Middle Click Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko@winko-erades.nl
‘ Developed: 27-03-2012
‘ Modified: 28-03-2012
‘ Version: 1.0

‘ Description: Automatic Mouse Click Middle

AantalKlikken = InputBox(“Enter the number of Clicks to be executed :”, “KlikkerDieKlikBox”) ‘Ask the number of Clicks to be executed
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 2)) ‘Get the delay from the sheet before starting to click – hh:mm:ss
For Klik = 1 To AantalKlikken ‘Start counting the Clicks
‘Do mouse click on cursor position
mouse_event MOUSEEVENTF_MIDDLEDOWN Or MOUSEEVENTF_MIDDLEUP, 0&, 0&, cButt, dwEI
DoEvents
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 3)) ‘Delay interval between Clicks – hh:mm:ss
Next Klik ‘Next Click
‘End
End Sub

 

 

Sub AutomaticMouseClickRight()
‘ Automatic Mouse Click Right Macro

‘ Developer: Winko Erades van den Berg
‘ E-mail : winko@winko-erades.nl
‘ Developed: 27-03-2012
‘ Modified: 28-03-2012
‘ Version: 1.0

‘ Description: Automatic Mouse Click Right

AantalKlikken = InputBox(“Enter the number of Clicks to be executed :”, “KlikkerDieKlikBox”) ‘Ask the number of Clicks to be executed
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 2)) ‘Get the delay from the sheet before starting to click – hh:mm:ss
For Klik = 1 To AantalKlikken ‘Start counting the Clicks
‘Do mouse click on cursor position
mouse_event MOUSEEVENTF_RIGHTDOWN Or MOUSEEVENTF_RIGHTUP, 0&, 0&, cButt, dwEI
DoEvents
Application.Wait (Now + Sheets(“Sheet1”).Cells(5, 3)) ‘Delay interval between Clicks – hh:mm:ss
Next Klik ‘Next Click
‘End
End Sub

 

Suggestions for improving this article are welcome, please let me know and drop me a line.