VBA的第一次程序汇总

Option Explicit

Sub Resize_1()
Dim myRange As Range
'Set myRange = ActiveSheet.UsedRange
'Set myRange = Range(“A2”).Resize(5, 6)
'myRange.Select
'MsgBox "当前选择的单元格区域地址为： " & myRange.Address

'在原区域的基础上实现行/列的增加
Set myRange = ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count + 1, _
    ActiveSheet.UsedRange.Columns.Count + 1)
MsgBox "当前选择的单元格区域地址为： " & myRange.Address
myRange.Select
'myRange = "$"   将区域内的所有元素进行赋值

End Sub

Sub Cells_1()
Dim i As Integer
'For i = 3 To 8
’ Cells(2, i) = i - 2
'Next
'For i = 3 To 17
’ Cells(i, 1) = i - 2
'Next
End Sub
Sub Offset_1()
Dim i As Integer
For i = 2 To 7
'Range(“A2”).Offset(0, i) = i - 1
Next
For i = 1 To 15
’ Range(“A2”).Offset(i, 0) = i
Next
End Sub
Sub UsedRange_1()
Dim myRange As Range
Dim myRow As Long
myRow = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet.UsedRange.Select
Set myRange = ActiveSheet.UsedRange.Resize(myRow - 1).Offset(1, 0) '下移
myRange.Select
MsgBox "当前选择的单元格区域地址为： " & myRange.Address
End Sub
Sub End_1()
Dim myRange As Range
Set myRange = ActiveSheet.UsedRange
'ActiveSheet.Cells(19, “L”).End(xlToRight).Select 用Cells()属性可以定位到具体单元格，因此可以实现End（）
'MsgBox "当前选择的单元格： " & ActiveSheet.Cells(19, “L”).End(xlToRight).Value
'MsgBox "当前选择的单元格： " & Range(“F8”).End(xlToRight).Value

myRange.Select
     'MsgBox myRange.Column  '返回Range的第一列列号，同理Row
     'MsgBox myRange.Columns.Count '返回Range的列数，同理Rows
     'MsgBox myRange.Cells(1, 1).End(xlToRight).Column  尽管所选择区域自定义，但返回的列号为其在EXCEL中的位置。

'查找区域内给定元素，然后找到其所在行列的End()元素
Dim i As Integer
Dim j As Integer
Dim num As Integer
Dim spe_ele As Integer

spe_ele = 99

num = myRange.Rows.Count * myRange.Columns.Count
MsgBox "所选择区域共计元素：" & num & "个"

For i = 1 To myRange.Rows.Count
    For j = 1 To myRange.Columns.Count
        If myRange.Cells(i, j) = spe_ele Then
            MsgBox "当前选择的单元格所在行/列的End元素为： " & myRange.Cells(i, j).End(xlToRight).Value
                                            'End(xlToleft)   End(xlToUp)   End(xlToDown）
        End If
    Next
Next

End Sub

Sub InputBox_1()
Dim myName As String, mySex As String, _
myAge As String
myName = InputBox(Prompt:=“请输入您的姓名：”, Title:=“个人信息输入框”, Default:=“太阳”)
'应当注意，这里几个参数的顺序是固定的，不能变更。这样，我们就能省略参数的说明，例如，上句代码与下句等价：
'myName = InputBox(“请输入您的姓名：”, “个人信息输入框”, “太阳”)
'当然，对于我而言，由于不熟悉VBA，建议保留参数说明，以增强代码的可阅读性

mySex = InputBox(Prompt:="您的姓名是：" + myName + _
                vbCrLf + "下面请输入您的性别信息:", _
                Title:="个人信息输入框", Default:="月亮")
myAge = InputBox(Prompt:="您的姓名是：" + myName + _
                vbCrLf + "您的性别是：" + mySex + _
                vbCrLf + "下面请输入您的年龄信息：", _
                Title:="个人信息输入框", Default:="星星")
'注意换行符的使用方法，注意信息录入顺序

End Sub