Saturday, September 29, 2012

My Excel macro selection

Some easy to use Excel macro’s. All explained below are available in one Excel Macro Example.

All macro’s are combined in one example Excel sheet template to test the usage of it.

Password Generation

Function RndPassword(vLength)
'This function will generate a random strong password of variable
'This script is provided under the Creative Commons license located
'at . It may not
'be used for commercial purposes with out the expressed written consent

For x = 1 To vLength
vChar = Int(89 * Rnd) + 33
If vChar = 34 Then 'this is quote character, replace it with a single quote
vChar = 39
End If
RndPassword = RndPassword & Chr(vChar)
End Function

An example usage of this Password generation function: I have a list with on each row a button to generate a password in the cell next to the button. Each button is linked to the same macro (for easy copy paste of the row). The cell to fill with the new password is automatically detected based on the cell in which the button is drawn. Before setting the new generated password, a warning is shown in a message box.


Sub Process_GenNewPassword()
Dim LRange As String
Dim RowOffset As Integer
Dim ColumnOffset As Integer
Dim newGenPassword As String
'Find cell that button resides in
LName = Application.Caller
Set bBox = ActiveSheet.Buttons(LName)
LRange = bBox.TopLeftCell.Address
RowOffset = 0 ' relative location of the row in which to work, same row used
FirstNameColumnOffset = -5 ' relative location of firstname column in row to show in warning message box
LastNameColumnOffset = -4 ' relative location of lastname column in row to show in warning message box
PasswordColumnOffset = 2 ' relative location of column in row where the generated password should be set
sResult = MsgBox("The password for user '" & Range(LRange).Offset(RowOffset, FirstNameColumnOffset).Value & " " & Range(LRange).Offset(RowOffset, LastNameColumnOffset).Value & "' will be replaced with a new random password!", vbExclamation + vbOKCancel, "New password generation")
If (sResult <> 1) Then
Exit Sub
End If
newGenPassword = RndPassword(10)
Range(LRange).Offset(RowOffset, PasswordColumnOffset).Value = newGenPassword
End Sub


Checkbox LinkedCell

Whenever you need many checkboxes in your sheet, you'll probably need each checkbox to be linked to the cell it is residing on.

When copy-pasting rows or columns, the checkboxes will be copied as well, but they will still be linked to the same cell as the checkbox you started to copy-paste. The little macro shown below, will update each Checkbox on the active sheet and set it's linked cell to the cell on which the Checkbox is drawn.

Sub setCheckBoxLinkedCell()
'Loop through all Checkboxes in the active sheet
'Set for each Checkbox it's "LinkedCell" value to the cell on which the Checkbox is drawn
Application.ScreenUpdating = False
' turns off screen updating
Application.DisplayStatusBar = True
' makes sure that the statusbar is visible
Application.StatusBar = "Updating linked cells of all Checkboxes in this sheet."
For Each chk In ActiveSheet.Checkboxes
LRow = ActiveSheet.Checkboxes(chk.Name).TopLeftCell.Row
LColumn = ActiveSheet.Checkboxes(chk.Name).TopLeftCell.Column
cA1 = Application.ConvertFormula("R" & LRow & "C" & LColumn, xlR1C1, xlA1)

With Selection
.Value = xlOff
.LinkedCell = cA1
.Display3DShading = False
End With
Next chk
Application.StatusBar = "Linked cells of all Checkboxes in this sheet are updated."
Application.ScreenUpdating = True
End Sub

Save date on Checkbox check

The macro below will set the date in the cell next to the Checkbox whenever the checkbox is checked. All checkboxes are linked to the same macro, for easy copy-pasting. The cell in which to save the date will be detected based on the checkbox location.


Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LRow As Integer
Dim LRange As String

LName = Application.Caller
Set cBox = ActiveSheet.Checkboxes(LName)

'Find address that checkbox resides in
LRange = cBox.TopLeftCell.Address
DateRowOffset = 0 ' row offset (relative to the checkbox location) in which the date should be set
DateColumnOffset = 1 ' column offset (relative to the checkbox location) in which the date should be set

'Change date if checkbox is checked
If cBox.Value > 0 Then
Range(LRange).Offset(DateRowOffset, DateColumnOffset).Value = Date

'Clear date if checkbox is unchecked
Range(LRange).Offset(DateRowOffset, DateColumnOffset).Value = Null
End If
End Sub

Create Folder Structure

I copied my previously explained 'Create Folder Structure' in this Excel Macros Example. For all details, see my previous blog post. An import of an existing system folder structure is now added in there as well.


Run macro upon opening a workbook

To run a macro whenever the workbook Excel sheet is opened, the Sub Workbook_Open in 'ThisWorkbook' can be used. This is needed if you want to link some keyboard keys to specific macro's.

Link keys to macro

To link a keyboard key to a specific macro action, the Application.OnKey "..." can be used in the auto startup macro. The keys must be unlinked before closing the workbook. I used this functionality in my Exam Point Counter workbook, described in this previous blog post. In 'ThisWorkbook':

'startup macro
Private Sub Workbook_Open()
Application.OnKey "t", "tKeyPressed"
Application.OnKey "e", "eKeyPressed"
Application.OnKey "s", "sKeyPressed"
Application.OnKey "t", "tKeyPressed"
End Sub
Private Sub Workbook_BeforeClose()
Application.OnKey "t"
Application.OnKey "e"
Application.OnKey "s"
Application.OnKey "t"
End Sub

