Sunday, May 20, 2007

AutoFilter Criteria in Excel VBA

Ok, last page we looked at how we can set AutoFilters up within an Excel Workbook. In summary, rather than check if AutoFilters are already applied to a specified Worksheet with an IF Statement, then, if they were on and in use (filtered down) we would turn them off and apply to the needed range. If they weren't on then simply apply them to the needed range.

This however was a lot of superfluous code. The easiest and best way is as shown below;

Sub ApplyAutoFilters()

With ActiveSheet
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
End With

End Sub

In the code above we turn off any existing AutoFilters and apply them to the range A1:D1 of the active worksheet.

FILTERING DOWN TO SHOW 1 MATCHING CRITERIA

Let's now look at how we can apply AutoFilters and show only matching criteria. In the examples below I have used a specified Worksheet by referencing its CodeName . It is also based on the data being in the range A1:D100 with A1:D1 being headings:

Name | Age | Date Joined | Department

Sub FilterTo1Criteria()

With Sheet1
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
.Range("A1:D1").AutoFilter Field:=2, Criteria1:=35
End With

End Sub

In the example below we have filtered our table down to match 1 criteria (Criteria1) on our second heading (Age) to show only those who are 35. If we were to show all those that are 35 or older our Criteria1 would need to be like;

Criteria1:=">=35"

In other words, the criteria and any operators should be passed as text with an equal sign preceding the string.

We can have the filter show only blanks for the specified Field by using: Criteria1:="="

To show all non-blanks we would use: Criteria1:="<>"

XlAutoFilterOperator can be one of these constants

xlAnd default
xlBottom10Items
xlBottom10Percent
xlOr
xlTop10Items
xlTop10Percent

If we wanted to show only those in the Name field whose name Start s with a "D" we would use: Criteria1:="=D*"
To show all names that do not contain a letter "a" we would use: Criteria1:="<>*a*"

In short, the best way to obtain your needed criteria is to simply record a macro filtering your table down and then copy the Criteria1: and the optional Criteria2: code generated.

If desired, for whatever reason, we can have Excel hide the Filter arrow for Field2 (or any Field) by using an additional
argument after Criteria1. That is: ,VisibleDropDown:=False

FILTERING DOWN TO SHOW 2 MATCHING CRITERIA

Let's now expand on the above by filtering down to show 2 criteria.

Sub FilterTo2Criteria()

With Sheet1
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
.Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=35", _
Operator:=xlAnd, Criteria2:="<=45"
End With

End Sub

In the above code we have chosen to show all whose age is between 35 and 45. It's important to note that for the Operator argument we have used xlAnd. If we had used the other choice (XlOr) our results would be that of our original table. That is, all records would show as all people would be either >=35 or <=45.

