How to Connect Paragraph using word vba

As I mentioned in the previous article, VBA allow us to automate your tasks, and your will be done quickly with vba because vba programming can perform many procedure at the same time. In this article, I will show how to connect two or more paragraphs together by just select the paragraph and then run the macro. Normally, when we want to connect to paragraph together, we need to delete the paragraph mark.

Suppose that we want to connect 20 paragraphs in word document, we need to delete the paragraph mark 20 times. However with vba programming, we can delete the 20 paragraphs marks at once.

How to delete the Paragraph Marks

Word VBA provides us the Selection object to word to selection text, paragraph, etc.

In order to delete paragraph mark using selection, first we need to select the paragraph using selection object

≫ Select 5 paragraph from the position of cursor down

Selection.MoveDown Unit:=wdParagraph, Count:=5, Extend:=wdExtend

≫ detect the paragraph and delete

Ifopara.Range.Words.Count > 1 Then

            IfisDeleted Then ExitFor

        Else

            opara.Range.Delete

            isDeleted = True

        End If

≫ then use selection.Find to find the paragraph mark and replace with space

    WithSelection.Find

        .ClearFormatting

        .Text = "^p"

        .Replacement.ClearFormatting

        .Replacement.Text = " "

        .Wrap = wdFindStop

        .Forward = True

        .Format = False

        .MatchCase = False

        .MatchWholeWord = False

        .MatchKashida = False

        .MatchDiacritics = False

        .MatchAlefHamza = False

        .MatchControl = False

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

        .Execute Replace:=wdReplaceOne

    EndWith

   

Full Code of this article

PublicSub ConnectParagraph()

    Dimopara As Paragraph

    DimisDeleted, isFirst As Boolean

    DimsLIndent, sRIndent, sSBefore, sSAfter, sFLIndent As Single

   

    isDeleted = False

    isFirst = True

    Selection.MoveDown Unit:=wdParagraph, Count:=5, Extend:=wdExtend

    ForEach opara In Selection.Paragraphs

        Ifopara.Range.Words.Count > 1 Then

            IfisDeleted Then ExitFor

        Else

            opara.Range.Delete

            isDeleted = True

        End If

       

        ' Save format

        IfisFirst Then

            sLIndent = opara.LeftIndent

            sRIndent = opara.RightIndent

            sSBefore = opara.SpaceBefore

            sSAfter = opara.SpaceAfter

            sFLIndent = opara.FirstLineIndent

        End If

       

        isFirst = False

    Next opara

   

    WithSelection.Find

        .ClearFormatting

        .Text = "^p"

        .Replacement.ClearFormatting

        .Replacement.Text = " "

        .Wrap = wdFindStop

        .Forward = True

        .Format = False

        .MatchCase = False

        .MatchWholeWord = False

        .MatchKashida = False

        .MatchDiacritics = False

        .MatchAlefHamza = False

        .MatchControl = False

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

        .Execute Replace:=wdReplaceOne

    EndWith

   

    WithSelection.Paragraphs(1)

        .FirstLineIndent = sFLIndent

        .LeftIndent = sLIndent

        .RightIndent = sRIndent

        .SpaceBefore = sSBefore

        .SpaceAfter = sSAfter

    EndWith

   

    ' Move back to the beginning of the paragraph

    Selection.Collapse wdCollapseStart

EndSub

How to test this Code

1. Open Ms Word

2. Open VB Editor or Alt+F11

3. Create new module

4. Copy and paste the Code

5. Go back to you word and go to Macro Dialog then select the Macro Name ConnectParagraph and Click on Run

Read More...

How to Change Font Format using VBA

In this section, I will show you on how to use vba code to change the format font.

In the following code, I discuss about the how to change the font format bold and italic while in th word document do not have the format of bold and Italic but it has the property of font name with bold or italic for instance Font Time New Roman-Bold. Firstly, in order to change the font format with this case, I need to detach the font name of each words or characters within the word document. And then create the function for finding and replacing with font format as we want to replace.

Detecting the font Propertry

To detect the font name properties in word document, I use vba code called: Range object  and ActiveDocument.content.Paragraphs to find the fount properties in each word or characters.

ActiveDocument.Content.Paragraphs

And then I compare the text string in each words

InStr(1, CStr(fontList(k)), "ITALIC", vbTextCompare)

InStr(1, CStr(fontList(k)), "BOLD", vbTextCompare)

Replacing font Format

In order to change the font bold or italic, I use the following function which take a paramer called font name and use this font for replacing styles

Public SubChangeFontFormatItalic(fontname As String)

    Selection.HomeKey

    Selection.WholeStory

    Selection.Find.ClearFormatting

    Selection.Find.Replacement.ClearFormatting

    Selection.Find.Font.Name = fontname

    Selection.Find.Replacement.Font.Italic = True

    With Selection.Find

        .Text = "^?"

        .Replacement.Text = ""

        .Forward = True

        .Wrap = wdFindContinue

        .Format = True

        .MatchCase = True

        .MatchWholeWord = False

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

    End With

    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

