Cell Border in Excel

Apply Cell Border in Excel:-

Function BorderLine()


'Put Border line in selected cells.

Selection.Borders(xlEdgeTop).LineStyle = xlContinuous

Selection.Borders(xlEdgeTop).Weight = xlThin

Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous

Selection.Borders(xlEdgeBottom).Weight = xlThin

Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic

Selection.Borders(xlEdgeRight).LineStyle = xlContinuous

Selection.Borders(xlEdgeRight).Weight = xlThin

Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic

Selection.Borders(xlInsideVertical).LineStyle = xlContinuous

Selection.Borders(xlInsideVertical).Weight = xlThin

Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic

Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous

Selection.Borders(xlInsideHorizontal).Weight = xlThin

Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic

End Function

Exiting a Loop

In the loop above if you want the loop to stop when it finds the value 99 you can add this line of code within the loop:


If Selection.Value = 99 Then Exit Do

Exit allows you to get out of almost anything like:

Exit Sub
Exit For

Exit Do

Delete the value in the cell

Sub proDelete()

Range("B1").Select

Do Until Selection.Value = "xxx"
If Selection.Value = "" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop
Range("A1").Select
  End Sub

Stop users from scrolling about in Excel

Stop users from scrolling about in Excel:-

Here's a very simple line of code that will stop users from scrolling about a chosen worksheet. To place the code in, right click on the sheet name tab, select "View Code" and paste in this code.


Private Sub Worksheet_Activate()
    Me.ScrollArea = "A1:T50"
    'To set back to normal use:
    Me.ScrollArea = ""
End Sub

Find the content in Excel with Loop

Sub WithLoop()

Dim rCell As Range

For Each rCell In Cells

If rCell.Value = "Find Me" Then
rCell.Activate
Exit For
End If

Next rCell
  End Sub

Find the content in Excel without Loop

Sub NoLoop()


Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

End Sub

Shortcut keys

Ctrl+Z	Undo
Ctrl+C Ctrl+V Ctrl+X	Copy, Paste & Multiple Paste Cut
Ctrl+F, Ctrl+H	Find, Find&Replace
Ctrl+P, Ctrl+S, Ctrl+F4, Ctrl+F4 Alt+F4	Print, Save, Close, Close Excel
Ctrl+Arrow	Move to edge of region
Ctrl+*	Select current region
Ctrl+A	Select all cells
Ctrl+Home Ctrl+End	Select A1, Select last cell in used range
Ctrl+Shift+End	Select from active cell to last cell in used range.
Ctrl+Shift+Home	Select from active cell to A1
Ctrl+Page Down Ctrl+Page Up	Move to the next sheet, Move to the previous sheet
Ctrl+Tab	Move to next open workbook
Ctrl+N	Open new workbook
Shift+F11	Insert new worksheet
Shift+F3	Paste function window
=+FunctionName+Ctrl+A	Insert new function
Alt+F11	Open VBE
Ctrl+Shift+Enter	Array formula
Ctrl+F3, F3	Define name, Paste name
Ctrl+Spacebar Shift+Spacebar	Select columns, Select rows
Ctrl+1, Ctrl+B, Ctrl+U	Format cells, Bold, Underline
Ctrl+; , Ctrl+shift+:	Current date, Current time

Convert PowerPoint Presentation PPT to PDF

How to Convert PowerPoint Presentation PPT to PDF using VBA

PDF is always the universal format for sending the files. With lot of versions of MS Office and other Office suites around.
The following snippet converts the Presentation to a PDF and saves in the same folder of the PPT

ActivePresentation.ExportAsFixedFormat ActivePresentation.Path & "\" & ActivePresentation.Name & ".pdf", ppFixedFormatTypePDF, ppFixedFormatIntentPrint

'Get the name of the currently active file. You'll need this when
'toggelinig between two files, and you want to open the old file
'where the data is assembled

Dim OrginialFile
OriginalFile = Application.ActiveWorkbook.Name

'Open new file
Dim MyFile
MyFile = "C:Maria\Myfolder\Myfile.xls"
Workbooks.Open FileName:=MyFile

