学习VBA-VBA基础语法讲解（一）

     因为最近在做公司MRP（material Requirement Plan）的时候涉及到对许多excel报表的操作，因为报表来自不同国家和地区，格式是多种多样，如果是用C#来整理资料的话会出现效率低下，而且容易出错，所以最后想了一个办法用VBA把几百个excel的资料整理出来生成txt档案，然后再倒进系统，这样txt档案里的数据时标准统一的数据了，这样会减轻系统的负担，有错误也在进系统前档掉了，也减轻人工的操作（想想一天上传几百个excel也是挺繁琐的事情吧？）。那么下面让我来简单介绍下什么是VBA吧，应该有不少人不知道，我也是进公司后才知道的，开始不屑一顾，后来发现这个东西功能挺强大，在excel，word,ppt,outlook之间操作时游刃有余，而且能访问数据库、调用系统api、还能访问webservice，这样减轻我们的办公负担。

 

开篇介绍(因为一开始在公司做的ppt都是英文，有兴趣的可以看一下，因为这是个基础，后面各个章节会讲具体操作和按列)  

 

一：什么是vba,怎么运行。

 VBA(Visual Basic for Applications )

The goal is to introduce how VBA can be used to help staff

1、VBA syntax and usage

2、Logical & Loop statements

3、Example

Accessing VBA in excel

 

 Tools ->Macros ->Visual Basic Editor

 

1.

 

 

2.  

 

 

In order to run VBA code your security settings must be properly set

 

Tools | Macro | Security…

 

1.

 

 

 

2.

 

At least Medium security must be set – each macro will require user verification to run

2.变量声明 

Declare by Dim Better to use Data Types:
     Dim amount As Double
     Dim year As Integer
     Dim name As String Default (no type) is Variant Use Option Explicit in the declarations section to require declaration of variables

  

3.数据类型

类型         字节长

Integer 2 byte integer

Long 4 byte integer

Single 4 byte floating point

Double 8 byte floating point

Currency 8 byte real

String up to 64K characters

Byte 1 byte

Boolean 2 byte true or false

Date 8 bytes

Object 4 bytes – an object reference

Variant 16 bytes + 1 byte / character

Example:string

String variables

Dim variable As String Dim variable As String * 50

The first form is variable length

The second form is limited to 50 characters

the variable will be space filled if string is < 50 characters

the string will be truncated if the contents are > 50 characters

  

4.数组

 Arrays are declared using

      Dim A (1 To 10) As Double

      Dim B (1 To 10, 1 To 10) As Double

      Dim C (4,4,4) As Integer

      Dim D () As Double

The lower bound starts at zero

      can explicitly specify lower bound

      can use Option Base command to reset to something other than 0

      Option Base 1

The last form above is a dynamic array – it must be dimensioned using ReDim before it can be used }Use ReDim Preserve to retain any existing entries in array - only the upper bound of array can be changed

  

5.常量

 [Public|Private] Const constantName [As type] = expression
Public Const PI = 3.1, NumPLANETS = 9
Const PI2 = PI * 2
Const RELEASE = #1/1/99/#

 

6.对象

To declare a variable that will refer to an instance of the Excel Worksheet clas

 

Dim ws1 As Worksheet

 

To put a reference into it

 

Set ws1 = Worksheets(“Sheet1”)

 

对象集合

 

There is a special form of objects known as Collections

 

They contain references to other objects and collections

 

It is the mechanism by which the object hierarchy is defined

 

By convention, collection names are usually plural

 

Workbooks – list of Workbook objects

 

Worksheets – list of Worksheet objects

 

Range – list of objects that represent cells, columns, rows

 

 

The following example iterates through Workbooks collection

 1 For Each ws In Worksheets
2
3     Debug.Print ws.Name
4
5  Next
6  

 7.逻辑

          1.判断

If anyDate < Now Then anyDate = Now

If anyDate < Now Then
      anyDate = Now
End If

If Index = 0 Then
      CopyActiveControl
      ClearActiveControl
Else If Index = 1 Then
      CopyActiveControl
Else If Index = 2 Then
      ClearActiveControl
Else      
      PasteActive Control
End If

2.选择

Select Case Index
        Case 0
                CopyActiveControl
                ClearActiveControl
        Case 1
                CopyActiveControl
        Case 2
                ClearActiveControl
        Case 3
                PasteActive Control
        Case Else
                frmFind.Show
End Select

                 3.循环

Do While condition
          statements
  Loop

  Do
          statements
  Loop While condition

 

                Do Until condition
                statements
        Loop

        Do
                statements
        Loop Until condition

 

 

}For … Next
For counter = start

 

8.函数调用

Sub (routines)

no value returned

Called without parenthesis
              mySub param1, param2

Called with parenthesis
              Call mySub(param1, param2)

Functions

value returned

assign return value to function name

To end [Step increment]
 statements
 Next counter