'Change Font Name with Bold to Font Time New Roamn with Bold format

Public SubChangeFontFormatBold(fontname As String)

    Selection.HomeKey

    Selection.WholeStory

    Selection.Find.ClearFormatting

    Selection.Find.Replacement.ClearFormatting

    Selection.Find.Font.Name = fontname

    Selection.Find.Replacement.Font.Bold = True

    With Selection.Find

        .Text = "^?"

        .Replacement.Text = ""

        .Forward = True

        .Wrap = wdFindContinue

        .Format = True

        .MatchCase = True

        .MatchWholeWord = False

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

    End With

    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

Full Code

Here is the full code of this tutorial section

Public Sub ChangeFormatFont

    Dim sTmp As String

    Dim i, j, k As Integer

    Dim opara As Paragraph

    Dim fontFont As Boolean

    Dim fontCount As Integer

    Dim fontList(200) As String

    Dim fontname As String

    Dim fontItalic As String

    Dim fontBold As String

    fontCount = 0

    For Each opara In ActiveDocument.Content.Paragraphs

        If opara.Range.Words.Count > 1 Then

            For i = 1 To opara.Range.Words.Count

                With opara.Range.Words(i)

                    fontname = .Font.Name

                    FoundFont = False

                    For k = 1 To fontCount

                       If fontList(k) = fontname Then FoundFont = True

                    Next k

                    If Not FoundFont Then

                        fontCount = fontCount + 1

                        fontList(fontCount) = fontname

                    End If

'

               ' Debug.Print .Font.Name

              

                End With

            Next i

        End If

    Next opara

    For k = 1 To fontCount

        If InStr(1, CStr(fontList(k)), "ITALIC", vbTextCompare) Then

            fontItalic = fontList(k)

            ChangeFontFormatItalic (fontItalic)

        End If

        If InStr(1, CStr(fontList(k)), "BOLD", vbTextCompare) Then

            fontBold = fontList(k)

            ChangeFontFormatBold (fontBold)

        End If

    Next k

    Debug.Print fontItalic

    Debug.Print fontBold

   

    Set opara = Nothing

    'MsgBox "Complete !!!"

End Sub

Public Sub ChangeFontFormatItalic(fontname As String)

    Selection.HomeKey

    Selection.WholeStory

    Selection.Find.ClearFormatting

    Selection.Find.Replacement.ClearFormatting

    Selection.Find.Font.Name = fontname

    Selection.Find.Replacement.Font.Italic = True

    With Selection.Find

        .Text = "^?"

        .Replacement.Text = ""

        .Forward = True

        .Wrap = wdFindContinue

        .Format = True

        .MatchCase = True

        .MatchWholeWord = False

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

    End With

    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

'Change Font Name with Bold to Font Time New Roamn with Bold format

Public Sub ChangeFontFormatBold(fontname As String)

    Selection.HomeKey

    Selection.WholeStory

    Selection.Find.ClearFormatting

    Selection.Find.Replacement.ClearFormatting

    Selection.Find.Font.Name = fontname

    Selection.Find.Replacement.Font.Bold = True

    With Selection.Find

        .Text = "^?"

        .Replacement.Text = ""

        .Forward = True

        .Wrap = wdFindContinue

        .Format = True

        .MatchCase = True

        .MatchWholeWord = False

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

    End With

    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

That is all for this tutorial. 

Read More...

How to Remove Shape in Ms Word

How to remove shape in Ms Word using VBA Macro

In Microsoft word, it provides the approach to write VBA Function known as Macro to perform automatically tasks in Ms Word. We can also implement VBA class objects with VBA’s function for the automatically tasks. First, write your own function to remove all shapes which contain in Word File. VBA contains a Class Object called Shape and Shapes to deal with all shapes. We can use Shape Object to delete, select or duplicate the shape.

Code Sample to delete all shapes

Here is complete code example to remove all shapes. This example uses Shapeobject in VBA:

SubRemoveShapes()

    Dim oShape AsShape

    Do

        For EachoShape In ActiveDocument.Shapes

           ' Delete the shape

            oShape.Delete

        NextoShape

    Loop UntilActiveDocument.Shapes.Count = 0

   

    Set oShape = Nothing

End Sub

How to test this Code

1. Open Ms Word

2. Open VB Editor or Alt+F11

3. Create new module

4. Copy and paste the Code

5. Go back to you word and go to Macro Dialog then select the Macro Name RemoveShape and Click on Run

That’s all on how to remove all shapes in Ms Word. 

Read More...