'Close a file
Dim MyNewFile

MyNewFile = "MyWonderfulFile.xls"

'Unable ScreenUpdating and DisoplayAlerts, so teh user isn't asked if he want tosave the changes

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Windows(MyNewFile).Close
Application.ScreenUpdating = true
Application.DisplayAlerts = true


'Toggle between open files.

Dim AnotherOpenFile
AnotherOpenFile = "MyWounderfullFile.xls"
Windows(AnotherOpenFile ).Activate

DeletePivotTables

Sub DeletePivotTables()

Dim Pt As PivotTable
Dim Ws As Worksheet

'Loop through worksheets
For Each Ws In ActiveWorkbook.Worksheets
Worksheets(Ws.Name).Select
'Loop through pivot tables

For Each Pt In Ws.PivotTables
'Delete pivot table

Pt.PivotSelect "", xlDataAndLabel, True
Selection.Delete Shift:=xlToLeft
'Exit Sub 'Optional: Get out
Next Pt
Next Ws
End Sub

Check Extension

Sub CheckExtn()

Dim MinExtensionX
Dim Arr() As Variant
Dim lngLoc As Variant

'Retrieve extension of file
MinExtensionX = Mid(MyFile.Name, InStrRev(MyFile.Name, ".") + 1)

Arr = Array("xls", "xlsx") 'define which extensions you want to allow

On Error Resume Next

lngLoc = Application.WorksheetFunction.Match(MinExtensionX, Arr(), 0)

If Not IsEmpty(lngLoc) Then '

'DO STUFF if it's an xls/xlsx file. Otherwise, chose next file in folder

End If

VBA Controls

This section is about communicating with users using controls or a Userform. Learn how to use these controls in Excel 2010, Excel 2007 or Excel 2003. You can directly place controls on a sheet or place them on a Userform.

1 Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.

2 Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.

3 Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.

4 Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.

5 Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.

6 Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.

 

VBA Programming

This section is for users who want to get the most out of Excel VBA. Excel VBA Programming is not difficult, but you do need to know the keywords used in Excel VBA.

1 Variables: Excel VBA uses variables just like any other programming language. Learn how to declare and initialize an excel vba variable of type Integer, String, Double, Boolean and Date.

2 String Manipulation: There are many functions in Excel VBA we can use to manipulate strings. In this chapter you can find a review of the most important functions.

3 Calculate: Calculate with Excel VBA and add, subtract, multiply and divide values just like you are used to doing in Excel.

4 If Then Statement: In many situations you only want Excel VBA to execute certain code lines when a specific condition is met. The If Then statement allows you to do this. Instead of multiple If Then statements, you can use Select Case.

5 Cells: Instead of the more common Range object we could also use Cells. Using Cells is particularly useful when we want to loop through ranges.

6 Loop: Looping is one of the most powerful programming techniques. A loop (or For Next loop) in Excel VBA enables you to loop through a range of data with just a few lines of code.

7 Logical Operators: Do you want to execute code in Excel Visual Basic when more conditions are met? Or just one? Or none? Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.

8 Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet.

In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.

9 Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.

10 Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.

11 Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.

12 Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.

VBA Basics

This section explains the basics of Excel Visual Basic. It is good to know the basic terminology explained in this section  before you start programming in Excel Visual Basic.

1 Macro Security: Setting up your macro security settings correctly is essential to protect yourself against potential viruses. Make sure your macro security settings are set up correctly so no harm can be done to your computer.

2 Visual Basic Editor: Learn how to launch the Visual Basic Editor and get the best configuration of the Project Explorer and the Code Window in your Excel Version. The Visual Basic Editor is the starting point for creating macros in Excel VBA, so it is important to get this configuration right.

3 Macro Comments: Add macro comments to your Excel VBA code and your code will be easier to read as program size increases.

4 MsgBox: The Message Box is a dialog box you can have appear to inform the users of your program.

5 Macro Errors: Dealing with VBA-errors can be quite a challenge. This chapter provides you with a simple tip to deal with these errors.

6 Debug Macros: Before you execute your VBA-code you can first debug your macro. This way most of the errors can be corrected before you execute your code.

