Skip to main content

Loops

There are many varieties of these, but they are all basically the same in that they will repeat a line, or multiple lines, of code a set number times, or until a condition becomes True or False.

 

Excel VBA has 5 loops from which we can choose from. Depending on the situation they are to be used for would dictate the type of loop to choose. Some loops are best suited for looping while incrementing a number, while others are ideal for looping through anyone of Excels Objects. The most useful loops are For loop and the For Each loop.

 

Shown below are the 5 loops available to us in Excel VBA.

 

Do Do While Do Until For For Each

 

 

Don’t be Confuse they really are not that complicated – Let’s see theirs example formats.

 

Loop Name Formats of Loop Code
Do  

Do

<Code to repeat>

Loop While <Whatever>

 

Do While  

Do While <Whatever>

<Code to repeat>

Loop

 

Do Until  

Do Until <Whatever>

<Code to repeat>

Loop

 

For  

For <Variable>=<Any Number> To <Any Other Number>

<Code to repeat>

Next   <Variable>

 

For Each  

For Each <Object Variable> in <Object Collection>

<Code to repeat>

Next  <Object Variable>

 

 

Just remember, all they are doing is what you have instructed them to do (via code) either a set number of times or until a condition is met (True or False).

Conditional Statement Select Case

Select Case

 

The other method of checking for single or multiple conditions is the Select Case Function. This is another method used for decision making based on a condition or criteria. It, in my opinion, is much better than If etc. This has the syntax

 

Select Case <Expression to test>

Case <Test1>

Do something

Case <Test2>

Do something

Case Else

Do something else

End Select

Select Case Range(“A1”).Value

Case 100

Range(“B1”).Value = 50

Case 200

Range(“B1”).Value = 10

Case Else

Range(“B1”).Value = 20

End Select

Conditional Statement If…Then…Else Statement

If…Then…Else Statement

 

Conditionally executes a group of statements, depending on the value of an expression.

 

Syntax Format:

If condition Then [statements] Else [statements]

 

Or, you can use the block form syntax:

 

If condition Then

[Statement]

ElseIf condition Then

[Statement]

ElseIf condition Then

[Statement]

ElseIf condition Then

[Statement]

.  .  .  .  .  .

Else: End If

 

Sub My_IF()

Dim MyNumber, A, B, C

MyNumber = Range(“B2”).Value

A = 1

B = 2

C = 3

If MyNumber < 10 Then A = A + MyNumber: B = B + A: C = C + B

Range(“B2”).Value = C

MsgBox (“Your value is:  ” & C)

End Sub

Looping Through Code

Using conditional statements and looping statements (also called control structures), you can write Visual Basic code that makes decisions and repeats actions. Another useful control structure, the WITH statement, lets you to run a series of statements without having to re-qualify an object.

 

 

Using Conditional Statements to Make Decisions

 

Conditional statements evaluate whether a condition is TRUE or FALSE, and then specify one or more statements to run, depending on the result. Usually, a condition is an expression that uses a comparison operator to compare one value or variable with another.

 

Choosing a Conditional Statement to Use

 

  • ..Then…Else: Branching when a condition is True or False
  • Select Case: Selecting a branch from a set of conditions

 

 

 

Using Loops to Repeat Code

 

Looping allows you to run a group of statements repeatedly. Some loops repeat statements until a condition is FALSE; others repeat statements until a condition is TRUE. There are also loops that repeat statements a specific number of times or for each object in a collection.

 

Choosing a Loop to Use

  • ..Loop: Looping while or until a condition is TRUE
  • ..Next: Using a counter to run statements a specified number of times
  • For Each…Next: Repeating a group of statements for each object in a collection

 

 

 

 

Running Several Statements on the Same Object

 

In Visual Basic, usually you must specify an object before you can run one of its methods or change one of its properties. You can use the WITH statement to specify an object once for an entire series of statements.

 

  • With: Running a series of statements on the same object

VBA code for export all email address and mail message from outlook to excel

