Excel Programming - Tips and Tricks
Tags : Microsoft Excel, Tips, Tricks, Software, Visual Basic, Calender, Workbook
Microsoft Excel is an amazing software to work with. I am sure the original creators of Excel are awed by the penetration and clout Excel has achieved. All sorts of companies from small grocery shops to multinational banks still use the spreadsheet application to do things from inventory tracking, sales lead tracking to structuring complex derivatives!
The companies that have gained maximum value from Excel are ones who have harnessed the full potential of Excel.
In this article I would like to share a few macros and workarounds in Excel that I have personally found useful. Many of the tricks are a result of trying to solve a customers expressed problem or sometimes, just my own need to do something more efficiently or elegantly.
I am keen to hear from all you Excel studs out there! Share the tips and tricks that you find most cool! Email me at prabhu@imfinity.com
Hide & Seek
Ever wondered how to use the find and replace functionality of Excel from VB? Here’s how it’s done,
The function below returns a range of the first cell that contains the search term: Username.
Set MyRange = oWS.Cells.Find(What:="Username", After:=[A1], LookIn:=xlFormulas, LookAt:=
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Alternatively, you can use
mySheet.Cells.Find(What:="Username", After:=[A1], LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
to activate the cell.
This function replaces all the occurrences of “Text to be changed” with “New Text” in the worksheet mySheet.
mySheet.Cells.Replace What:="Text to be changed", _
Replacement:="New Text", LookAt:=xlPart, MatchCase:=False
You can tweak the options a bit to find more interesting properties of the find and replace commands. A good place to start is
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexvba/html/odc_5709_chap05idx.asp
It’s Make-Up time
After you find the cell that contains the text you want to change or modify, you may want to format the text to appear bold and right justified. Use
myRange.Font.Bold = True
myRange.HorizontalAlignment = xlRight
and there is a host of other functions you can exploit to make the text look the way you want it to.
There is a lot of other things you can do with Range. Below is an example of how to draw a nice bar in a cell
Sub BarExample()
Dim BarRange As Range
Dim myBar As Shape
Set BarRange = ActiveSheet.Cells(1, 1)
With BarRange
Set myBar = ActiveSheet.Shapes.AddShape(msoShapeRectangle, BarRange(1).Left, BarRange(1).
Top, BarRange(1).Width * (20 / 100), BarRange(1).Height)
End With
With myBar
myBar.Fill.ForeColor.SchemeColor = 12
myBar.TextFrame.Characters.Text = 20
myBar.TextFrame.Characters.Font.Size = 6
myBar.TextFrame.Characters.Font.Color = vbWhite
End With
End Sub
The output will look like
Yes, you can display nice progress bars and percentage indicators. It’s really handy and looks very refreshing too.
Why this formula? – Forget ‘N’omore
Ever found a formula in Excel and broken your head trying to remember why it’s there? There is a simple solution to this. Make use of the N function. Basically it works like this: the N-function converts parameters passed to it, to a number. So, all the text passed to the N-function is converted to 0. The next time you add a formula to a cell, instead of saying something like ‘=Sum(A2:B2)’, key in =Sum(A2:B2)+N(“KFC Cheese Fries Expenditure”)
.Get a Date – Just right click away
Recently, one of our business guys asked me for a functionality to be added in Excel. He wanted to right click on any cell and add a date to it by clicking on a calendar. There is a really cool and easy solution to this problem. It goes like this:
1. Open Up Excel
2. Press Alt + F11 to open up the visual basic editor
3. Under Workbook, Add the following code:
Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
Application.CommandBars(”Cell”).Reset ‘was not in 2001-04-13 posting
With Application.CommandBars(”Cell”).Controls
With .Add
.Caption = “Insert Date” ‘Copy Formula / Paste Formula
.OnAction = “Module1.OpenCalendar” ‘in ChipPearson_RClick
.BeginGroup = True
End With
End With
Call Application.OnKey(”+^{C}”, “Module1.OpenCalendar”)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(”Cell”).Controls(”Insert Date”).Delet
e
End Sub
4. On the left pane you will see a list with Microsoft Excel Objects, Forms and Modules. Right Click on the VBAProject and Insert -> UserForm
5. Right Click on the toolbox and click on Additional Controls. In additional Controls select Calendar Control. Once selected it will appear in your toolbox. Drag-n-drop it onto your form. Resize your form to fit the Calendar and add a command button to facilitate Close.
6. Add the following code to your UserForm1’s code window.
Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub
7. Similar to UserForm in Step 4, add a new module and add the following code to the module.
Sub OpenCalendar()
UserForm1.Show
End Sub
8. Now save the workbook. Close and open again. Right click on any cell to see an Insert Date Item in your context menu. Clicking on which pops up a nice calendar from where you can choose the date you want to enter. Alternatively, you can press Ctrl + Shift + C to open the calendar window.
Hope you found the above article to be useful. I will continue with more interesting and complex stuff in the coming issues of ISV Lounge.
There are lots to come. Stay Tuned.
Thanks for the support.
Send your comments to Prabhu.
