VBA编程常用功能

Private Sub sub( byval strFilePaht as string byval strCellRange as string)
      Dim wb As Workbook
      Dim aRow As Integer
On Error GoTo error_handle
       set wb = Workbooks.Open("F:/test/Data.xlsx")
       aRow = wb.Sheets("Sheet1").Range("a65536").End(xlUp).Row
       wb.Worksheets("Sheet1").Range("a1:ap" & aRow).Copy ThisWorkbook.Sheets(1).Range("a1")
       wb.Close (False)
       MsgBox "Open Sucessd"
       Exit Sub
error_handle:
       MsgBox "Open Failed"

End Sub

 

 

rem 定义一个函数时设置默认值

Public Sub test(Optional ByVal strTest1 As String = "", Optional ByVal strTest As String = "abc")

           MsgBox strTest1 & strTest
End Sub

 

 

rem 设置进度条实例

 

按钮驱动

Private Sub ProcessAdd_Click()
    If ProcessBar.Value < 10 Then
    ProcessBar.Value = ProcessBar.Value + 1
    Else
    ProcessBar.Value = 0
    End If
End Sub

rem 进度条初始化

Private Sub UserForm_Initialize()
         ProcessBar.Max = 10
         ProcessBar.Min = 0
End Sub

 

excel 中调用

Private Sub CommandButton2_Click()
     Load ProcessWnd
     ProcessWnd.Show

End Sub

 

 

保护工作薄

Sub ProtectWorkbook()
  MsgBox "保护工作簿结构,密码为123"
'设置密码来保护工作簿结构
  ActiveWorkbook.Protect Password:="123", Structure:=True
  MsgBox "保护工作簿窗口,密码为123"
'设置密码来保护工作簿窗口
  ActiveWorkbook.Protect Password:="123", Windows:=True
  MsgBox "保护工作簿结构和窗口,密码为123"
'设置密码来同时保护工作簿结构和窗口
  ActiveWorkbook.Protect Password:="123", Structure:=True, Windows:=True
End Sub

 

 

Sub UnprotectWorkbook()
  MsgBox "取消工作簿保护"
'解除工作簿保护
  ActiveWorkbook.Unprotect "123"
End Sub

 

Sub UsePassword()
'声明一个工作簿对象变量wb
  Dim wb As Workbook
'指定wb为当前工作簿
  Set wb = Application.ActiveWorkbook
  wb.Password = InputBox("请输入密码:")
  wb.Close
End Sub

 

 

rem 从关闭的文件中获取数据

 

Sub testGetValuesFromClosedWorkbook()
  GetValuesFromAClosedWorkbook "F:/test", "Data.xlsx", "Sheet1", "A1:G20"
End Sub
'
Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As String, sName, cellRange As String)
  With ActiveSheet.Range(cellRange)
    .FormulaArray = "='" & fPath & "/[" & fName & "]" _
                    & sName & "'!" & cellRange
    .Value = .Value
  End With
End Sub

Private Sub CommandButton1_Click()
testGetValuesFromClosedWorkbook
End Sub