Sub ExportToExcel()
On Error GoTo ErrHandler
Set appExcel = CreateObject(“Excel.Application”)
Set appExcel = Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
Dim strPath As String
Dim intRowCounter As Integer
Dim intColumnCounter As Integer
Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itm As Object
strSheet = “OutlookItems.xls”
strPath = “C:Attendance”
strSheet = strPath & strSheet

Debug.Print strSheet
‘Select export folder
Set nms = Application.GetNamespace(“MAPI”)
Set fld = nms.PickFolder
‘Handle potential errors with Select Folder dialog box.
If fld Is Nothing Then
MsgBox “There are no mail messages to export”, vbOKOnly, “Error”
Exit Sub
ElseIf fld.DefaultItemType <> olMailItem Then
MsgBox “There are no mail messages to export”, vbOKOnly, “Error”
Exit Sub
ElseIf fld.Items.Count = 0 Then
MsgBox “There are no mail messages to export”, vbOKOnly, “Error”
Exit Sub
End If
‘Open and activate Excel workbook.
Set appExcel = CreateObject(“Excel.Application”)
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
appExcel.Application.Visible = True
‘Copy field items in mail folder.
For Each itm In fld.Items

intColumnCounter = 1

Set msg = itm
intRowCounter = intRowCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.To
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SenderEmailAddress
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Subject
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SentOn
intColumnCounter = intColumnCounter + 1
Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.ReceivedTime

Next itm

Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing
Exit Sub
ErrHandler: If Err.Number = 1004 Then
MsgBox strSheet & ” doesn’t exist”, vbOKOnly, “Error”
Else
MsgBox Err.Number & “; Description: “, vbOKOnly, “Error”

End If
Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing
End Sub

Split word document into multiple documents vba

Option Explicit ‘This goes in the Declarations section of your code module.
‘Hopefully it is already there because you have ticked the ‘Require Variable Declaration’
checkbox. (Tools/Options, Editor tab.)

Sub SplitIntoPages()
Dim docMultiple As Document
Dim docSingle As Document
Dim rngPage As Range
Dim iCurrentPage As Integer
Dim iPageCount As Integer
Dim strNewFileName As String

Application.ScreenUpdating = False ‘Makes the code run faster and reduces screen _
flicker a bit.
Set docMultiple = ActiveDocument ‘Work on the active document _
(the one currently containing the Selection)
Set rngPage = docMultiple.Range ‘instantiate the range object
iCurrentPage = 1
‘get the document’s page count
iPageCount = docMultiple.Content.ComputeStatistics(wdStatisticPages)
Do Until iCurrentPage > iPageCount
If iCurrentPage = iPageCount Then
rngPage.End = ActiveDocument.Range.End ‘last page (there won’t be a next page)
Else
‘Find the beginning of the next page
‘Must use the Selection object. The Range.Goto method will not work on a page
Selection.GoTo wdGoToPage, wdGoToAbsolute, iCurrentPage + 4
‘Set the end of the range to the point between the pages
rngPage.End = Selection.Start
End If
rngPage.Copy ‘copy the page into the Windows clipboard
Set docSingle = Documents.Add ‘create a new document
docSingle.Range.Paste ‘paste the clipboard contents to the new document
‘remove any manual page break to prevent a second blank
docSingle.Range.Find.Execute Findtext:=”^m”, ReplaceWith:=””
‘build a new sequentially-numbered file name based on the original multi-paged file name and path
strNewFileName = Replace(docMultiple.FullName, “.doc”, “
” & Right$(“000” & iCurrentPage, 4) & “.doc”)
docSingle.SaveAs strNewFileName ‘save the new single-paged document
iCurrentPage = iCurrentPage + 4 ‘move to the next page
docSingle.Close ‘close the new document
rngPage.Collapse wdCollapseEnd ‘go to the next page
Loop ‘go to the top of the do loop
Application.ScreenUpdating = True ‘restore the screen updating

‘Destroy the objects.
Set docMultiple = Nothing
Set docSingle = Nothing
Set rngPage = Nothing
End Sub

 

 

Create pdf file from ms word

Sub pdffilesfromword()
Application.ScreenUpdating = False
Dim DocName As String, PDFPath, Folderpath, From, Till, Message, fs
Folderpath = “C:Merged Letters In PDF File From Word”

