vba实用代码

以下是一些常用的VBA代码：

在单元格中输入公式

Sub InsertFormula()
Range(“A1”).Formula = "=SUM(B1:B10)"
End Sub

在单元格中输入数值

Sub InsertValue()
Range(“A1”).Value = 100
End Sub

在单元格中输入文本

Sub InsertText()
Range(“A1”).Value = “Hello World”
End Sub

复制单元格内容

Sub CopyCell()
Range(“A1”).Copy
End Sub

粘贴单元格内容

Sub PasteCell()
Range(“B1”).PasteSpecial
End Sub

删除单元格内容

Sub DeleteCell()
Range(“A1”).ClearContents
End Sub

隐藏单元格

Sub HideCell()
Range(“A1”).EntireRow.Hidden = True
End Sub

显示单元格

Sub UnhideCell()
Range(“A1”).EntireRow.Hidden = False
End Sub

设置单元格格式

Sub SetFormat()
Range(“A1”).NumberFormat = “0.00”
End Sub

循环遍历单元格

Sub LoopCells()
Dim cell As Range
For Each cell In Range(“A1:A10”)
cell.Value = cell.Value * 2
Next cell
End Sub

插入新行或列

Sub InsertRow()
Rows(“2:2”).Insert Shift:=xlDown
End Sub

Sub InsertColumn()
Columns(“B:B”).Insert Shift:=xlToRight
End Sub

删除行或列

Sub DeleteRow()
Rows(“2:2”).Delete Shift:=xlUp
End Sub

Sub DeleteColumn()
Columns(“B:B”).Delete Shift:=xlToLeft
End Sub

查找和替换

Sub FindReplace()
Cells.Replace What:="old", Replacement:="new", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

打开文件对话框

Sub OpenFileDialog()
Dim fileDialog As FileDialog
Set fileDialog = Application.FileDialog(msoFileDialogOpen)
fileDialog.Show
End Sub

保存文件

Sub SaveFile()
ActiveWorkbook.Save
End Sub

关闭文件

Sub CloseFile()
ActiveWorkbook.Close
End Sub

打印

Sub Print()
ActiveSheet.PrintOut
End Sub

发送邮件

Sub SendEmail()
Dim outlookApp As Outlook.Application
Set outlookApp = New Outlook.Application
Dim outlookMail As Outlook.MailItem
Set outlookMail = outlookApp.CreateItem(olMailItem)
outlookMail.To = “recipient@example.com”
outlookMail.Subject = “Subject”
outlookMail.Body = “Body”
outlookMail.Send
End Sub

获取当前日期和时间

Sub GetCurrentDateTime()
Dim currentDateTime As Date
currentDateTime = Now
MsgBox currentDateTime
End Sub

获取单元格的值

Sub GetCellValue()
Dim cellValue As Variant
cellValue = Range(“A1”).Value
MsgBox cellValue
End Sub