7 Objects, Properties and Methods: In this chapter you will learn more about Excel VBA objects. An object has properties and methods. Excel Visual Basic is a semi-object oriented programming language. Learn more about the object hierarchy of Excel Visual Basic.

8 Workbook and Worksheet: In this chapter you will learn more about the Excel VBA Workbook and Excel VBA Worksheet object. You will see that the Worksheet and Workbook object have properties and methods as well, such as the count property which counts the number of active workbooks or worksheets. The Workbook and Worksheet object are commonly used in Excel VBA. They
are very useful when your macro code has to be executed on different workbooks or worksheets.

9 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

Delete Pivot table

Sub DeletePivotTables()
   
Dim Pt As PivotTable
   
Dim Ws As Worksheet
'Loop through worksheets
   
For Each Ws In ActiveWorkbook.Worksheets
   
 Worksheets(Ws.Name).Select
'Loop through pivot tables
   
 For Each Pt In Ws.PivotTables
'Delete pivot table
          
  Pt.PivotSelect "", xlDataAndLabel, True
   
  Selection.Delete Shift:=xlToLeft
           
  'Exit Sub
  'Optional: Get out
 Next Pt
   
Next
Ws End
Sub 
 

How to add Format Conditions using Excel VBA

Sub Format_Condition_Example()

Dim oFc As FormatCondition
Dim oRange As Range

Set oRange = Range("B2:B5")
Set oFc = oRange.FormatConditions.Add(xlCellValue, xlLess, "0.5")
oFc.Interior.ColorIndex = 3
Set oFc = oRange.FormatConditions.Add(xlCellValue, xlBetween, "0.5", "0.80")
oFc.Interior.ColorIndex = 6
Set oFc = oRange.FormatConditions.Add(xlCellValue, xlGreater, "0.80")
oFc.Interior.ColorIndex = 4

End Sub

Time intervals

If you need to calculate the difference between two time values within a certain limit (e.g. when calculating elapsed work time), the worksheet function below can be useful since it makes it simple to perform the time interval calculation.

Function TimeInterval(StartTime As Double, EndTime As Double, _

LowerLimit As Double, UpperLimit As Double) As Double

' returns EndTime-StartTime limited by LowerLimit and UpperLimit

TimeInterval = 0

If StartTime > EndTime Then Exit Function

If StartTime > UpperLimit Then Exit Function

If EndTime < LowerLimit Then Exit Function

If StartTime < LowerLimit Then StartTime = LowerLimit

If EndTime > UpperLimit Then EndTime = UpperLimit

TimeInterval = EndTime - StartTime

End Function

Create an addin in Excel97

Addins in Excel97 are saved differently from the previous versions of Excel:



Start the Visual Basic Editor by pressing Alt+F11.

Select Debug, Compile VBA projectname. In previous versions of Excel the VBA-code was automatically compiled when you saved the addin. This is no longer true, you have to do it yourself before you save the addin.

If you want to lock the project from viewing you can select Tools, Properties for VBA projectname. In this dialog you activate Protection and check the option Lock project for viewing. Fill in a password and click the OK-button.

Activate Excel by pressing Alt+F11.

Select File, Properties..., Summary and fill inn information for the fields Title and Comments. The title will be the name that appears in the Add-Ins dialog (the dialog displaying available add-ins), the comment will be the description that appears when you select the addin in the Add-Ins dialog. Click the OK-button to close the Properties dialog.

Select File, Save as….

Change the option Filetype: to Microsoft Excel addin (*.xla) (the last choice in the dropdown). The add-in must contain at least one worksheet if this option is to be displayed.

Click the Save-button to save the workbook as an addin.

The locking of the project will not take effect until you close and re-open the workbook.

You can convert a workbook to an addin by changing the property IsAddin to True for the ThisWorkbook-object. This must be done from the Visual Basic Editor. When the property is changed you can save the workbook by clicking on the Save-toolbarbutton.

What can a COM Add-In do?

All objectmodels are available for you as a developer.

Supports events, included application level events, and can perform a task according to these events.

