Use VBA (Visual Basic for application) macro _VBA in office Office software

Using VBA (Visual Basic for application) macros in office Office software

Tang Tiexin

Talk about macros, many people think of macro virus, in fact, if there is genuine anti-virus software, open real-time protection, set a good macro security, you can use macros to complete many office automation functions.

Open the Word software, from the file main Menu "Tools"-> "Macros"-> "Security", set security to, so you can choose to execute macros. When you install Office, you should customize the installation and select Visual Basic Help in the Office tools to get a very good help document on the VBA programming environment, VBA language references, VBA objects, and so on. From the Tools-> macro-> Visual Basic Editor, you can open the VBA programming Environment-Microsoft Visual Basic. You can see Help when you open Help-> Microsoft Visual Basic Help in a programming environment.

Many people feel that learning Visual Basic is useless, in fact, the Visual Basic series of programming languages, including VB, VBA, VBScript and so on, you can write Web pages, office automation programming, application programming. VBA can also be used in some other companies ' applications, such as Corel Draw. Here are a few examples to illustrate the use of VBA.

If you have dozens of documents that need to be merged into one document, it's a hassle to open and copy them individually, and you can do that with macros.

Example one: Merging documents. Create a new form and put a button on the form. The toolbox can be opened from the View-> Toolbox.

' button event Private Sub CommandButton1_Click () Dim spath As String ' spath used to save path Dim tem as String ' intermediate variable Set newdoc = Documents. Add ' uses Documents.Add to add a new document with Newdoc. SaveAs filename:= "D:/txtal.doc" ' New document saved full path end with Activedocument.close ' closes the new document with Dialogs (Wddi Alogfilefind) ' Sets the text Lookup dialog box. SortBy = 2 ' files are arranged by name. Searchname = "*.txt" only lists text files. Update ' updates ' with If Dialogs (wdDialogFileFind). Show =-1 Then ' If pressed open, execute spath = Dialogs (wdDialogFileFind). SearchPath ' adds value to spath Dialogs (wddialogfilefind) with the path opened by the Find dialog box. Execute Else Exit Sub ' Sets the search for the application by exiting the end if and Application.FileSearch ' if it is canceled or closed. NewSearch ' New lookup. LookIn = spath ' Sets the path of the new lookup to the path (spath) of the file dialog box. SearchSubFolders = True ' to find subdirectories. FileType = Msofiletypeallfiles ' finds all file types, where you can set other types, specifically the FileType properties of FileSearch objects. filename = "*.txt" to find a file with a suffix of txt. ExecutE ' perform file lookup Documents.Open ("D:/txtal.doc") ' Open ' d:/txtal.doc ' Set Range2 = activedocument.content ' Set the Range2 object to the contents of the active document for i = 1 to. FoundFiles.Count ' opens the found file, copies the contents of the file, and adds it to the new D:/txtal.doc file Documents.Open (. FoundFiles (i)) ' Open the first found file ActiveDocument.Content.Select ' contents of the selected active file selection.copy ' Copy selected content Ac                             Tivedocument.close ' Close active document Range2.collapse Direction:=wdcollapseend ' Set paste position as Range2 last Range2.paste                     ' Paste the copied content into Range2 Next i activedocument.save ' to save the active document Activedocument.close

 ' Close the active document end with the end Sub

If you have a large number of Word documents that need to modify a term or a fixed statement, it's a hassle to open it all at once, and you can use macros to do it very quickly without mistakes. This task can be accomplished by modifying the example.

Example two: full-text lookup replacement.

Private Sub CommandButton1_Click () Dim spath as String with Dialogs (wddialogfilefind) ' Sets the text Lookup dialog box. SortBy = 2 ' files are arranged by name. Searchname = "*.doc" lists only *.doc files. Update ' updates ' with If Dialogs (wdDialogFileFind). Show =-1 Then ' If pressed open, execute spath = Dialogs (wdDialogFileFind). SearchPath ' adds value to spath Dialogs (wddialogfilefind) with the path opened by the Find dialog box. Execute Else exit Sub ' If canceled or closed, exits the end if with Application.FileSearch. NewSearch. LookIn = spath. SearchSubFolders = True. FileType = msofiletypeworddocuments ' lookup file type is a Word document. Execute for i = 1 to. FoundFiles.Count Documents.Open (. FoundFiles (i)) Activedocument.select with Dialogs (wddialogeditreplace) on Error GoTo ERR. Find = TextBox1.Text. Replace = TextBox2.Text. ReplaceAll = True. Execute End and Activedocument.save activedocument.close Next i with err:if TextBox1.Text = "" Then Ocument.
 Save Activedocument.close End If MsgBox "Please enter a replacement lookup string" End Sub
 

In Exel I didn't find the ability to change rows and columns, so I wrote a macro.

Example three: In Exel, rows are changed into columns. In Exel, open the programming environment from the tools-> macros-> the Visual Basic Editor, creating macros by inserting the-> module.

Sub change ()           ' Change macro
  Dim row (0 to 0) as String        ' defines a 21*21 two-dimensional array
  Range ("A1"). Select                         ' Select A1 cell for
i = 0 to-for
    j = 0
        to row (i, j) = ActiveCell.Offset (i, J). Value   ' Values the contents of the cell of the A1 Displacement (i,j) to the array row (I,J)
    next J
Next I

Range ("A1"). Select on the
Error GoTo err
for i = 0 to the for
    j = 0
        to ActiveCell.Offset (i, J). Value = Row (j, i)  ' to attach the contents of an array row (J,i) to the A1 displacement (i,j) cell
    Next J
Next i
Exit Sub
err:
MsgBox Error End

Sub

netscaner@sina.com.cn