Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines. Let’s look at each one in turn and use them in a simple way. Do Loop START: Do (tell our Loop) STOP: Do … Read more
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 … Read more
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 … Read more
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] . . . . … Read more
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 … Read more
Sub ExportToExcel()On Error GoTo ErrHandlerSet appExcel = CreateObject(“Excel.Application”)Set appExcel = Excel.ApplicationDim wkb As Excel.WorkbookDim wks As Excel.WorksheetDim rng As Excel.RangeDim strSheet As StringDim strPath As StringDim intRowCounter As IntegerDim intColumnCounter As IntegerDim msg As Outlook.MailItemDim nms As Outlook.NameSpaceDim fld As Outlook.MAPIFolderDim itm As ObjectstrSheet = “OutlookItems.xls”strPath = “C:Attendance”strSheet = strPath & strSheet Debug.Print strSheet ‘Select … Read more
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 … Read more
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 = 1Till = 5 Message = (Till – From) + 1 While From <= Till ActiveDocument.MailMerge.DataSource.ActiveRecord = … Read more
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 … Read more
< 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 … Read more