You can add buttons to a CommandBar and display different user interfaces.

The position and size of the Office applications main windows can be controlled.

Any Office application can be controlled from another Office application.

You can control other applications that have an objectmodel that is accessible for you as a developer, included other 3rd party applications that licenses VBA version 6.

COM addins?

Earlier versions of Office had 9 different add-in models. All of these models had different attributes for startup, shutdownd, registering, behaviour, language and communication between applications. This is now history, a COM Add-In has full access to the objectmodels in Office.

What is an addin?

An addin is a workbook with custom commands and functions that can be used with Excel. Addin's are used to distribute macros, userdefined functions or custom solutions to other users. If the addin contains VBA code that refers to the workbook running the code you have to use the object ThisWorkbook instead of ActiveWorkbook. An addin will never be the active workbook.

When you save a workbook as an addin, the content is "compiled" and protected so that the source code is (usually) not visible and editable by other people. You can find tools on the Internet that can "crack" this protection from addin's made for Excel 5/95 and Excel97, but ordinary users will usually not be able to see or edit the contents of an addin.

When you create an addin in Excel5/95 you have to save your original workbook if you want to be able to edit the contents of your addin. Excel5/95 can not open and edit the addin, you will have to edit the original workbook and create a new addin every time you need to make a change to the addin.

Excel97 can open and edit addins, so it's not necessary to keep a copy of the original workbook you created the addin from.

It's also possible to password protect the contents of the VBA project in the workbook so that the source code is (usually) not visible and editable by other people.

Addin's can be installed by opening the file as you open an ordinary workbook. You can also install the addin by saving it to the Library folder and installing it from the Addin Manager with the menuchoice Tools, Addins.... When you install an addin the custom functions are available to the user, and any userdefined menus in the addin will also be added to the existing menus in Excel.

Addin's installed by the Addin Manager can be uninstalled by removing the checkmark in the list of installed addin's in the Addin Manager.

Addin's can also be installed and uninstalled by using VBA-code.

Count by color

With the built-in COUNTA()-function it's not possible to count cells in a range depending on each cells background color.



With the custom function below you can count the cells depending on their background color:



Function CountByColor(InputRange As Range, ColorRange as Range) As Long

Dim cl As Range, TempCount As Long, ColorIndex As Integer

ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex

TempCount = 0

For Each cl In InputRange.Cells

If cl.Interior.ColorIndex = ColorIndex Then

TempCount = TempCount + 1

End If

Next cl

Set cl = Nothing

CountByColor = TempCount

End Function

This function is used in the same way as built-in worksheetfunctions. InputRange is the range that the function is going to


count, ColorRange is a reference to a cell containing the background color you wish to count.

E.g. used like this in a worksheet formula: =CountByColor(A1:A100,C1)

WeekStartDate

Function WeekStartDate(intWeek As Integer, intYear As Integer) As Date


Dim FromDate As Date, lngAdd As Long

If intYear < 1 Then intYear = Year(Date) ' the current year

FromDate = DateSerial(intYear, 1, 1)

If Weekday(FromDate, vbMonday) > 4 Then lngAdd = 7

WeekStartDate = FromDate + ((7 * intWeek) - 6) - _

Weekday(FromDate, vbMonday) + lngAdd

End Function

Using Without With-End With Statement

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = xlCenter

Selection.WrapText = True

Selection.Orientation = 0

Selection.ShrinkToFit = False

Selection.MergeCells = False

Avoiding Excel’s Questions

Avoiding Excel’s Questions:-




Application.DisplayAlerts = False



Use this statement to reinstate the confirmation messages:



Application.DisplayAlerts = True

Speeding Up Your Macros:-




Application.ScreenUpdating = False



Use this statement to reinstate the screen refresh display:



Application.ScreenUpdating = True

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

Error Trapping:-

Trapping errors are important as users can do marvelous things to mess up you macros. Here you can use either of these 2 statements.
         - On Error Resume Next   OR
         - On Error Goto ErrorTrap1
            ... more lines of code
            ErrorTrap1:
            ... more code (what to do if there is an error)
