编写VBA 程序,有些函数,非常常用。 特此记载,既为留存,也为各位excel爱好者,做以参考。
此篇会不断更新,也欢迎感兴趣的读者留言,以丰富此主题。
函数1: 去除单元格内空格,方便消除由于空格造成的影响
Private Function ClearSpace(sData As String) As String
ClearSpace =Replace(sData, ' ', '')
End Function
函数2:打开与本文件在同一目录下的excel文件
Private Sub Open_Excel_File(sWorkbookForOpen As String)
Workbooks.OpenApplication.ThisWorkbook.Path & '\'& sWorkbookForOpen
End Sub
函数3:删除区域内的重复数据 (区域的写法举例:A1:B3)
Private Sub Remove_Duplicates(sWorkbooksName As String,sSheetName As String, sRange As String)
Workbooks(sWorkbooksName).Sheets(sSheetName).Range(sRange).RemoveDuplicatesColumns:=1, Header:=xlYes
End Sub
函数4:为单元格添加公式
ActiveCell.FormulaR1C1 = '=COUNTA(论坛!C[7])-1'
函数5:将当前sheet变为活动页
Workbooks('Test.xlsm').Sheets('Test').Activate
函数6:删除excel文件中的sheet页
Private Sub Delete_sheet(sWorkbookName As String, sSheetNameAs String)
Application.DisplayAlerts= False '关掉屏幕警告信息
Workbooks(sWorkbookName).Sheets(sSheetName).Delete '删除sheet
Application.DisplayAlerts= True '打开屏幕警告信息
End Sub
函数7: 在某列左侧插入一列。其中sRange的写法举例:'P:P'
Private SubInsert_Column(sWorkbookNameAs String, sSheetName As String,sRange As String)
Workbooks(sWorkbooksName).Sheets(sSheetName).Activate
Columns(sRange).Select
Selection.InsertShift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
endSub
函数8:创建一个excel文件,并更名
Workbooks.Add
ActiveWorkbook.SaveAsFilename:=ThisWorkbook.Path & '\' &'test.xlsm'
函数9:在活动的workbook上创建一个新的sheet页并命名 (其中的变量为创建的sheet页的名字)
Private Sub creat_sheet(sSheetTemporary As String)
Dim wsWorksheet AsWorksheet
''查看是否已经含有temporarysheet,如果已经含有,则进行删除
For Each wsWorksheet In Worksheets
If wsWorksheet.Name = sSheetTemporary Then
Application.DisplayAlerts = False
wsWorksheet.Delete
Application.DisplayAlerts = True
End If
Next
''创建新的sheet页并命名
Sheets.Add.Name =sSheetTemporary
End Sub
函数10: 改变单元格内字体颜色
Workbooks(sWorkbooksCopyName).Sheets(sSheetsCopyName).Cells(iX,iY).Font.ColorIndex = 3
其中ColorIndex = 3是红色
函数11: 改变单元格内字体格式
Workbooks(sWorkbooksCopyName).Sheets(sSheetsCopyName).Cells(iX,iY).NumberFormat = '0.0'
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。