首页 » Tutorials » Excel VBA入门基础与常用函数

Excel VBA入门基础与常用函数

作者: lesca 分类: Tutorials 发布时间: 2012-07-16 12:24 ė浏览 4,816 次 6Comments Off on Excel VBA入门基础与常用函数

一、数据类型

1.变量与类型

  • Dim x As Integer
  • Dim x As Double
  • Dim x As String
  • Dim x As Boolean
  • Dim x As Date
  • Dim x As Range

2.数组

Dim Films(1 To 5) As String

Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"

3.字符串

  • Left(“example text”, 4) // Output: exam
  • Right(“example text”, 2) // Output: xt
  • Len(“example text”) // Output: 12
  • Instr(“example text”, “am”) // Output: 3
  • Mid(“example text”, 9, 4) // Output: text

4.特殊符号

连接符号
“Value is ” & Range(“A1”).value
换行符
MsgBox “Line 1” & vbNewLine & “Line 2”

二、操纵单元格

1.范围

Dim example As Range
Set example = Range("A1:D1")
  • example.Count
  • example.Value = 8
  • example.Select
  • example.Rows(3).Select
  • example.Columns(2).Select
  • example.Columns.Count
  • example.ClearContents

2.复制与粘贴

Range("A1:A2").Select
Selection.Copy

Range("C4").Select
ActiveSheet.Paste

3.工作簿

  • Application.Workbooks(“Book1”).Worksheets(1).Range(“A1”).Value
  • Worksheets(1).Range(“A1”).Value
  • Worksheets.Count
  • Worksheets.Add

4.单元格赋值

  • Range(“A1”).value = “Hello”
  • Range(“A1”, “D4”).value = “Hello”
  • Range(“A1:D4”).Value = “Hello”
  • Cells(1,1).value = “Hello”

5.清空单元格

  • Range(“A1”, “D4”).Clear
  • Range(“A1”).ClearContents

6.公式

  • Range(“B1”).Formula = Range(“A1”) * 2
  • Range(“B1”) = WorksheetFunction.Average(Range(“A1:A4”))

三、函数

1.消息

MsgBox “This is fun.”

2.函数(返回值)

Function Area(x As Double, y As Double) As Double
	Area = x * y
End Function

3.过程

Sub Area(x As Double, y As Double)
	MsgBox x * y
End Sub

四、条件判断

1.IF-THEN-ELSE-END IF

If score >= 60 Then
    grade = "passed"
Else
    grade = "failed"
End If

2.条件逻辑(AND, OR, NOT)

If Range("B1").Value >= 110 And Range("B2").Value >= 7 Then ...
If Range("B1").Value >= 110 Or Range("B2").Value >= 7 Then ...
If Not Range("B3").Value = "Yes" Then ...

五、循环

1.FOR循环

For i = 1 To 4
	'Do something
Next i

2.FOR Each循环

Dim entry, myRange As Range
Dim sum As Integer
Set myRange = Range("A1:D4")
myRange.Select
sum = 0
For Each entry In myRange
    sum = entry.Value + sum
Next

3.Do Until循环

i = 1
Do Until Cells(i, 1).Value = ""
	i = i + 1
Loop
row_end = i

六、时间与日期

1.日期

Dim exampleDate As Date
exampleDate = DateValue("Jun 19, 2010")
  • Year(exampleDate) // Output: 2010
  • Month(exampleDate) // Output: 6
  • Day(exampleDate) // Output: 19

2.NOW函数

  • Hour(Now)
  • Minute(Now)
  • Second(Now)

3.TimeValue函数

Dim y As Double
y = TimeValue("09:20:01 am") // Output: 0.388900462962963
Hour(y) // Output: 9
Miniute(y) // Output: 20
Second(y) // Output: 01

Reference:

[1] Excel VBA Easy

本文出自 Lesca 技术宅,转载时请注明出处及相应链接。

本文永久链接: https://lesca.me/archives/excel-vba-basics-andselected-functions.html

Ɣ回顶部