The first statement will allow the macro to continue the next line of code upon hitting an error but the second statement will run an alternative code should there be an error.

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. 

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)

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.

Also see Deleting Empty Rows

Application.ScreenUpdating = False

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

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

Current Cell Address:-

To get the current cell address (or current range) to perhaps incorporate into your formula, you could use the following code.




Sub MyAddress()

MsgBox ActiveCell.Address 'absolute cell reference with the pesky $ signs

MsgBox ActiveCell.Address(RowAbsolute:=False, columnAbsolute:=False) 'without the $ signs, you may choose to omit either one Row / Column absolute

End Sub

Deleting Range Names :-

To delete all the range names in your workbook, this macro will do the trick.




Sub DeleteNames()

Dim NameX As Name

For Each NameX In Names

ActiveWorkbook.Names(NameX.Name).Delete

Next NameX

End Sub

Emailing Workbook :-

To email your current workbook the following code.



Sub Email()

ActiveWorkbook.SendMail recipients:="julsn@yahoo.com"

End Sub

Collections & Methods

Collections:-
Some objects are collections of other objects. For example, a workbook is a collection of all the objects it contains (ex. sheets, cells, ranges, charts, VBA modules, etc.). A VBA statement that makes reference to the workbook names “Loan Calculator.xls” would appear like this:

Workbooks(“Loan Calculator.xls”)

Methods:-
A method is an action that can be performed on an object. Excel VBA objects are separated from their methods by a period. For example, if you wanted to save a particular file as part of a VBA program you could include the following sentence in the code:

Workbooks(“Loan Calculator.xls”).Save

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

Close All Files

Sometimes you may want to close all files without saving. Doing it manually is a hassle with the question "Do you wanna save?"

Sub CloseAll()
Application.DisplayAlerts = False
myTotal = Workbooks.Count
For i = 1 To myTotal
ActiveWorkbook.Close
Next i
End Sub

Carriage Return

Sometimes you may want to put a line of text on the next row and not let it continue on the first row. See this example in a message box.

Sub TwoLines()
MsgBox "Line 1" & vbCrLf & "Line 2"
End Sub

Counting Rows & Columns & Sheets

When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information

can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro

will do the trick.

Sub Count()
myCount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox myCount
End Sub

The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.

Sub Count2()
myCount = Application.Sheets.Count
MsgBox myCount
End Sub

Adding Items to a combobox

To add a combobox refer to User Form. To populate a combobox or a listbox is the same. You could add from the code or even

from a range of cells in your spreadsheet. To add from the code, just add this line to your code.

ComboBox1.AddItem "Product A"
ComboBox1.AddItem "Product B"

A simple demonstration of the Selection.Information property

Sub SelectionInformation()
Debug.Print "Selection starts at column: " & _
Selection.Information(wdFirstCharacterColumnNumber)

Debug.Print "Selection starts at line: " & _
Selection.Information(wdFirstCharacterLineNumber)

Debug.Print "The selection starts on page " & _
Selection.Information(wdActiveEndPageNumber) _
& ". There are " & _
Selection.Information(wdNumberOfPagesInDocument) & _
" page(s) in the document."
End Sub

This listing demonstrates basic data retrieval from an Access database.

Sub DatabaseExample()
Dim rst As ADODB.Recordset
Dim sConnection As String
Dim sSQL As String
Dim rg As Range

On Error GoTo ErrHandler

' This is the range that will receive the data.

Set rg = ThisWorkbook.Worksheets(1).Range("a1")

' The database connection string. Double-check the path
' to the Northwind database on your computer.

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\" & _
"OFFICE11\SAMPLES\northwind.mdb"

' The query to execute

sSQL = "SELECT LastName, FirstName, Title FROM employees"

' Create & Open the recordset

Set rst = New ADODB.Recordset
rst.Open sSQL, sConnection

' Copy to the range

rg.CopyFromRecordset rst
rg.CurrentRegion.Columns.AutoFit

' Close the recordset when you're done with it.

rst.Close

ExitPoint:
Set rst = Nothing
Set rg = Nothing
Exit Sub

ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly

' resume at the ExitPoint label to clean up object variables
Resume ExitPoint
End Sub

How to created hyperlinks in VBA:-

Sub Create_HyperLinks()

Dim i1 As Integer
Dim sA, sB As String

For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
If LenB(Trim$(Cells(i1, 3).Value)) <> 0 Then
sA = Trim$(Cells(i1, 1).Value)
sB = Trim$(Cells(i1, 2).Value)
sA = "Compared_" & sA & "_" & sB & ".xls"
Sheets(1).Range("C" & i1).Hyperlinks.Add Cells(i1, 3), "CompareReports\" & sA
End If
Next i1

End Sub

How to convert text to Comments using VBA

Sub Convert_Text_To_Comments()

Dim sText As String ' Comment String
Dim i1 As Long ' Counter
Dim sUser As String ' User Name

sUser = Application.UserName

For i1 = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

sText = ActiveSheet.Cells(i1, 5).Value

'Deletes Existing Comments
Cells(i1, 3).ClearComments

' Creates Comment
Cells(i1, 3).AddComment
Cells(i1, 3).Comment.Text Text:=sUser & Chr(10) & sText

Next i1

End Sub

How to Identify and Tag Numbered Lists using VBA

Sub Tag_Lists()

Dim oBL As ListFormat
Dim oList As List
Dim oLI

For Each oList In ActiveDocument.Lists
If oList.Range.ListFormat.ListType = WdListType.wdListBullet Then
For Each oLI In oList.ListParagraphs
oLI.Range.InsertBefore ""
oLI.Range.InsertAfter ""
Next oLI
oList.Range.InsertBefore ""
oList.Range.InsertAfter ""
End If
Next oList

End Sub

Function Types available in VBA Excel

Nested Formulas

Arithmetic Operations(+,-,/,*)

Text Functions

CHAR
CLEAN
TRIM
LEFT
RIGHT
REPT
REPLACE

Mathematical Functions

ABS
FLOOR
CEILING
ROUND
INT
SIGN
ODD
EVEN
PI
TRUNC

Information Functions

ISBLANK
ISERR
ISERROR
ISEVEN
ISLOGICAL
ISNA
ISREF
ISTEXT

DATE/TIME Functions

SECOND
MINUTE
HOUR
DAY
MONTH
YEAR
TODAY
NOW

Statistical Formulas

SMALL
LARGE
MAX
MIN
MEDIAN
AVERAGE

To Optimize VBA Code for FASTER Macros_4

14. Use 'For Each' than 'Indexed For'

We can avoid using Indexed For when looping through collections. For example, take the code just before this tip. It can be

modified to:
For Each myCell in Range("C5:C10")
mProduct = mProduct * myCell.Value
Next

This is in relation to qualifying object again and again as using "WITH" statements.


15. Use 'Early Binding' rather 'Late Binding'

Declaring and defining the OLE objects in declaration statement is called 'Early Binding' but declaring an object and defining it later is called 'Late Binding'. Always prefer 'Early Binding' over 'Late Binding'. Now for example use:
Dim oXL As Excel.Application
rather than
Dim oXL As Object
Set oXL = CreateObject("Excel.Application")



16. Avoid using Variant

Think about better logic and get rid of them. i.e. do not use Dim i As Variant or Dim mCell As Object. By trying to be specific,we will save a lot of system memory this way, particularly in case of large projects. We may not remember which has been declared variant above and misuse a variable assigning any value to it which will be type-casted without errors. A variant's descriptor is 16 bytes long while double is 8 bytes, long is 4 bytes and integer is 2 bytes. Hence use Variant cautiously. As an example, use:
Dim i As Long rather than Dim i As Variant
Similarly use:
Dim mCell As Range 'or
Dim mSheet As Worksheet
rather than
Dim mCell As Object 'or
Dim mSheet As Object

17. Declare OLE objects directly

Declaring and defining the OLE objects in declaration statement is called 'Early Binding' but declaring an object and defining it later is called 'Late Binding'. Always prefer 'Early Binding' over 'Late Binding'. Now for example use:
Dim oXL As Excel.Application
rather than
Dim oXL As Object
Set oXL = CreateObject("Excel.Application")

