Tìm hiểu VBA - Visual Basic For Applications

Visual Basic For Applications cho phép người sử dụng một mức độ tùy biến xa hơn những gì là đặc trưng có sẵn trong các sản phẩm của Microsoft Office. Bạn có thể làm rất nhiều với Excel chỉ sử dụng các công thức nhưng đến một lúc khi bạn nhận thấy rằng mình đang làm một công việc tiêu chuẩn liên quan đến di chuyển hoặc sắp xếp dữ liệu và bạn không thể tìm thấy một cách dễ dàng để giảm nó vào một tập hợp các công thức. Thường thì nó chỉ là dễ dàng hơn để viết một chương trình tự động hóa các nhiệm vụ và Excel có một ngôn ngữ lập trình để dễ dàng sử dụng được xây dựng trong tất cả các phiên bản, VBA – Visual Basic cho các ứng dụng. VBA cho phép người sử dụng một mức độ tùy biến xa hơn những gì là đặc trưng có sẵn trong các sản phẩm Microsoft Office, chẳng hạn như Excel, Word và Power Point. Sử dụng các loại lệnh thành một module chỉnh sửa để tạo ra một Macro. Macros có thể cho phép người sử dụng một cách máy móc làm báo cáo tùy chỉnh, bảng xếp hạng và thực hiện các chức năng xử lý dữ liệu khác. Trong ngành công nghiệp tài chính, VBA Excel thường được sử dụng để cải thiện và duy trì mô hình bảng tính tài chính phức tạp. Bằng phương tiện của VBA, chúng ta có thể làm bất cứ điều gì gần như hình dung được với Excel. Microsoft Excel là một công cụ rất mạnh mẽ mà bạn có thể sử dụng để vận hành, phân tích và dữ liệu hiện tại. Mặc dù các tập hợp phong phú các tính năng trong giao diện người dùng chuẩn Excel, bạn có thể muốn tìm một cách dễ dàng hơn để thực hiện một thói quen, nhiệm vụ đơn điệu, hoặc để thực hiện một số nhiệm vụ mà giao diện người dùng dường như không giải quyết. Ngẫu nhiên, các ứng dụng văn phòng như Excel có Visual Basic cho các ứng dụng (VBA), một ngôn ngữ lập trình mà cung cấp cho bạn các thiết bị để lan truyền những ứng dụng. VBA hoạt động bằng cách thực hiện macro, step-by-step cách viết bằng Visual Basic. Nhiều người sử dụng khám phá ra rằng việc học thậm chí ở mức độ nhẹ của mã VBA làm cho công việc của họ dễ dàng hơn và cung cấp cho họ các kỹ năng để làm việc trong văn phòng mà họ đã không xem xét là có thể xảy ra. Một khi bạn đã học được một số VBA, nó quay ra được dễ dàng hơn nhiều để tìm hiểu rất nhiều đầy đủ nhiều hơn để các khách hàng tiềm năng ở đây là không giới hạn. Sử dụng VBA trong Excel là hệ thống hóa các nhiệm vụ lặp đi lặp lại. Ví dụ, bạn có một số lượng nhỏ các sách bài tập, trong số đó có một số lượng nhỏ các bảng và mỗi người trong những nhu cầu phải có một số thay đổi được thực hiện cho nó. Những thay đổi có thể đơn giản như việc áp dụng định dạng mới cho phạm vi cố định nhất định của tế bào hoặc khó khăn như nhìn vào một số tính năng đại số của các dữ liệu trên mỗi tờ, chọn loại tốt nhất của bảng xếp hạng để hiển thị dữ liệu với những đặc điểm, sau đó thực hiện và định dạng biểu đồ trong view đó.Một cách thay thế khác, nếu biết về VBA thì bạn có thể tự động hóa các nhiệm vụ bằng cách sử dụng VBA để viết lệnh rõ ràng cho Excel để theo. VBA Excel không chỉ dành cho các công việc đơn điệu. Bạn cũng có thể sử dụng VBA để xây dựng thành thạo với Excel ( bạn có thể tạo ra một công cụ mới để phân tích dữ liệu của bạn, sau đó sử dụng các khả năng biểu đồ trong Excel để hiển thị các kết quả) và để thực hiện các công việc lặt vặt có kết hợp với các ứng dụng của Office như Microsoft Access. Trong thực tế, tất cả các ứng dụng văn phòng Excel là được sử dụng giống với một nền tảng phát triển tổng thể. Hơn nữa để tất cả các nhiệm vụ có chứa danh sách và kế toán, các nhà thiết kế sử dụng Excel trong một loạt các nhiệm vụ từ dữ liệu trực quan để phần mềm tạo mẫu. Một số ứng dụng quan trọng của các macro: - Hiệu suất ổn định: giả sử ông chủ của bạn nói để làm báo cáo bán hàng vào cuối tháng và sau đó bạn có thể phát triển một chương trình trong excel. Bạn có thể hiển thị hiệu suất và sự xuất sắc báo cáo công việc hàng ngày của bạn với sự giúp đỡ của VBA. - Công việc lặp đi lặp lại và lệnh tùy chỉnh: nếu bạn có để thực hiện cùng một hành động được lặp lại nhiều lần trong excel thì bạn nên sử dụng các macro, bạn có thể làm cho công việc đơn điệu đó bằng một hoặc một số dòng lệnh duy nhất và bạn cũng có thể sửa đổi lệnh của bạn. Với sự giúp đỡ của vba công việc lặp đi lặp lại nhiều lần của bạn sẽ được thực hiện bằng dòng lệnh duy nhất do bạn viết ra. - Tạo thanh công cụ tùy chỉnh: Sử dụng macro và làm cho thanh công cụ tùy chỉnh của bạn với các điều khiển của riêng bạn trong excel. - VBA có thể được sử dụng để tính toán "phức tạp" cho một hoặc rất nhiều công việc của bạn.