Sub FilterTo2Fields()
With Sheet1
.AutoFilterMode = False
With .Range("A1:D1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="Dave"
.AutoFilter Field:=4, Criteria1:="Lab"
End With
End With
End Sub

In the code above we have shown all those with the name "Dave" whose department is "Lab". As you can see from the above code,
We can add more fields, but cannot exceed our total column count of headings. In this case we could use Field 1, 2, 3 and/or 4.

FILTERING DOWN TO SHOW WILDCARDS

The wildcard characters we can use in AutoFilter are the asterisk (*) to represent a string of characters and/or the question mark (?) to represent a single character.

However, what if we need to show data that actually houses the * or ? By the way, if at all possible these characters should not be used on their own.

Sub FilterToShowAsterisk()

With Sheet1
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
.Range("A1:D1").AutoFilter Field:=1, Criteria1:="~*"
End With

End Sub

As you can see from the above code, we have told Excel we actually want to filter by the asterisk and not have it seen as a wildcard. The same applies for the question mark. That is: Criteria1:="~?"

VBA & AutoFilters

AutoFilter provides us with a MUCH faster alternative to loops of all kinds.

In the majority of cases it's faster and more efficient to use one of Excel's built in features as apposed to re-inventing the wheel with VBA code. This is why those that have learnt Excel from the ground-up know what native features Excel has to offer. While those only familiar with VB/VBA tend to be the ones who re-invent the wheel.

Ok, the first thing we need to know is how to apply AutoFilter to a range. When we do apply AutoFilter via VBA one SHOULD always turn the off any current filters and remove them completely. Why not check if the AutoFilter is already in place and go from there? The answer is simple, while we can determine if AutoFilter has/is on a specific Worksheet, we cannot guarantee (with extra checking) that it is in use on the range we need! For example, we could use the code below to check.

Sub CheckForAutoFilters()
If ActiveSheet.AutoFilterMode = True Then
MsgBox "They are visible"
Else
MsgBox "They are not visible"
End If
End Sub

From the code above we will know if AutoFilters are visible, but not necessarily in Filter mode (more on that soon). However, we cannot tell if the AutoFilterMode is applied to the correct range. Let's now see how we can determine if the AutoFilters and in use and are being used to filter down.

Sub CheckForAutoFilters2()

With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
MsgBox "They are visible and in use"
ElseIf .AutoFilterMode = True Then
MsgBox "They are visible but not in use"
Else
MsgBox "They are not visible or in use"
End If
End With

End Sub

As you can see, we have used the FilterMode Property of the Worksheet to determine whether the AutoFilters are filtering data down. So, in summary, AutoFilterMode tells us if the AutoFilter arrows are visible and FilterMode tells us if they are in use. However, as I mentioned above this does not tell us which range has had AutoFilter applied. So, with this in mind, we are better off simply removing any existing Autofilter and then applying them to our required range. Here is how, assuming we want A1:D1 to have the AutoFilters.

Sub ApplyAutoFilters()
With ActiveSheet
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
End With
End Sub

Another advantage to applying AutoFilter is this manner is that no error occurs if AutoFilterMode is already false. By the way, we cannot use: AutoFilterMode = True to apply AutoFilters. To apply AutoFilter (at this time with no criteria) we would use Range("A1:D1").AutoFilter. If we are to first check the range that AutoFilter is applied to, we would use code like below;

Sub IsAutoFiltersOnRightRange()
With ActiveSheet
If .AutoFilterMode = True Then
MsgBox .AutoFilter.Range.Address
Else
MsgBox "AutoFilters are not on"
End If
End With
End Sub

In my mind though, this code is superfluous when compared with simply removing and applying AutoFilters. Let's now look at how we apply AutoFilter to a SINGLE cell in a range. If we had our table in the range A1:D200 on the Active sheet and we used the "ApplyAutoFilters" Procedure with .Range("A1").AutoFilter we would likely end up with AutoFilter applied to ALL contiguous headings across row 1. This due to the fact that Excel will detect the contiguous headings across row 1 and assume that we want all headings to have AutoFilters. We can force Excel to not do this by specifying a 2 row single column range. For example;

Sub ApplyAutoFiltersToOneCell()
With ActiveSheet
.AutoFilterMode = False
.Range("A1:A2").AutoFilter
End With
End Sub

Add Worksheets to Excel

Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise), name it "MySheet" and have it become the active sheet, you would use some code like shown below;

Sub AddWorksheet()
Worksheets.Add().Name = "MySheet"
End Sub

If we wanted to add a Worksheet as the last Worksheet and name it "MySheet" we would use;

Sub AddAsLastWorksheet()
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"
End Sub

The Add Method as it applies to the Worksheet Object also has a Before Variant as well as an After Variant. However, we can only nominate a Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet after the current active Sheet.

To add, say, 4 Worksheets we could use the Count Variant;

Sub AddXWorksheets()
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4
End Sub

The only other Variant we can use if desired is the Type Variant. The Type specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet

Security in Excel