In any module, for example KeyboardAction:

Sub tKeyPressed()
Application.DisplayStatusBar = True
Application.StatusBar = "t key linked to special action in KeyboardAction module"
End Sub
Sub eKeyPressed()
Application.DisplayStatusBar = True
Application.StatusBar = "e key linked to special action in KeyboardAction module"
End Sub
Sub sKeyPressed()
Application.DisplayStatusBar = True
Application.StatusBar = "s key linked to special action in KeyboardAction module"
End Sub

Search Lookup

The search lookup functionality has also been described in details in this previous blog post. The macro is included in this Excel Macro Example as well.

The formula for E9 below looks like:



Open Save

A button with linked macro to navigate to a folder is shown in the module 'OpenSave'.

A basic example of the resource exporter I showed in a previous post, is included as well. It will export the example data set into a text file, and while doing so, all special characters will be converted (eg &#233). For rows in the list of AlternativeEncoding, an alternative encoding for special characters will be used (eg \u00E9). To make the sheet readable, the special characters can be converted back into readable special characters. The conversion is based on a hex2ascii converter, unicode encoding, html encoding and decoding.

When you would reuse this code, please note, in order to support FileSystemObject, you’ll need to add reference to Microsoft scripting runtime in your Excel workbook VBA. In order to do so, open the Visual Basic environment in Excel (ALT+F11) > Menu ‘Tools’ > ‘Reference’ > Enable ‘Microsoft Scripting Runtime’. If this isn’t done in the workbook, the error ‘User defined type not defined’ will appear when writing an export file on ‘Dim fso As New FileSystemObject’.


When the special characters are encoded using the macro, they will look like this:


PhoneBook vCard export

A sheet in which all phonebook data can be added (can be exported from any other application) is now available. Once all contact information is set, it can be exported into a vCard format which is supported by many address and contact management applications. More details can be found in this specific blog post.


All code is available in the Excel Macro Example.

Monday, August 27, 2012

My Android selection

I recently moved to Android. Starting with a cheap but excellent Archos A80s G9 Turbo 16GB TI OMAP4430 dual core 1,2GHz with 3G usb stick. It was one of the first tablets to get official ICS support and it has a very good XDA support for rooting and custom roms. Last week I replaced my old Samsung B7610 Pro Windows Mobile 6.5 phone for a Sony Xperia Sola MT27i Android 2.3.7 rooted and custom rom SSpeed. The ICS update should become available soon.

A list of my favorite applications I found very useful:

  • TeamViewer: free easy VNC with no firewall configuration
  • Google Reader: RSS reader
  • AirDroid: manage your Android device via browser in local network
  • Roboform: manage all your passwords securely and automated login/saving. Synchronized in cloud for full desktop synchronization and making sure you’ve your passwords everywhere in sync.
  • PhoneAlarm: never miss a new reminder/sms/mail/call by repeating the notification
  • IM+: connect all chat accounts at once (Google Talk, Facebook, MSN, Skype, Yahoo, etc)
  • TriCount: easy track and calculate group expenses
  • GoldenDict: offline dictionary compatible with the enormous Babylon dictionaries and custom Babylon dicts
  • myShopi: grocery list to remember what to buy in which shop, integrated list of shops and their opening hours, coupons and recipes
  • Smart Tools: use compass, gyro, accelerometer, flashlight, camera to measure etc
  • Opera mobile: browser
  • Business Calendar: many calendar look and feel options
  • Weather Service Pro: weather info and widget, still able to show cached info while offline (unlike many others)
  • Shazam Encore: record short music to get full info on artist, offline record possible to get full info later while online
  • TVGids 2.0: TV schedule overview compatible with Belgian tv
  • Mixologist: good looking bar and mix info
  • Knots3D: info on how to tie knots, with full usage info and 3D details
  • Car Locator: locate your car, automatically save car location when disconnection Bluetooth car headset
  • Sygic drive: offline GSP navigation, download maps
  • FolderSync: file manager and synchronization of all cloud storage service (Amazon S3,, dropbox, ftp, google docs, google drive, netdocuments, sftp, SkyDrive, SMB, SugarSync, Ubuntu One, WebDAV)
  • FileExpert: filemanager with integrated zip and rar archive support
  • AutomateIt: automate tasks, alternative for Tasker
  • Find My Phone: retrieve phone GPS location by sending special SMSs with password or make your phone ring on max volume level by sending specific sms (even when phone is muted and offline)
  • Ultra Keyboard: keyboard with many customization options and multi clipboard
  • Camera Zoom FX: camera app with special effects and optimized quality
  • Gallery Excluder: exclude some folder to be shown in Android default image gallery
  • OneNote: MS Office OneNote synchronized notes
  • Evernote: notes saved in cloud
  • Call Log Calendar: keep track of each call and SMSs in specific calendars
  • Convertor Pro: convert any kind of sizes, currencies, dates, time, etc
  • Mantano Reader: ebook and pdf reader with good options and compatible with most formats
  • Google translate: translate text
  • Kingsoft Office: excellent MS Office document viewer and editor , free!
  • NeverLate: traffic information

Besides those, some other standard well know applications known by everyone (eg facebook, gmail, whatsapp, youtube, etc).