Unprotect All sheet in Excel

Khi dùng excel bạn bảo vệ sheet (Protect sheet) nhưng chẳng may quên mật khẩu bài viết này giúp bạn gỡ bỏ mật khẩu bảo vệ sheet (unprotect sheet) trong trường hợp này. Cách làm: - Mở trình soạn thảo VBA bằng cách vào Tab Developer > Visual Basic - Trong trình soạn thảo VBA vào Insert > Module - Paste đoạn code vba vào Module vừa tạo rồi bấn F5 trên bàn phím để thực thi đoạn code vba: Sub unprotectedAll() Dim i As Integer For i = 1 To Application.Sheets.Count PasswordBreaker Application.Sheets(i) Next End Sub Sub PasswordBreaker(MySheet) Dim pass As String If MySheet.ProtectContents = True Then Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If ActiveSheet.ProtectContents = False Then MsgBox "One usable password is " & Chr(i) & Chr(j) & _ Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) Exit Sub End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next End If End Sub Chúc bạn thành công!

Cải thiện tốc độ phản hồi của Macro VBA

Khi chạy một đoạn macro VBA bạn có thể thấy màn hình chớp nháy hoặc gặp hiện tượng đơ máy... bài viết này sẽ giúp bạn khắc phục được các hiện tượng trên giúp công việc của bạn mượt mà hơn ^-^. Đầu tiên các bạn tạo một Module có nội dung:

Public CalcState As Long Public EventState As Boolean Public PageBreakState As Boolean '//Tat bo mot thiet lap quan trong trong Office: tan dung bo nho may tinh de lien tuc cap nhat hoat dong cua macro len man hinh (theo thoi gian thuc) Sub OptimizeCode_Begin() Application.ScreenUpdating = False EventState = Application.EnableEvents Application.EnableEvents = False CalcState = Application.Calculation Application.Calculation = xlCalculationManual PageBreakState = ActiveSheet.DisplayPageBreaks ActiveSheet.DisplayPageBreaks = False End Sub '//========================================================== Sub OptimizeCode_End() ActiveSheet.DisplayPageBreaks = PageBreakState Application.Calculation = CalcState Application.EnableEvents = EventState Application.ScreenUpdating = True End Sub

Cách sử dụng: Khi các bạn viết macro thì cấu trúc sẽ như sau:

Sub YourMacro() '//Optimize Code Call OptimizeCode_Begin '//Bạn đặt macro bạn muốn sử dụng ở đây your macro code here... '//Optimize Code Call OptimizeCode_End End Sub

Chúc bạn thành công!

Tổng hợp cách gán thuộc tính Rowsource của combobox

Cách 1: gán từng item cho ComboBox1 bằng đoạn code dưới đây:

With ComboBox1
    .AddItem "Item 1"
    .AddItem "Item 2"
    .AddItem "Item 3"
    .AddItem "Item 4"
End With