Level 1 - To protect your excel files, there are a few steps required to make it more difficult for other users to by pass security. To prevent changes made to the worksheet, you need to protect your worksheet. See protecting sheets. To prevent sheets from being renamed, moved or deleted, protect the workbook. However protection of worksheets and workbook can easily be hacked using macros as shown by an Excel developer. I believe the next level of protection is protecting your macros. To protect your macros, point at your project in the explorer window, right click on it and select VBA project properties, click on the Protection tab, check on Lock Project for Viewing and next key in your password and you're done. Now the project cannot be viewed or amended.

Level 2 - The next step is to force the user to enable your macro when opening your file. The best way is to use a macro to hide the important sheets (see Hiding sheets) when saving your file. Upon opening the file, a macro will be used to unhide these sheets. If the user disables the macros when opening the worksheet, they will not be able to view your worksheet unless they allow the macro to run.

Level 3 - The final step is to put an expiry date for your worksheet or your macro. However this has a draw back as the user may change the system date of the computer to by pass the step. Alternatively you can use a counter (Refer Counter Macro) to allow a fixed number of access to your worksheet or macro. Here you need to save the counter value each time the file or macro is used. Upon reaching the defined limit, disable the macro or disable the access of your worksheet.

The steps mentioned above are not 100% fool proof. But it will keep normal users out but not hackers and crackers. Here I will not supply the code as this can be lengthy and may be difficult to understand but I believe these steps may be useful to some of you out there.

Running A Sub Routine


To run another macro from within a macro you need to use the Call statement.

Sub Macro1()
Msgbox("This is Macro1")
Call Macro2 'This calls for Macro2 to run
End Sub

Saving a file


There are times you may want a macro to save a file automatically after running a macro. The second macro will save the file with a name called "MyFile". You may specify the path if you need to.

Sub Save()
ActiveWorkbook.Save
End Sub
Sub SaveName()
ActiveWorkbook.SaveAs Filename:="C:\MyFile.xls"
End Sub

Resizing a Range


Resizing a range is simple. You can apply this to inserting rows & columns or to expand a selected range. This macro resizes the range to 7 rows by 7 columns.

Sub ResizeRng()
Selection.Resize(7,7).Select
End Sub

Rounding Numbers

Here I will show how to perform different types of rounding. Key in 12345 in any active cell and run the following code.

Sub Round()
ActiveCell = Application.round(ActiveCell, -3)
End Sub

This code round to the nearest 1000 thus giving the value 12000.

ActiveCell = Application.Ceiling(ActiveCell, 1000)
Replace with this line of code and it will round up to the next 1000 ie 13000
ActiveCell = Application.Floor(ActiveCell, 1000)
Replace with this line of code and it will round down to the next 1000 ie 12000

Range Names


Assigning range names to a range of cells.

Sub RngName()
Selection.Name = "myRange"
End Sub

Random numbers


For macros to generate random numbers, the code is takes this format - Int ((upperbound - lowerbound +1) * Rnd + lowerbound). Where the Upperbound is the largest number random number to be generated and Lowerbound is the lowest.

Sub RandomNo()
Randomize
MyNumber = Int((49 - 1 + 1) * Rnd + 1)
MsgBox ("The random number is ") & (MyNumber)
End Sub

In this case the random numbers that will be generate is between 1 and 49.

Protecting your VB code


To protect your VB code from being seen by others, all you need to do is go to the project explorer, point at your project and right click on it. Select VBA project properties, click on the protection tab and check the Lock project for viewing and key your password. That's it.

Sunday, May 13, 2007

Protecting all sheets


To protect all the sheets this macro uses all the methods contained in this page (see counting sheets). The If, Then statement is also used here. This tests for a condition and if the condition is TRUE, then the macro continuous the next line of code. In this case it will END the macro. If the condition is NOT TRUE, then it will go to the following line which in this case is to select the next sheet. You will also notice the For, Next statement is also used. This acts as a counter to tell the macro how many loops to run. In this case if there are 3 sheets, the macro will run 3 times protecting all the 3 sheets.

