因为最近在做公司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、ExampleAccessing 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 div to require declaration of variables3.数据类型类型 字节长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 / characterExample:stringString 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 characters4.数组
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 changed5.常量
[Public|Private] Const constantName [As type] = expressionPublic Const PI = 3.1, NumPLANETS = 9Const PI2 = PI * 2Const 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 ForEach ws In Worksheets2 3 Debug.Print ws.Name4 5 Next6
7.逻辑
1.判断
If anyDate < Now Then anyDate = Now
If anyDate < Now Then anyDate = NowEnd If
If Index = 0 Then CopyActiveControl ClearActiveControlElse If Index = 1 Then CopyActiveControlElse If Index = 2 Then ClearActiveControlElse PasteActive ControlEnd If
2.选择
Select Case Index Case 0 CopyActiveControl ClearActiveControl Case 1 CopyActiveControl Case 2 ClearActiveControl Case 3 PasteActive Control Case Else frmFind.ShowEnd Select
3.循环
Do While condition statements Loop Do statements Loop While condition
Do Until condition statements Loop Do statements Loop Until condition
}For … NextFor 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]statementsNext counter 有勇气并不表示恐惧不存在，而是敢面对恐惧克服恐惧