Cách 2: Khai báo Rowsource trong Properties của ComboBox, chọn ComboBox1 trong cửa sổ Properties của ComboBox ta khai báo tại dòng Rowsource. Ví dụ: Nhaplieu!B13:B20 trong đó Nhaplieu là tên sheet. Cách 3: Để làm cách này đầu tiên các bạn khai báo 2 biến Arr và lr_dongcuoi và code cho ComboBox như sau:

    Dim lr_SoPhieu As Long
    Dim Arr
    lr_dongcuoi = ThisWorkbook.Worksheets("NhapLieu").Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    Arr = ThisWorkbook.Worksheets("NhapLieu").Range("B12:B" & lr_SoPhieu).Value
    With ComboBox1
        .List = Arr
    End With

Trong đó NhapLieu là tên sheet, với cách này dữ liệu sẽ tự động cập nhật khi bạn có thêm nhiều dòng dữ liệu muốn đưa vào ComboBox, trong ví dụ này mình thêm dữ liệu tại cột B sheet NhapLieu bắt đầu từ dòng B12 cho đến dòng cuối của cột B có chứa dữ liệu ^-^ Chúc bạn thành công!

Ẩn TitleBar userform trong vba excel

 Cách ẩn TitleBar của Userform, nhiều khi bạn muốn ẩn Button close mặc định của userform để nhìn cho nó ngầu :) thì bài này cũng sẽ giúp bạn thực hiện được điều đó luôn.

Giờ bắt đầu thôi, đầu tiên bạn tạo một Module  có nội dung như sau:

  Option Explicit
Option Private Module

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
#If VBA7 Then
    Public Declare PtrSafe Function GetWindowLong _
           Lib "user32" Alias "GetWindowLongA" ( _
           ByVal hWnd As LongPtr, _
           ByVal nIndex As Long) As Long
    Public Declare PtrSafe Function SetWindowLong _
           Lib "user32" Alias "SetWindowLongA" ( _
           ByVal hWnd As LongPtr, _
           ByVal nIndex As Long, _
           ByVal dwNewLong As Long) As Long
    Public Declare PtrSafe Function DrawMenuBar _
           Lib "user32" ( _
           ByVal hWnd As LongPtr) As Long
    Public Declare PtrSafe Function FindWindowA _
           Lib "user32" (ByVal lpClassName As String, _
                         ByVal lpWindowName As String) As LongPtr
#Else
    Public Declare Function GetWindowLong _
                         Lib "user32" Alias "GetWindowLongA" ( _
                             ByVal hWnd As Long, _
                             ByVal nIndex As Long) As Long
    Public Declare Function SetWindowLong _
                         Lib "user32" Alias "SetWindowLongA" ( _
                             ByVal hWnd As Long, _
                             ByVal nIndex As Long, _
                             ByVal dwNewLong As Long) As Long
    Public Declare Function DrawMenuBar _
                         Lib "user32" ( _
                             ByVal hWnd As Long) As Long
    Public Declare Function FindWindowA _
                         Lib "user32" (ByVal lpClassName As String, _
                                       ByVal lpWindowName As String) As Long
#End If

Sub HideTitleBar(frm As Object)
    #If VBA7 Then
        Dim lFrmHdl As LongPtr
    #Else
        Dim lFrmHdl As Long
    #End If
    Dim lngWindow As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

