Excel的VBA初识

变量数组

'强制变量申明
Option Explicit
'申明全局变量在其他模块也可以用
Public Price As Double

Sub 变量()
    'Byte 整数0-255
    'Boolen bool值
    'Integer 整数-32768-32767，long整数，Currency整数
    '小数：Single，Double
    '日期：Date，字符String
    
    Dim Dc
    Dc = 1
    '弹窗
    MsgBox Dc
    Dc = "Hello"
    MsgBox Dc
    
    
    Dim score As Date, word As String
    score = #1/1/1900#
    word = "Hello world"
    MsgBox score & word
    '按方块键和关闭安文档会停止存储
    Static Count As Integer
    Count = Count + 1
    MsgBox "第" & Count & "次"
End Sub

常量

Sub SSS()
    'Price = 5.6
    'MsgBox Price
    '常量开始就要赋值，不能改变
    Const P As Double = 3.5
    Dim res
    res = P ^ 4
    '比较运算> >= <= = <>:不等于
    '逻辑运算符 And Or Not
    res = 2
    '条件
    'If res = 1 Then
    '    MsgBox 1
    'ElseIf res = 2 Then
    '    MsgBox 2
    'Else
    '    MsgBox 3
    'End If
    '相同用法
    'Select Case res
    '    Case Is >= 90
    '        MsgBox "good"
    '    Case Is >= 80
    '        MsgBox "well"
    '    Case Else
    '        MsgBox "bad"
    'End Select
    
    'For循环
    'For Num = 1 To 5
    '    res = res + Num
    '    Next Num
    'MsgBox res
    
    '循环跳两次
    For Num = 1 To 5 Step 2
        If Num >= 5 Then
        'Exit for 中断循环
            Exit For
        End If
    '    MsgBox Num
        res = res + Num
        Next Num
   'MsgBox res
    
    Dim mum As Integer
    'DoWhile循环
    'Do While mum <= 10
        'MsgBox mum
     '   mum = mum + 1
    'Loop
    
    mun = 0
    'do Until循环
    Do Until mum > 10
        mum = mum + 1
        MsgBox mum
    Loop
    '中断子程序
    Exit Sub
    
End Sub

函数

Option Explicit

Sub SubTest(Num As Integer)
    For Num = 1 To Num
        MsgBox Num
        Next Num
End Sub

Sub SubTesttwo(Num As Integer, two As Integer)
    Do While two <= Num
        MsgBox two
        two = two + 1
    Loop
End Sub

Sub Test()
    'SubTest (2)
    SubTesttwo 3, 1
    Dim Value As Double
    Value = Cube(1, 5)
    MsgBox Value
    
End Sub

'这个函数也可以在在
Function Cube(x As Integer, y As Integer)
    Cube = x + y
End Function

数组

'设置数组第一位索引值为1
Option Base 1

Sub ArrayTest()
    '一维数组
    Dim MyArray(1 To 3) As Integer
    
    'LBound为数组初始值，UBound为数组
   ' For Index = LBound(MyArray) To UBound(MyArray)
    '    MyArray(Index) = Index
     '   MsgBox MyArray(Index)
      '  Next Index
    
    Dim newArray(3) As Integer
    For Index = LBound(MyArray) To UBound(MyArray)
        'MsgBox Index
        Next Index
    '二维数组(行，列）
    Dim lll(1 To 2, 1 To 3) As Integer
    
    '获取行最小和最大小标
    For Row = LBound(lll, 1) To UBound(lll, 1)
        For low = 1 To 3
            lll(Row, low) = Row + low
           ' MsgBox lll(Row, Low)
            Next low
        Next Row
    
    '动态数组
    Dim dotai() As Integer
    '定义动态数组大小
    ReDim dotai(1 To 3)
    '定义动态数组大小,之前的定义和赋值会被抹除
    ReDim dotai(1 To 3, 1 To 2)
    For roow = LBound(dotai, 1) To UBound(dotai, 1)
        For low = 1 To 2
            dotai(roow, low) = roow + low
            MsgBox dotai(roow, low)
            Next low
        Next roow
    '定义动态数组大小,之前的定义和赋值不会被抹除
    ReDim Preserve dotai(1 To 3, 1 To 2)
End Sub

返回目录