Sub protectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet
For i = 1 To myCount
ActiveSheet.Protect
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Modeless Forms


Sometimes you want to allow users to be able to switch between your form and your spreadsheet by clicking on either one. All you need to do is set the form property of Show Modal to False or you can try this. However this is only for Excel 2000 & above.

Sub myForm()
UserForm.show vbModeless
End Sub

Moving your cursor


Sometimes you need to move your cursor around your worksheet to re-position it before running the next step of a macro. The movement here uses the row, column position method.

Sub Down()
ActiveCell.Offset(1, 0).Select
End Sub
Sub up()
ActiveCell.Offset(-1, 0).Select
End Sub
Sub Right()
ActiveCell.Offset(0, 1).Select
End Sub
Sub Left()
ActiveCell.Offset(0, -1).Select
End Sub

Protecting / Unprotecting a sheet


The macros below will protect/unprotect the current worksheet with a password.

Sub ProtectSheet()
Dim Password 'This line of code is optional
Password = "1234"
ActiveSheet.Protect Password, True, True, True
End Sub
Sub UnProtectSheet()
Password = "1234"
ActiveSheet.Unprotect Password
End Sub

Lower Case


To change text in a selected range to lower case use this code.

Sub LowerCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = LCase(cell)
End If
Next
End Sub

Message Box


When you need to communicate with users, you can use message boxes. This macro will display a message "This macro is created by Julian". The Message Box appearance can be customised to show whether it is Information, Critical Messages. Here the icon in the message box would be different. The buttons can also be customise to show extra Yes, No, Ok buttons. (Refer to vbYesNo macro). This macro will show you 3 different styles.

Sub MyMessage()
MsgBox "This macro is created by Julian"
MsgBox "The icon is different", vbInformation
MsgBox "The top title is different", vbExclamation, "Julian's Tips"
End Sub

Killing The Current File


Killing the current file you need to change it's status to read only.

Sub Killed()
Application.DisplayAlerts=False
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
ThisWorkbook.Close False
End Sub

Inserting Rows


To insert rows required by a user is easy. Here the input box is used so that a user can define the number of rows required.

Sub InsertRow()
Dim Rng
Rng = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
Selection.EntireRow.Insert
End Sub

Here the macro uses the range technique where a range is first selected and then subsequently rows are inserted.

Joining Text Together


There are times where we import text file into Excel an we get text that are separated. I received an email asking how put these text together. Select across your cells first and run this macro.

Sub JoinText()
myCol = Selection.Columns.Count
For i = 1 To myCol
ActiveCell = ActiveCell.Offset(0, 0) & ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = ""
Next i
End Sub

Killing Files


Killing or deleting files is easy. However the files must not be in used.

Sub Killfile()
Dim MyFile As String 'This line of code is optional
On Error Resume Next 'On hitting errors, code resumes next code
MyFile = "c:\folder\filename.xls"
kill MyFile
End Sub

Wildcards can be use. Replace the file name with * (use with caution!).

Input Box


When you need to get input from users, you can use input boxes. This macro will ask for the user's name and will display a message "Hello" plus the user's name.

Sub GetInput()
Dim MyInput 'This line of code is optional
MyInput = InputBox("Enter your name")
MsgBox ("Hello ") & MyInput
End Sub

Hiding Sheets


To hide your worksheet from users you can use the following code.

Sub HideSheet()
Sheet1.Visible = xlSheetVeryHidden
End Sub

If you hide your sheets this way, users will not be able to unhide them using the menus. Only using VB codes will be able to display the sheets again.

Hiding Excel


You can hide the Excel application with this macro. This disables the user from using the excel menus. Don't forget to set it back to visible.

Sub HideExcel()
Application.Visible = False
End Sub

Going to the 1st Sheet


You can select the first sheet of the workbook without knowing the name of the sheet by referring to it by the index.

Sub FirstSheet()
Sheets(1).Select
End Sub

GoTo Sheet