VBA Tools and Techniques – VBA Tips

Developing an Application requires the technique. Unlike the other language, VBA is a language that is special for Microsoft Office Application, which is built by Microsoft. The technique in VBA is the quite the same as programming by Microsoft likes VB.net, C#. For Visual Basic Application (VBA), it requires four steps to start with. These techniques include designing an interface, setting property to each control, implementing or coding, and testing.

Designing an interface:

Designing interface is important. It helps you to know what you will deal with. In order to design an interface you should know some controls that exist in the controls group.

Here is some basic control

Text Box control, sometime called an edit field or edit control, displays the information entered at design time, entered by user, or assign to the control at run time.

Label is a graphical controlthat you can use to display text that a user cannot change directly.

Command Button control is used to begin, interrupt, or end a process, when chosen; A Command Button appears pushed in and so is sometime called a push button.

Combo Box control is the same as textbox except it provide user to select data from the list for inputting without typing

List Box is use to display data in a list.

Option Group: use for grouping data or function.

Option Button uses grouping data that user select only one from that values. Option Button is true when it is selected otherwise is false.

Check Box is similar to option Button but user can select more than one.

Image Box is used to display image

Setting Property to Each Control

Setting Property is the step where you name or object within your interface. Setting Property helps you to know what your object is and the name of your object is easy to remember. In order to setting property you need the conversion of object name. The following is the most common conversion in VBA.

Object	Prefix	Example
Form	frm	frmSum
CommandButton	cmd	cmdSum
Label	lbl	lblInputX
Text Box	txt	txtResult
Check Box	chk	chkOption
Menu	mnu	mnuEdit

Implementing (Coding)

Coding is to add event or procedure to you controls. This event can be Button click event, mouse drag and drop, text change… etc For example:

.Private Sub cmdSum_Click()

   txtSum = CInt(txtInputX.Text) + CInt(txtInpuY.Text)

End Sub

Testing and Debug code

Testing is the step that you must do to find all bugs in your code. In the Testing Step, you can see errors and how your code work through intermediate window and Watch window in the VBA Editor Explorer.

Example Design interface and Setting Properties

1. Start with new form object

2. Start Drawing Some Control

3. Setting Properties to each control

Form1

Name	frmSum
Caption	Sum Two Number

Label1

Name	lblInputX
Caption	InputX

Label2

Name	lblInputY
Caption	InputY

Label3

Name	lblResult
Caption	Result

TextBox1

Name

txtInputX

TextBox2

Name

txtInputY

TextBox3

Name

txtResult

CommandButton1

Name	cmdSum
Caption	Sum

CommandButton2

Name	cmdCancel
Caption	Cancel

After that, our form would look like the following form

In summary, VBA require you to know how to deigning interface, setting property to control, and implementing or coding so that you can easily work with VBA. 

Read More...

Using Code Window VBA

The VBA Code Window is where you write, edit, and view the code. The Code Window is like a word processor or text editor. For example, you can type text, use the backspace or delete text. And just like in Word, you can press Tab to indent the text, select text by dragging the mouse point to it, and copy and paste text.
As mentioned, the code window is all really a small word processor or text editor. But word processors tend to be oriented around paragraphs of text, whereas the Code Window is built for typing individual lines of code. Unlike word processors – where you do not press Enter until you get to the end of paragraph – in the Code window, you press Enter at the end of each line you type.

When you type a line of VBA code and press Enter, The Visual Basic Editor compiles that line of code. For now, you can think of compiling as testing the line of code to see whether it works. If you type the same line at random in the Code Window – pr even if you try to type a legitimate line of code but make a mistake – you will see the compile error message, as shown in below Figure.

Earlier, we talk about ways of dealing with compile errors. In the next article, we will get to telling on how to write code. For now, just realize that if you are type anything other than a valid line if VBA code into the Code Window, you can see a compile error message as soon as you press Enter. So don’t waste your time trying to type text at random into the Code Window.

Read More...

How to use Property Window VBA

In this article, I would like to show how to use Property Window in VBA. The Property window in the VBA Editor can be quite perplexing because it displays the property of whatever object is selected in Word, Excel, and of course Access, the property window might show nothing. That is often the case when you are working with standard modules because the standard module does not tied any particular object or event.

To illustrate how thing tie together, the following Figure shows a portion of a form, In Design View, in Access. A Form is selected. In the VBA Editor which also appears in this figure, The Properties for that selected sub-form appear in the VBA Editor Properties Window.

In this figure, you also see an example of how Project Explorer might look in a database that already contains some modules. The modules whose name begin with the world Form_ are all the class modules that are attached to forms in the Database.

The most important thing about the Property window is that it is optional, and you really don’t need it taking space in your VBA editor when you are not using them. Most of time, you probably won’t use it, so feel free to close it.

Read More...