Set fs = CreateObject(“Scripting.FileSystemObject”)
If fs.FolderExists(Folderpath) = False Then
fs.createfolder (Folderpath)
Else: End If

From = 1
Till = 5

Message = (Till – From) + 1

While From <= Till

ActiveDocument.MailMerge.DataSource.ActiveRecord = From
DocName = ActiveDocument.Fields(1).Result
‘CheckFilename for: ?ActiveDocument.Fields(1).Code
PDFPath = Folderpath & “” & DocName & “.pdf”
Massage = ((Till – From) + 1)

ActiveDocument.ExportAsFixedFormat OutputFileName:=PDFPath, ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
Item:=wdExportDocumentContent, IncludeDocProps:=True

From = From + 1
Wend

MsgBox (Message & ” Pdf Files Saved In = ” & Folderpath)

End Sub

VBA code for move file to other folder

Sub CheckIfFileExists()

Dim LRow As Integer
Dim LPath As String
Dim LExtension As String

Dim LContinue As Boolean
Dim MPath As Integer
Dim FNAme As String

Dim sourceFile As String
Dim targetFile As String

Dim fso As Object

LContinue = True
LRow = 2
MPath = 2
LPath = “C:New Folder”
LExtension = “.pdf”

While LContinue

If Len(Range(“A” & CStr(LRow)).Value) = 0 Then
LContinue = False

Else

aa = “C:New Folder”

Set fso = CreateObject(“Scripting.FileSystemObject”)

bb = “C:New Folder”

Set fs = CreateObject(“Scripting.FileSystemObject”)

bb = bb & “” & Range(“A” & CStr(MPath)).Value
bb = bb & “.PDF”

If fs.FileExists(bb) = True Then

Range(“B” & CStr(LRow)).Value = “Yes”

FNAme = Range(“C” & CStr(MPath)).Value
aa = aa & “” & FNAme

If fs.FolderExists(aa) = False Then
fs.createfolder aa

End If

aa = aa & “”
fso.MoveFile bb, aa

End If
End If

Set fs = Nothing
Set fso = Nothing

LRow = LRow + 1
MPath = MPath + 1
Wend

End Sub

Rows and columns, and other new limits in new excel

<

p style=”text-align: justify;”>What’s new in Microsoft Office Excel 2007

To enable you to explore massive amounts of data in worksheets, Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows and 6,300% more columns than you had available in Microsoft Office Excel 2003. For those of you who are curious, columns now end at XFD instead of IV.

Instead of 4 thousand types of formatting, you can now use an unlimited number in the same workbook, and the number of cell references per cell are increased from 8 thousand to limited by available memory.

To improve the performance of Excel, memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.

You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets.

Office Excel 2007 also supports up to 16 million colors.

Easy-to-use PivotTables

What’s new in Microsoft Office Excel 2007

In Office Excel 2007, PivotTables are much easier to use than in earlier versions of Excel. By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away—you no longer have to drag data to drop zones that aren’t always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.

And after you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.

Using Undo in PivotTables   You can now undo most actions that you take to create or rearrange a PivotTable.

Plus and minus drill-down indicators   These indicators are used to indicate whether you can expand or collapse parts of the PivotTable to see more or less information.

Sorting and filtering   Sorting is now as simple as selecting an item in the column that you want to sort and using sort buttons. You can filter data by using PivotTable filters, such as Greater than, Equals, or Contains.

Conditional formatting   You can apply conditional formatting to an Office Excel 2007 Pivot Table by cell or by intersection of cells.

PivotTable style and layout   Just like you can for Excel tables and charts, you can quickly apply a predefined or custom style to a PivotTable. And changing the layout of a PivotTable is also much easier to do in the new user interface.

PivotCharts   Like PivotTables, PivotCharts are much easier to create in the new user interface. All of the filtering improvements are also available for PivotCharts. When you create a PivotChart, specific PivotChart tools and context menus are available so that you can analyze the data in the chart. You can also change the layout, style, and format of the chart or its elements the same way that you can for a regular chart. In Office Excel 2007, the chart formatting that you apply is preserved when you make changes to the PivotChart, which is an improvement over the way it worked in earlier versions of Excel.