To Optimize VBA Code for FASTER Macros_3

9. Reduce the number of lines using colon(:)

Avoid multiple statements especially when they can be clubbed into one line.

For example - See these 2 macros

SLOW MACRO:-

With Selection
.WrapText = True
.ShrinkToFit = False
End With

FAST MACRO:-

With Selection
.WrapText = True: .ShrinkToFit = False
End With

As you can see, you can club multiple statements into one using colon character(:). When you do this with multiple statements, it will decrease the readability but will increase the speed.

10. Prefer constants

But many of us don't follow it. Like
Dim Pi As Double
Pi = 3.14159
instead use
Const Pi As Double
Pi = 3.14159
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.

11. Avoid Unnecessary Copy and Paste

Instead of:-

Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial xlPasteFormulas
Application.CutCopyMode=False
'Clear Clipboard

Use this:-

'Bypass the Clipboard if only formulas are required
Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula
'Same can be done with FormulaR1C1 and Array Formulas.


12. Clear the Clipboard after Paste

Instead of:-

Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial
Application.CutCopyMode = False
'Clear Clipboard

Use this:-

'Bypass the Clipboard
Sheet1.Range("A1:A200").Copy Destination:= Sheet2.Range("B1")


13. Avoid 'Macro Recorder' style code.

The code will look genius and eventually perform like Genius too ! You'll better catch it with example, so use:
[A1].Interior.Color = vbRed
rather than
Range("A1").Select
Selection.Interior.Color = vbRed
Using too many Select and Selection effects the performance drastically.

To Optimize VBA Code for FASTER Macros_2

5. Hide Page breaks

When we run a Microsoft VBA macro in a later version of Microsoft Excel, the macro may take longer to complete than it does in earlier versions of Excel. For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. This problem may occur if the following conditions are true:

* The VBA macro modifies the properties of many rows or columns.
* An operation has been performed that forced Excel to calculate page breaks. Excel calculates page breaks when we perform any of the following operations:

o We display a print preview of your worksheet.
o In Microsoft Office Excel 2003 and in earlier versions of Excel, we click Page Setup on the File menu.
o We modify any of the properties of the PageSetup object in a VBA macro.

* In Excel 2003 and in earlier versions of Excel, we selected the Page breaks check box on the View tab of the Options dialog box.
Solution: is to disable Page breaks using ActiveSheet.DisplayPageBreaks = False

6. Use 'WITH' statement

If we have to access an object's properties and methods in several lines, we

must avoid using object's name or fully qualified object path again and again. It is annoying for VBA processor as it needs

to fully qualify the object each time. (Isn't it annoying for us too when some work or something is told us again and again?

Got it Guys !

SLOW MACRO:-

Sheets(1).Range("A1:E1").Font.Italic = True
Sheets(1).Range("A1:E1").Font.Interior.Color = vbRed
Sheets(1).Range("A1:E1").MergeCells = True

FAST MACRO:-

With Sheets(1).Range("A1:E1")
.Font.Italic = True
.Font.Interior.Color = vbRed
.MergeCells = True
End With

The point here to understand is minimum qualifying of an object by VBA processor. i.e. using minimum dots/periods(.) in the code. This concept tells us to use [A1] rather than Range("A1") and Range("StockRange")(3,4) rather than Range("StockRange").Cells(3,4)


7. Use vbNullString instead of ""


Use vbNullString instead of ""(2 double quotes) : vbNullString is slightly faster than "", since vbNullString is not actually a string, but a constant set to 0 bytes, whereas "" is a string consuming at least 4-6 bytes for just existence.

For example: Instead of strVariable = "", use strVariable = vbNullString.

8. Release memory of Object variables

Whenever we create an object in VBA, we actually create two things -- an object, and a pointer (called an object reference). We might say, "VB does not use pointers", but it is not true. "VB does not let you manipulate pointers" is more precise. Behind the scenes, VB still makes extensive use of pointers. To destroy an object in VB, you set it to Nothing. But wait a minute. If all we ever use are object pointers, how can we set the object itself to Nothing? The answer is: We can't.
When we set a reference to Nothing, something called the garbage collector kicks in. This little piece of software tries to determine whether or not an object should be destroyed. There are many ways to implement a garbage collector, but Visual Basic uses what is called the reference count method.
When VB interprets the last line(where we generally sets our objects to Nothing), it will remove the existing reference. At this point, if the object has no more references, the garbage collector will destroy the object and deallocate all its resources. If any other references point to the same object, the object will not be destroyed.

To Optimize VBA Code for FASTER Macros_1

1. Analyze the Logic :-

Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.

2. Turn off ScreenUpdating

Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.

The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error.

3. Turn off 'Automatic Calculations'

Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated.

You may turn off the automatic calculation using
Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation

Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.

ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.

4. Disable Events

Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell we're changing via code. Hence, turning off events will speed up our VBA code performance.

VBA Controls:-

This section is about communicating with users using controls or a Userform. Learn how to use these controls in Excel 2010,

Excel 2007 or Excel 2003. You can directly place controls on a sheet or place them on a Userform.

1) Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.

2) Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.

3) Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.

4) Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.

5) Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.

6) Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.

VBA Programming 2:-

7) Logical Operators: Do you want to execute code in Excel Visual Basic when more conditions are met? Or just one? Or none?
Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.

8) Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet. In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.

9) Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.

10) Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.

11) Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.

12) Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.

VBA Programming 1:-

This section is for users who want to get the most out of Excel VBA. Excel VBA Programming is not difficult, but you do need to know the keywords used in Excel VBA.

1) Variables: Excel VBA uses variables just like any other programming language. Learn how to declare and initialize an excel vba variable of type Integer, String, Double, Boolean and Date.

2) String Manipulation: There are many functions in Excel VBA we can use to manipulate strings. In this chapter you can find a review of the most important functions.

3) Calculate: Calculate with Excel VBA and add, subtract, multiply and divide values just like you are used to doing in Excel.

4) If Then Statement: In many situations you only want Excel VBA to execute certain code lines when a specific condition is met. The If Then statement allows you to do this. Instead of multiple If Then statements, you can use Select Case.

5) Cells: Instead of the more common Range object we could also use Cells. Using Cells is particularly useful when we want to loop through ranges.

6) Loop: Looping is one of the most powerful programming techniques. A loop (or For Next loop) in Excel VBA enables you to loop through a range of data with just a few lines of code.

VBA Basics 2:-

6) Debug Macros: Before you execute your VBA-code you can first debug your macro. This way most of the errors can be corrected before you execute your code.

7) Objects, Properties and Methods: In this chapter you will learn more about Excel VBA objects. An object has properties and methods. Excel Visual Basic is a semi-object oriented programming language. Learn more about the object hierarchy of Excel Visual Basic.

8) Workbook and Worksheet: In this chapter you will learn more about the Excel VBA Workbook and Excel VBA Worksheet object.

You will see that the Worksheet and Workbook object have properties and methods as well, such as the count property which counts the number of active workbooks or worksheets. The Workbook and Worksheet object are commonly used in Excel VBA. They are very useful when your macro code has to be executed on different workbooks or worksheets.

9) Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

VBA Basics 1:-

This section explains the basics of Excel Visual Basic. It is good to know the basic terminology explained in this section before you start programming in Excel Visual Basic.

1) Macro Security: Setting up your macro security settings correctly is essential to protect yourself against potential viruses. Make sure your macro security settings are set up correctly so no harm can be done to your computer.

2) Visual Basic Editor: Learn how to launch the Visual Basic Editor and get the best configuration of the Project Explorer and the Code Window in your Excel Version. The Visual Basic Editor is the starting point for creating macros in Excel VBA, so it is important to get this configuration right.

3) Macro Comments: Add macro comments to your Excel VBA code and your code will be easier to read as program size increases.

4) MsgBox: The Message Box is a dialog box you can have appear to inform the users of your program.

5) Macro Errors: Dealing with VBA-errors can be quite a challenge. This chapter provides you with a simple tip to deal with these errors.