Sometimes we have many sheets or sheets with long names & we cannot view them all. You can select which sheet to go to by using this macro.

Sub Go2sheet()
myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)
Sheets(mySht).Select
End Sub

Goto (a range)


To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Sales". You may also use an alternative method ie the Range select method. Naming a range in excel is recommended rather than specifying an absolute cell reference.

Sub GoHere()
Application.Goto Reference:="Sales" OR Range("Sales").Select
End Sub

Saturday, May 12, 2007

Functions


Creating function is useful as complicated formulas can be made easier in code than in a spread sheet. Formulas can be protected so that users cannot see or modify them. The example I use will calculate tax using the Select Case Statement. Here's the scenario.
First $2500 is tax free.
Next $2500 is taxable at 5%.
Anything above $5000 is taxable at 10%.
In cell A1 type Income and in cell B1 type in your income in numbers say $20000.
In cell A2 type Tax payable and in cell B2 type =tax(B1).
Put the following code in a module. The tax payable here would be $1625.

Public Function tax(income As Single)
Select Case income
Case Is <= 2500
tax = 0
Case Is <= 5000
tax = (5000 - 2500) * 0.05
'in this case is 125
Case Else
tax = (income - 5000) * 0.1 + 125
End Select
End Function

Flickering Screen


Sometimes when you run a macro, the screen flickers a lot due to the screen updating itself. This slows the macro done especially when the macro has a lot of work to do. You need to include the statement as shown below.

Application.ScreenUpdating = False

You need to set the screen updating back to true at the end of the macro.

Excel Functions


Using Excel functions in VBA is almost the same as using them in a spreadsheet. For example to round an amount to 2 decimal places in a spreadsheet would be;
=round(1.2345,2)
In VBA you would need to use the term Application followed by the function ie;
ActiveCell = Application.round(ActiveCell, 2)

Errors in macros


Ever had a macro running perfectly one day and the next day errors keep on popping up even though you never made changes to that macro? This is no fault of yours. Due to the excel VBA design, macro files get badly fragmented due to heavy editing of macros, insertion of modules & userforms. What you need to do is copy your macros else where, delete the macros, save the file without macros. Open the file again and import the macros and save it once more with the macros. You macros will run properly until it gets fragmented again at a later stage.

Emailing Workbook


To email your current workbook the following code.

Sub Email()
ActiveWorkbook.SendMail recipients:="julsn@yahoo.com"
End Sub

Duplicates (Highlight duplicates in Bold Red)


There are times you need to highlight duplicate data in your worksheet. This macro does the trick.

Sub DupsRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

Duplicates (Highlight duplicates in Bold Red)


There are times you need to highlight duplicate data in your worksheet. This macro does the trick.

Sub DupsRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

Current Cell Position

Sometimes we need to know the current cell position. This would do the trick.

Sub MyPosition()
myRow = ActiveCell.Row
myCol = ActiveCell.Column
Msgbox myRow & "," & myCol
End Sub

Deleting Empty Rows


To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.

Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with 'the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".

Current Cell Content


Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example a message box is displayed. Replace this with a macro should you require another course of action.

Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
MsgBox "Text" 'replace this line with your macro
Else
If ActiveCell = "" Then
MsgBox "Blank cell" 'replace this line with your macro
Else
End If
If ActiveCell.HasFormula Then
MsgBox "formula" 'replace this line with your macro
Else
End If
If IsDate(ActiveCell.Value) = True Then
MsgBox "date" 'replace this line with your macro
Else
End If
End If
End Sub

Current Date


It's a good idea to insert the current date when you save the file so that you can tell if it's the latest version. Of course this is shown under file properties but how many people know where to find it? You could also put the current date in the footer of your print out. It is ideal if the date does not change unless the file is saved. You can use this code. (On the drop down list that says declaration, select before save and you will see the 1st line of code shown below - more details refer to Auto Run macro)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = Now 'Select any cell you want
End Sub