Sub ShowTitleBar(frm As Object)
    #If VBA7 Then
        Dim lFrmHdl As LongPtr
    #Else
        Dim lFrmHdl As Long
    #End If
    Dim lngWindow As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow + (WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

sau đó trong userform bạn chỉ cần dùng đoạn code dưới đây để thực thi câu lệnh trong Module mà bạn vừa tạo ở trên:

  Private Sub UserForm_Initialize()
    HideTitleBar Me
End Sub

Chú ý:Trong Userform của bạn cần tạo một CommandButton để Close from có nội dung:

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Hehe giờ thì tận hưởng sự ngầu của bạn đi nào.

Biến và cách khai báo biến trong VBA excel

 1. Các loại biến VBA:

- Các kiểu giá trị của biến phổ biến thường dùng: String , Boolean , Integer và Double là 4 trong số các kiểu giá trị của biến VBA hay dùng nhất. 4 kiểu giá trị này sẽ là tất cả những gì bạn cần cho hơn 90% macro VBA của mình.

2. Cách khai báo các biến:

Để khai báo biến ta bắt đầu bằng từ khóa Dim (Private hoặc Public) as [Kiểu dữ liệu]

Ví dụ: Dim VariableName as DataType

Dim là viết tắt của Dimension. 

DataType là các kiểu dữ liệu phổ biến nhất đã nêu ở trên.

VariableName là tên biến của bạn, bạn có thể đặt tên tùy ý bạn muốn.

Chú ý quy tắc đặt tên biến:

- Tên biến phải bắt đầu bằng ký tự chữ (text) không được bắt đầu bằng số.

- Tên biến không được vượt quá 255 ký tự.

- Tên biến không được trùng với tên với các từ khóa trong VBA;

- Tên biến không được chứa khoảng trắng (dấu cách hay Space);

- Tên biến không chứa các ký tự đặc biệt;

-Tên biến không được vợt quá 255 ký tự.

Nếu bạn muốn tên biến tường minh rõ ý để sau này dễ theo dõi và sửa chữa có thể thêm dấu gạch dưới (_) hoặc chữ in hoa để ngăn cách ví dụ: bien_so_1, BienSo1

Tên các biến VBA không phân biệt chữ hoa chữ thường. Ví dụ: strDog và strdog là một nếu bạn đặt tên biến giống nhau VBA sẽ thông báo lỗi Duplicate

Không có quy tắc đặt tên "chính xác" cho các biến trong VBA. Bạn có thể tự thiết lập quy ước của riêng mình, nhưng hãy đảm bảo sử dụng nó một cách nhất quán. Quy ước đặt tên biến nhất quán giúp bạn và những người khác đọc macro của bạn một cách dễ dàng hơn.

Ví dụ quy ước đặt tên của tôi cho các kiểu dữ liệu phổ biến nhất như sau:

Tên biến chuỗi bắt đầu bằng str (VD: strEmployee)

Tên biến Boolean bắt đầu bằng b (VD: bFlag)

Tên biến số nguyên bắt đầu bằng i (VD: iCount)

Tên biến kép bắt đầu bằng d (VD: dSalary)

Câu lệnh IF THEN cơ bản trong VBA Excel

Câu lệnh IF THEN thực hiện có điều kiện một nhóm hành động, tùy thuộc vào giá trị của một biểu thức. Chúng tuân theo một định dạng nhất quán và có thể dự đoán được. Hãy xem qua cú pháp cho dạng cơ bản nhất của câu lệnh IF THEN:

Copy

	If [điều kiện là đúng] THEN
		[làm việc gì đó]
	End If

Đây là dạng đơn giản nhất của câu lệnh IF THEN. Điều tối quan trọng là bạn phải nhớ đặt từ khóa End If ở cuối câu lệnh If của mình. Mở Visual Basic Editor (Alt+F11) của bạn Insert Module và nhập ví dụ sau:

Copy

Sub IF_THEN_DEMO()
	If Range("A1") = "" Then
		Range("B1") = "A1 is blank"
	End If
End Sub

Ví dụ này kiểm tra xem giá trị trong ô A1 có trống hay không. Nếu đúng, dòng chữ "A1 is blank" sẽ xuất hiện trong ô B1. Câu lệnh IF THEN ELSE Câu lệnh IF THEN ELSE để kiểm tra xem điều kiện có được đáp ứng hay không và nó cho macro của bạn biết phải làm gì nếu điều kiện không được đáp ứng. Nó có dạng như sau:

Copy

If [điều kiện là đúng] then
	[làm việc gì đó]
else
	[làm việc gì khác]
End If

Về cơ bản, nếu điều kiện mà bạn đã kiểm tra không đúng, các hành động trong phần [làm việc gì khác] sẽ được thực hiện. Bây giờ sửa đổi ví dụ ở trên để minh họa cách phần ELSE được thực hiện:

Copy

Sub IF_THEN_DEMO ()
	If Range("A1") > 10 Then
		Range("B1") = "A1 lớn hơn 10"
	else
		Range ("B1") = "A1 KHÔNG lớn hơn 10"
	End If
End Sub

Ví dụ này xem xét giá trị trong ô A1. Nếu một số lớn hơn 10 (>) được nhập vào ô đó, ô B1 sẽ thay đổi để cho bạn biết như vậy. Nếu nó không lớn hơn 10, ô B1 sẽ cho bạn biết giá trị trong ô A1 không lớn hơn 10. Bạn thay đổi với các số khác nhau trong ô A1 và tự mình xem sự khác biệt. Chỉ cần thay đổi số trong ô A1, sau đó chạy macro IF_THEN_DEMO bằng cách nhấn F5 từ trình chỉnh sửa VBA của bạn. Đến đây bạn có thể hình dung câu lệnh IF THEN cũng tương tự hàm if trong excel phải không nào?

IF THEN ELSEIF (Câu lệnh If lồng)Hãy thêm một lớp phức tạp nữa vào các câu lệnh IF của chúng ta: câu lệnh ELSEIF. Các câu lệnh ELSEIF cho phép bạn kiểm tra một loạt các điều kiện trong cùng một khối "IF THEN". Nó trông như thế này:

Copy

If [điều kiện 1 là đúng] Then
    [làm việc gì đó]
ElseIf [Điều kiện 2 là đúng] Then
    [làm việc gì khác]
ElseIf [Điều kiện 3 là đúng] Then
    [làm việc gì khác]
End If

Bạn có thể có bao nhiêu ELSEIF tùy thích (tất nhiên là phải có lý do), có nghĩa là bạn có thể kiểm tra một loạt các điều kiện khác nhau, như sau:

Copy

Sub IF_THEN_DEMO()
If Range("A2") > 10 Then
    Range("B2") = "A2 lớn hơn 10"
ElseIf Range("A2") > 5 Then
    Range("B2") = "A2 lớn hơn 5"
ElseIf IsEmpty(Range("A2")) Then
    Range("B2") = "A2 is empty"
End If
End Sub

Ví dụ này kiểm tra ba điều kiện từ trên xuống dưới, nhưng nó chỉ đi xuống nhánh [làm việc gì đó] một lần. Ví dụ: giả sử bạn có giá trị là "12" trong ô A2. Số 12 lớn hơn 10 và lớn hơn 5, vì vậy có vẻ như cả hai điều kiện đầu tiên đều được đáp ứng, phải không? Tuy nhiên, vì VBA dừng ở điều kiện đầu tiên đúng , macro sẽ không đi xuống 2 điều kiện còn lại. Khi bạn chạy macro, đầu tiên nó sẽ kiểm tra xem số 12 có lớn hơn 10. Chính vì vậy macro ghi ra "A2 lớn hơn 10" trong ô B2 và nó ngay lập tức nhảy đến dòng End If ở dưới cùng. Nó thậm chí không bao giờ kiểm tra xem giá trị ở A2 có lớn hơn 5 hay không! Đó là một khái niệm rất quan trọng cần nhớ, và nó là một khái niệm mà nhiều lập trình viên thường quên. Nếu giá trị trong ô A2 lớn hơn 5, nhưng nhỏ hơn hoặc bằng 10, macro sẽ đi xuống nhánh thứ hai và báo cáo "A2 lớn hơn 5". Vì VBA kiểm tra các điều kiện tuần tự, chúng tôi thậm chí không cần phải yêu cầu nó xác nhận giá trị cũng nhỏ hơn hoặc bằng 10! Đó là một cách tiết kiệm thời gian nhỏ nhưng tiện dụng. Lưu ý rằng điều kiện thứ ba kiểm tra xem ô A2 có trống không. Lệnh IsEmpty có thể là mới đối với bạn. Đó là một trong nhiều chức năng được tích hợp sẵn mà VBA có để làm cho công việc lập trình vba của bạn trở nên dễ dàng hơn. Chúng ta có thể kết hợp tất cả những điều này lại với nhau không? Câu trả lời là có, bạn có thể bao gồm câu lệnh ELSE khi sử dụng câu lệnh ELSEIF, nhưng câu lệnh ELSE phải đi sau câu lệnh ELSEIF . Đây là cú pháp:

Copy

If [điều kiện 1 là đúng] then
	[làm việc gì đó]
elseIf [Điều kiện 2 là đúng] then
	[làm việc gì khác]
ElseIf [Điều kiện 3 là đúng] then
	[làm việc gì khác]
else
    [làm việc gì khác]
End If

Thêm một câu lệnh ELSE vào ví dụ trước của chúng ta và nhập "3" vào ô A2.

Copy

Sub IF_THEN_DEMO ()
If range("A2")> 10 then
	range("B2") = "A2 lớn hơn 10"
ElseIf Range("A2")> 5 then
	range("B2") = "A2 lớn hơn 5"
ElseIf IsEmpty (range("A2")) then
	Range("B2") = "A2 is empty"
else
	range("B2") = "A2 is <= 5 and not empty"
End If
End Sub

Chạy macro với số 3 trong ô A2 và chuỗi trong câu lệnh ELSE sẽ xuất hiện trong ô B2. Các "hành động" ELSE chỉ được thực hiện nếu không có điều kiện nào khác được thỏa mãn. Nói cách khác, nếu không có gì khác đúng, thì điều kiện ELSE được thực hiện. Chú ý, nếu bạn vô tình cố gắng đặt câu lệnh ELSE trước câu lệnh ElseIf bạn sẽ gặp một thông báo lỗi nhỏ của excel. Lúc này bạn chỉ cần click OK và sửa lại cấu trúng câu lệnh If lại cho đúng là được.