Thủ thuật Excel

[levanduyet]

Rất tiếc!!! Không thể upload lên hình ảnh được. Mong thông cảm.
Xin các bạn góp ý về
levanduyet@yahoo.com

DÙNG VBA TRONG EXCEL ĐỂ TẠO VÀ SỬA CHỮA PIVOT TABLE.
Chức năng pivot table là chức năng mạnh của Excel. Chức năng này đầu tiên xuất hiện trong Excel 5. Nó giúp cho bạn tổng kết số liệu nhanh một cách kinh ngạc.

Trong bài viết này tôi giả sử rằng các bạn đã làm quen với việc tạo vào sửa chữa pivot table một cách thủ công và bài viết này viết cho Excel 2000. Tôi sẽ hướng dẫn cho các bạn dùng VBA để tạo và sửa chữa pivot table một cách linh động.

Giả sử ở sheet1 tôi có khối dữ liệu cần phân tích như hình1. Khối dữ liệu này gồm các trường: SalesRep (đại diện bán hàng), Region (Vùng), Month (Tháng), Sales (doanh số bán).



Trước khi tạo bảng pivot table như hình 2, tôi đã chọn Record New Macro... như hình 3, để xem đoạn mã được ghi lại như thế nào.





Sau đó tôi vào cửa sổ màn hình VBE bằng cách nhấn tổ hợp phím Alt + F11. Tôi vào Module1, tôi sẽ thấy được đoạn mã như sau:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 17/03/2003 by Duyet
'
Range("A113").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!R1C1:R13C4").CreatePivotTable TableDestination:=Range("A1"), TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="SalesRep", ColumnFields:="Month", PageFields:="Region"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = xlDataField
End Sub


Với chú ý:

Region là trường page trong pivot table.
SalesRep là trường row trong pivot table.
Month là trường column trong pivot table.
Sales là trường data trong pivot table sử dụng hàm Sum

Khảo sát đoạn mã đã được ghi:

Để khảo sát đoạn mã trên bạn cần phải biết một số đối tượng liên quan. Tất cả các đối tượng này đều được giải thích trên online help.

PivotCaches là tập họp các đối tượng PivotCache trong đối tượng Workbook
PivotTables là tập họp các đối tượng PivotTable trong đối tượng Workbook
PivotTableFields là tập họp các trường trong đối tượng PivotTable
CreatePivotTable một phương thức của đối tượng PivotCache để tạo một pivot table sử dụng dữ liệu trong một pivot cache

Ta có thể viết lại thủ tục trên như thủ tục CreatePivotTable (chú ý bạn nhập thủ tục này vào trong module1) sau đây, có thể nó hơi dài nhưng sẽ dễ hiểu hơn, và bạn có thể chạy chương trình bất cứ đâu bằng cách nhấn tổ hợp phím Alt + F8, sau đó chọn thủ tục CreatePivotTable và chọn Run như hình 4 sau:



Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Application.ScreenUpdating = False
' Xoa PivotSheet neu no ton tai
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
' Tao Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion.Address)
' Tao worksheet moi va dat ten
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
' Tao Pivot Table tu Cache
Set PT = PTCache.CreatePivotTable (TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="PivotTable1")
With PT
' Them cac truong
.PivotFields("Region").Orientation = xlPageField
.PivotFields("Month").Orientation = xlColumnField
.PivotFields("SalesRep").Orientation = xlRowField
.PivotFields("Sales").Orientation = xlRowField
Application.ScreenUpdating = True
End With
End Sub
Khi bạn chạy xong thủ tục trên thì bạn cũng sẽ được một pivot table như ở sheet2, nhưng trong trường hợp này thì là sheet có tên PivotSheet.(Hình5)

Bạn chú ý bạn sẽ thấy sự khác biệt của 2 đoạn mã trên. Trong Macro1 khi sử dụng phương thức Add để tạo pivot cache thì SourceData là "Sheet1!R1C1:R13C4" còn trong đoạn mã tôi viết là Sheets("Sheet1").Range("A1").CurrentRegion.Address . Ở đây tôi dùng thuộc tính Current Region, có nghĩa là dữ liệu chúng ta sử dụng dựa trên vùng hiện tại xung quanh ô A1. Điều này để chắc chắn rằng thủ tục CreatePivotTable vẫn tiếp tục làm việc tốt khi chúng ta thêm vào dữ liệu.

Bây giờ giả sử tôi có thêm trường Target (chỉ tiêu) trong khối dữ liệu của tôi, và trong pivot table tôi sẽ đưa thêm trường target vào đồng thời cũng thêm trường tính toán Variance. Trường này (Variance) sẽ bằng Sales - Target. Khối dữ liệu mới của tôi như hình 6.


Đoạn mã trong thủ tục CreatePivotTable trên sẽ được thêm như sau: (Tôi chỉ thêm trong đoạn With PT ....End With)

With PT
' Them cac truong
.PivotFields("Region").Orientation = xlPageField
.PivotFields("Month").Orientation = xlColumnField
.PivotFields("SalesRep").Orientation = xlRowField
.PivotFields("Sales").Orientation = xlDataField
.PivotFields("Target").Orientation = xlDataField
' Them truong tinh toan
.CalculatedFields.Add "Variance", "=Sales - Target"
.PivotFields("Variance").Orientation = xlDataField
' Thay doi caption
.PivotFields("Sum of Sales").Caption = "Sales ($) "
.PivotFields("Sum of Target").Caption = "Target ($) "
.PivotFields("Sum of Variance").Caption = "Variance ($) "
End With


Sau khi chạy lại thủ tục trên tôi sẽ được như hình 7 sau:



Giả sử bây giờ dữ liệu của tôi gồm 6 tháng (hình 8), tôi muốn đưa thêm cột tổng theo từng 3 tháng. Vậy tôi phải sửa lại đoạn mã của mình như sau:



With PT
' Them cac truong
.PivotFields("Region").Orientation = xlPageField
.PivotFields("Month").Orientation = xlColumnField
.PivotFields("SalesRep").Orientation = xlRowField
.PivotFields("Sales").Orientation = xlDataField
.PivotFields("Target").Orientation = xlDataField
' Them truong tinh toan
.CalculatedFields.Add "Variance", "=Sales - Target"
.PivotFields("Variance").Orientation = xlDataField
' Them muc tinh toan
.PivotFields("Month").CalculatedItems.Add "Q1", "= thang 1 + thang 2 + thang 3"
.PivotFields("Month").CalculatedItems.Add "Q2", "= thang 4 + thang 5 + thang 6"
' Di chuyen cac muc tinh toan
.PivotFields("Month").PivotItems("Q1").Position = 4
.PivotFields("Month").PivotItems("Q2").Position = 8
' Thay doi caption
.PivotFields("Sum of Sales").Caption = "Sales ($) "
.PivotFields("Sum of Target").Caption = "Target ($) "
.PivotFields("Sum of Variance").Caption = "Variance ($) "
End With


Sau khi chạy lại thủ tục CreatePivotTable tôi sẽ được kết quả như hình 9.



Vâng, và đến đây các bạn thấy đó, nếu chúng ta biết sử dụng VBA thì công việc phân tích dữ liệu của bạn sẽ trở nên đơn giản hơn chỉ cần vài dòng chỉnh sửa mã. Ngoài ra ta cũng có thể tạo một pivot table từ nguồn dữ liệu bên ngoài như Access chẳng hạn. Để cho việc lập trình về pivot table được tốt, tôi đề nghị các bạn nên đọc phần online help của Excel về các đối tượng, phương thức, thuộc tính mà tôi đã đề cập ở trên.

Hy vọng rằng bài viết trên sẽ giúp các bạn một phần nào trong công việc.

Mọi góp ý, xin các bạn gởi về levanduyet@pmail.vnn.vn

Lê Văn Duyệt.

 

[levanduyet]

Chào các bạn,
Bài này sẽ giúp các bạn trong công việc chỉnh sửa các form báo cáo.

Thay đổi độ rộng cuả cột và chiều cao của hàng với đơn vị milimet

Có bao giờ các bạn phải thay đổi chiều cao của cột hay độ rộng của hàng nhằm định dạng tài liệu các bạn in ra hay không? Các bạn có cảm thấy rắc rối hay không?

Tôi xin giới thiệu các bạn cách làm bằng VBA:

Đầu tiên các bạn copy đoạn mã sau vào module của bạn (tên module tùy bạn):

Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' Thay doi chieu rong cot sang mm
Dim w As Single
If ColNo < 1 Or ColNo > 255 Then Exit Sub
Application.ScreenUpdating = False
w = Application.CentimetersToPoints(mmWidth / 10)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
Wend
End Sub
Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' Thay doi chieu cao cot sang mm
If RowNo < 1 Or RowNo > 65536 Then Exit Sub
Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub



Giả sử rằng bạn muốn rằng khi Right Click vào một ô nào ở sheet1 để thay đổi độ rộng của cột hay độ cao của hàng thì bạn hãy chép đoạn mã sau và dán vào mã của sheet1:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim bNumber As Integer, bText As String
' Dung phuong thuc (Method) INPUTBOX chi de nhan so thoi
On Error GoTo Thongbao1
bText = Application.InputBox("Ban muon thay doi do Rong cot hay chieu Cao cua hang (R/C)?" , "Ban chi nhap vao R hay C", 2)
bText = UCase(bText)
Select Case bText
Case "R"
On Error GoTo Thongbao2
bNumber = Application.InputBox("Xin ban nhap vao do rong cua cot (mm)" , "Ban chi nhap vao so tu nhien thoi", 1)
Call SetColumnWidthMM(Target.Column, bNumber)
Case "C"
On Error GoTo Thongbao2
bNumber = Application.InputBox("Xin ban nhap vao do cao cua hang (mm)" , "Ban chi nhap vao so tu nhien thoi", 1)
Call SetRowHeightMM(Target.Row, bNumber)
Case Else
On Error GoTo Thongbao2
MsgBox "Xin ban chi nhap vao R hay C ma thoi !", vbOKOnly, "Thong bao"
End Select
Cancel = True
Exit Sub
Thongbao1:
MsgBox "Ban chi go vao R (Rong) hay C (Cao) thoi !", vbOKOnly, "Thong bao"
Cancel = True
Exit Sub
Thongbao2:
MsgBox "Ban chi go vao so ma thoi !", vbOKOnly, "Thong bao"
Cancel = True
Exit Sub
End Sub


Vậy, bây giờ bạn có thể trở về màn hình Excel để thử xem sao.

Chúc các bạn thành công.

levanduyet@yahoo.com

 

[levanduyet]

Hàm chấm công đây....Rất tiếc!!! Không có hình ảnh!!!
levanduyet@yahoo.com

Tạo hàm chấm công.

Vấn đề: ở một số công ty, nhà máy nhỏ việc chấm công thông thường được thực hiện trên Excel. Qua trao đổi tôi thấy rằng đa số khi nhân viên có đi làm thì sẽ được chấm là X , nghĩ phép là P , ...vv. Sau đó sẽ sử dụng một số hàm trong Excel để tính số ngày công trong một tháng của nhân viên. Tôi nghĩ rằng thông thường số ngày làm việc sẽ nhiều hơn số ngày nghĩ, nếu chấm công như trên nghĩa là bạn phải nhập vào bảng tính Excel nhiều hơn nếu bạn chỉ nhập vào những ngày nghĩ của nhân viên ấy. Giả sử rằng trong một ngày nhân viên nghĩ phép ½ ngày, sau đó do nhu cầu công việc yêu cầu họ tăng ca 3 tiếng thì công việc chấm công sẽ như thế nào. Tôi thấy một số nơi sẽ đưa việc chấm tăng ca qua một sheet khác, hay bảng dữ liệu khác...Hoặc có những ngày thì tính công nhật (công thời gian), có những ngày tính theo năng suất...thì bạn phải làm như thế nào. Tôi nghĩ ở một số công ty việc chấm công sẽ rắc rối hơn như trên vì hiện nay ở một số công ty có cả công nhân thời vụ và công nhân chính thức,...!

Vâng, vấn đề chắc chắn là rắc rối. Ở đây tôi không có tham vọng giải quyết tất cả các vấn đề trên. Tôi chỉ muốn đưa ra hàm chấm công để cho công việc chấm công dễ dàng hơn.

Cấu trúc của hàm chấm công như sau:

Public Function Chamcong(ByVal Khoang As Range, ByVal Chucnang As String) As Single
_ Khoang: là khoảng (Thông thường là một hàng từ cột...đến cột...) tương ứng với hàng hay khoảng mà bạn chấm công cho một nhân viên.

_ Chucnang: là chuổi hay ký tự đại diện cho một chức năng chấm công của bạn.

Giả sử ở đây tôi quy định:

Nghĩ phép là: A

Nghĩ bệnh là: S

Nghĩ ma chay là: C

Nghĩ không lương là: U

Tăng ca ngày thường (tính nhân cho 1.5) là: N

Tăng ca ngày nghĩ (tính nhân cho 2) là: D

Vậy nếu trong một ngày nhân viên đó nghĩ phép ½ ngày, tăng ca (ngày thường) 3 tiếng thì tôi sẽ chấm như sau: (Giả sử tên nhân viên là Triệu Nhất Đông, ngày áp dụng là 27/02/2002)

Ở đây việc chấm công cho nghĩ phép và tăng ca sẽ được cách nhau bởi “ ; “ (dấu chấm phẩy, nếu bạn muốn bạn cũng có thể thay đổi ký tự trên khi bạn khai báo để xử lý trong hàm chấm công).

Trong hàm chấm công của tôi, tôi có sử dụng một Class tên clsString của Thấy Lê Đức Hồng (HTTP: www.vovisoft.com). Bạn chỉ việc copy đoạn mã sau và đưa vào class module trong cửa sổ VBE của bạn.





Author: Le Duc Hong http://www.vovisoft.com
Option Explicit
Private SText As String
Private SDelimiter As String
Private IPos As Integer
Private ILen As Integer
Public MaxToken As Integer
Private Tokens() As String
Public Property Get Text() As Variant
Text = SText
End Property
Public Property Let Text(ByVal vNewValue As Variant)
SText = vNewValue
ILen = Len(SText): IPos = 1
End Property
Public Property Get Delimiter() As Variant
Delimiter = SDelimiter
End Property
Public Function TokenAt(TNum) As String
If (TNum > 0) And (TNum <= MaxToken) Then
TokenAt = Tokens(TNum)
Else
TokenAt = ""
End If
End Function
Public Property Let Delimiter(ByVal vNewValue As Variant)
SDelimiter = vNewValue
Tokenise
End Property
Private Sub Tokenise()
Dim i
i = 0: IPos = 1
Do Until IPos > ILen
i = i + 1
ReDim Preserve Tokens(i)
Tokens(i) = GetToken
Loop
MaxToken = i
IPos = 1
End Sub
Public Sub ReplaceToken(TNum, NewToken)
If (TNum > 0) And (TNum <= MaxToken) Then
Tokens(TNum) = NewToken
ReconstructText
End If
End Sub
Private Sub ReconstructText()
Dim i
SText = ""
For i = 1 To MaxToken
SText = SText & Tokens(i)
If i < MaxToken Then SText = SText & SDelimiter
Next
End Sub
Public Function KeepLeftPart(NumChar) As String
If ILen >= NumChar Then
SText = Left(SText, NumChar): ILen = Len(SText)
End If
KeepLeftPart = SText
End Function
Public Function KeepRightPart(NumChar) As String
If ILen >= NumChar Then
SText = Right(SText, NumChar): ILen = Len(SText)
End If
KeepRightPart = SText
End Function
Public Function KeepMidPart(SPos, NumChar) As String
If ILen >= SPos Then
SText = Mid(SText, SPos): ILen = Len(SText)
End If
If ILen >= NumChar Then
SText = Right(SText, NumChar)
End If
KeepMidPart = SText
End Function
Public Property Get CurrentPos() As Variant
CurrentPos = IPos
End Property
Public Property Let CurrentPos(ByVal vNewValue As Variant)
IPos = vNewValue
End Property
Public Function GetToken() As String
Dim Pos
GetToken = ""
If SDelimiter = " " Then
Do While Mid(SText, IPos, 1) = " "
IPos = IPos + 1
If IPos > ILen Then
Exit Function
End If
Loop
End If
Pos = InStr(IPos, SText, SDelimiter)
If Pos > 0 Then
GetToken = Mid(SText, IPos, Pos - IPos)
IPos = Pos + Len(SDelimiter)
Else
GetToken = Mid(SText, IPos, ILen - IPos + 1)
IPos = ILen + 1
End If
End Function
Public Sub Substitude(Param, ParamValue)
Dim Pos, PLen
PLen = Len(Param)
Pos = InStr(SText, Param)
Do While Pos > 0
SText = Left(SText, Pos - 1) & ParamValue & Mid(SText, Pos + PLen)
Pos = InStr(SText, Param)
Loop
ILen = Len(SText)
End Sub
Public Function GetLastToken() As String
Dim Pos, Tlen
Tlen = Len(SDelimiter)
GetLastToken = ""
If ILen = 0 Then
Exit Function
End If
Pos = ILen - Tlen + 1
Do While Pos > 0
If Mid(SText, Pos, Tlen) = SDelimiter Then
GetLastToken = Mid(SText, Pos + Tlen)
End If
Pos = Pos - 1
Loop
End Function
Public Property Get Length() As Integer
Length = ILen
End Property
Public Property Get TokenCount() As Variant
TokenCount = MaxToken
End Property

Sau đó bạn bạn copy đoạn mã hàm chấm công sau vào trong module của bạn.
Public Function Chamcong(ByVal Khoang As Range, ByVal Chucnang As String) As Single
Dim Socot As Integer, Sohang As Integer
Dim i As Integer, j As Integer, k As Integer
Dim Btotal As Single
Dim Bgiatriso As Single
Dim Bchucnang As String
Dim SoLoai As Byte ' Bien nay nham xac dinh so loai ngay nghi, tang ca... trong mot chuoi
Dim BChuoi As clsString
Dim BGiatri
On error resume next
'Xac dinh so cot trong bien Khoang
Socot = Khoang.Columns.Count
'Xac dinh so hang trong bien Khoang
Sohang = Khoang.Rows.Count
' Nham bao dam so sanh dung ta dung ham UCase
Chucnang = UCase(Chucnang)
'Duyet qua cac cell trong bien Khoang
For i = 1 To Sohang
For j = 1 To Socot
BGiatri = Khoang.Cells(i, j).Value
BGiatri = Trim(BGiatri)
' Bat dau xu ly bgiatri qua Class clsString
Set BChuoi = New clsString
BChuoi.Text = BGiatri
'Ky tu de phan cach cac Chuc nang
BChuoi.Delimiter = ";"
'Xac dinh so Chuc nang trong 1 cell
SoLoai = BChuoi.TokenCount
For k = 1 To SoLoai
'Chuoi cua tung chuc nang
BGiatri = BChuoi.TokenAt(k)
Bchucnang = UCase(Left(BGiatri, Len(Chucnang)))
Bgiatriso = Val(Right(BGiatri, Len(BGiatri) - len(Chucnang)))
Select Case Bchucnang
Case Chucnang
Btotal = Btotal + Bgiatriso
End Select
Next k
Next j
Next i
Chamcong = Btotal
End Function

Với hàm chấm công như trên, bạn có thể dùng các chuổi ký tự dài hơn để thể hiện chức năng mà bạn cần (Ví dụ như: nghiphep, nghibenh, tangca,...vv), hay trong công thức bạn có thể tham chiếu đến một cell nào đó để đại diện cho chức năng bạn muốn.

Trong ví dụ này, ngày 22/02 tôi chấm công nghiphep1, ngày 23/02 tôi chấm công nghiphep0.5;tangca3. Công thức trong ô AM8, tôi sẽ nhập vào

=chamcong(G8:AH8,AM7) (ô AM7 chứa chữ Nghiphep)

Công thức trong ô AN8, tôi nhập công thức sau:

=chamcong(G8:AH8,AN7) (ô AN7 chứa chữ Tangca)

Vâng, dĩ nhiên là bạn có thể sử dụng các chuổi ký tự như trên sẽ dễ hiểu, nhưng bảng chấm công của bạn sẽ rất lượm thuộm.

Hình vẽ sau minh họa ví dụ trên của tôi.






















Tôi hy vọng rằng bài viết trên sẽ giúp ích các bạn phần nào trong công việc chấm công nhàm chán (nhưng không được sai) hàng ngày của bạn.

Ngòai chức năng trên, hàm này còn làm được nhiều chức năng khác tùy từng ứng dụng của bạn.

Mọi ý kiến góp ý xin các bạn gởi về:

levanduyet@yahoo.com

 

[levanduyet]

TẠO MENU POPUP TRONG EXCEL
Mục đích: Tạo menu popup khi người dùng Right-Click khi chuột trong vùng làm việc của một worksheet.
Giả sử workbook của tôi có một worksheet, thì trong ví dụ của tôi có hai đoạn mã. Đoạn thứ nhất nằm trong Module VBA: PopupMenu và đoạn mã thứ hai nằm trong module worksheet: workhere
Đây là đoạn mã trong module VBA PopupMenu:
Option Explicit
Public Const gc_Title = "PopUp Menu Demo"
Public gcBar_RgtClkMenu As CommandBar
' ***************************************************************************
' Muc dich: Gọi hàm tạo popup menu người dùng
'
Sub RunMeToGetThingsGoing()
Set gcBar_RgtClkMenu = CreateSubMenu
End Sub
' ***************************************************************************
' Hàm tạo popup menu
'
Function CreateSubMenu() As CommandBar
'đặt tên cho popup menu
Const lcon_PuName = "PopUpDemo"
'Tạo các đối tượng cho cho popup menu
Dim cb As CommandBar
Dim cbc As CommandBarControl
'Chắc chắn rằng popup menu không tồn tại
DeleteCommandBar lcon_PuName
'Thêm popup menu người dùng cho tập họp (collection) CommandBars
Set cb = CommandBars.Add(Name:=lcon_PuName, Position:=msoBarPopup, MenuBar:=False, Temporary:=False)
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' Thêm vào controls
Set cbc = cb.Controls.Add
With cbc
.Caption = "&Control 1"
.OnAction = "DummyMessage"
End With
Set cbc = cb.Controls.Add
With cbc
.Caption = "Control &2"
.OnAction = "DummyMessage"
End With
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Set CreateSubMenu = cb
End Function
' ***************************************************************************
' Mục đích: Kiểm tra nếu command bar có tên menuName?
' Nếu tồn tại thì xóa đi
'
Sub DeleteCommandBar(menuName)
Dim mb
For Each mb In CommandBars
If mb.Name = menuName Then
CommandBars(menuName).Delete
End If
Next
End Sub
Sub DummyMessage()
MsgBox "Hello", vbInformation + vbOKOnly, gc_Title
End Sub
Đây là đoạn mã trong worksheet module: workhere
Option Explicit
' ***************************************************************************
' Muc đích : Nó sẽ được kích họat khi người dùng Right click
' ***************************************************************************

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error GoTo Worksheet_BeforeRightClick_Error
'Hiện popup menu người dùng
gcBar_RgtClkMenu.ShowPopup
Worksheet_BeforeRightClick_Resume:
'Nhằm ngăn chặn popup menu mặc định của Excel
Cancel = True
'Thoát khỏi thủ tục
Exit Sub
Worksheet_BeforeRightClick_Error:
'Nếu macro khởi tạo chưa chạy
'Hỏi người dùng có muốn chạy bây giờ không?
If vbYes = MsgBox("You need to run the macro " _
& "RunMeToGetThingsGoing" _
& " before this demo will work" & vbCrLf _
& vbCrLf & "Run it now?", vbQuestion + vbYesNo, gc_Title) Then
'Nếu người dùng click "Yes", thì chạy
RunMeToGetThingsGoing
MsgBox "Bây giờ thử lại", vbInformation + vbOKOnly, gc_Title
End If
''Thoát
Resume Worksheet_BeforeRightClick_Resume
End Sub
Lần đầu khi bạn Right Click thì bạn sẽ nhận được thông báo sau:


Sau đó nếu bạn chọn Yes thì bạn sẽ nhận được thông báo sau:


Cuối cùng bạn thử Right Click lại thì bạn sẽ nhận được popup menu sau:


Chúc các bạn thành công. Hy vọng bài viết trên sẽ giúp ích các bạn phần nào.

Mọi góp ý của các bạn xin gởi cho tôi theo địa chỉ email sau:

levanduyet@yahoo.com

 

[levanduyet]

Lại Menu đây!!!


TẠO MENU NGƯỜI DÙNG TRONG EXCEL
(Dành cho người đã biết căn bản Visual Basic For Application)

Khi bạn tạo một ứng dụng trong excel, để cung cấp việc dễ dàng thao tác cho người dùng bạn phải tạo một menu riêng. Tôi xin giới thiệu cùng các bạn một cách để tạo menu riêng này.

Một menu bao gồm menu cấp 1 (Menu), menu cấp hai (Menu Item), menu cấp ba (Sub Menu Item). Hình 1 dưới đây thể hiện các cấp menu mà tôi vừa nêu trên.


Cách giải quyết là menu này được gọi khi một tập tin excel này được mở ra và menu này được gở bỏ khi tập tin excel này được đóng lại.

Để cho việc tạo menu này được linh động, tức là bạn có thể thêm bớt, chỉnh sửa dễ dàng thì tôi tạo một bảng dữ liệu nằm trên một sheet làm nguồn cho thủ tục tôi viết để tạo ra menu. Bảng dữ liệu của tôi gồm có 5 cột (Column) đại diện cho 5 trường (field). Các trường cụ thể đó là: Cấp menu (level) như tôi đã giải thích ở trên; Đầu đề (caption) của cấp menu. Các bạn chú ý ở đây ký tự & đứng trước ký tự nào trong đầu đề thì ký tự đó sẽ được gạch dưới (phím nóng); Vị trí hay tên macro cần thực hiện (position/macro) vị trí đối với menu cấp một, tên macro cần thực hiện đối với menu cấp hai hay cấp ba. Lằn ngăn cách (divider), nếu bạn cho bằng true thì trước menu đó sẽ có lằn ngăn cách giống như hình trên. FaceID số nguyên đại diện cho hình biểu diễn của menu đó. Để biết được số nguyên nào đại diện cho hình gì bạn có thể download và dùng tập tin add-in faceids.xla.

Các dữ liệu trong ví dụ tôi được thể hiện ở hình 2 sau:



























Sau đây là các thủ tục để tạo và xoá menu (bạn nên cho vào module). Bạn chú ý tên sheet chứa dữ liệu để tạo menu của bạn có tên là Menusheet.

Sub CreateMenu()
' Thủ tục này thực hiện khi workbook được mở
Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Chỉ ra Sheet chứa dữ liệu cho menu
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Nhằm chắc chắn Menu không bị trùng
Call DeleteMenu
' Khởi tạo giá trị của hàng đầu tiên
Row = 2

' Thêm vào menu, menu items và submenu items sử dụng
' dữ liệu được lưu trong MenuSheet
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With
Select Case MenuLevel
Case 1 ' Menu
' Đưa Menu ở mức cao nhất vào Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). Controls.Add(Type:=msoControlPopup, ?Before:=PositionOrMacro, Temporary:=True)
MenuObject.Caption = Caption
Case 2 ' Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True
Case 3 ' SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Sub DeleteMenu()
' Thủ tục này sẽ thực hiện khi workbook dược đóng lại
' Xóa Menu
Dim MenuSheet As Worksheet
Dim Row As Integer
Dim Caption As String
On Error Resume Next
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
If MenuSheet.Cells(Row, 1) = 1 Then
Caption = MenuSheet.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
End If
Row = Row + 1
Loop
On Error GoTo 0
End Sub
Sub DummyMacro() 'Đây chỉ là thủ tục để thử mà thôi
MsgBox "Thu tuc nay khong lam gi ca!"
End Sub

Để tạo và xoá menu bạn gọi các thủ tục trên khi sự kiện Open và BeforeClose xãy ra.
Private Sub Workbook_Open()
Call CreateMenu
MsgBox "A new menu (MyMenu) was created.", vbInformation
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

Lược dịch từ internet.

Lê Văn Được

Chúc các bạn thành công. Hy vọng bài viết trên sẽ giúp ích các bạn phần nào.

Mọi góp ý của các bạn xin gởi cho tôi theo địa chỉ email sau:

levanduyet@yahoo.com

 

[levanduyet]

Rất tiếc, không thể đưa hình ảnh vào...xin các bạn thông cảm...
CÁC KỸ THUẬT & THỦ THUẬT TRONG CÔNG THỨC
Hầu như các ứng dụng bảng tính thành công đều dùng công thức.

About Formulas - Công thức

Công thức là gì? Có thể nói công thức là những gì tạo nên một bảng tính. Nếu không phải là công thức thì worksheet của bạn chỉ là một tài liệu tĩnh - Nó như là những gì bạn làm trong Microsoft Word được hổ trợ bởi bảng biểu (table).

Và khi có công thức thì Excel thật là tuyệt. Nó có thể sử dụng hàm người dùng, hổ trợ mạnh việc đặt tên, hổ trợ công thức mảng - một kiểu đặc biệt của công thức mà ta không thể nào tưởng tượng được.

Công thức được đưa vào ô có thể bao gồm các yếu tố sau:

Các toán tử như là "+" (cộng) hay "*" (nhân).

Tham khảo đến các ô (bao gồm cả tên của các ô hay một khỏang-range).

Giá trị hay chuổi.

Các hàm (như là hàm SUM hay AVERAGE)

Công thức có thể lên đến 1,024 ký tự (điều này chắc rằng một số bạn sẽ không biết). Sau khi bạn đưa công thức vào ô và Enter thì ô sẽ thể hiện kết quả của công thức. Bản thân công thức sẽ được thể hiện trên thanh công thức một khi ô đó là active.

Calculating formulas - Công thức tính tóan

Nếu bạn chú ý, bạn sẽ thấy các công thức trong worksheet của bạn sẽ tính tóan ngay tức thì. Nếu bạn thay đổi bất cứ ô (cell) nào mà công thức bạn dùng, thì công thức sẽ thể hiện kết quả mới ngay. Excel sẽ tự động tính tóan khi bạn chọn chế độ Automatic như hình sau.



Đây là chế độ mặc định trong Excel, Excel dựa trên một số nguyên tắc sau để tính tóan trên worksheet của bạn:

Dữ liệu trong công thức thay đổi hay sửa chữa.

Khi Excel đang tính tóan, Excel có thể tạm dừng việc tính tóan này khi bạn cần thực hiện những công việc worksheet khác. Nó sẽ bắt đầu lại khi bạn đã hòan tất.

Các công thức sẽ được tính tóan theo thứ tự. Ví dụ như ô D12 phụ thuộc vào kết quả của ô D11, thì ô D11 sẽ được tính tóan trước ô D12.

Không phải lúc nào việc thiết lập như trên là điều tốt. Ví dụ như khi trên worksheet của bạn có nhiều công thức thì khi bạn thiết lập chế độ tự động tính tóan như trên nó sẽ làm chậm lại các tiến trình khác của bạn. Trong những trường hợp như thế bạn nên thiết lập chế độ tính tóan ở Manual. Bạn có thể tham khảo bài viết: "Một số thủ thuật nhằm tăng tốc chương trình".

Khi bạn đang làm việc ở chế độ Manual, Excel sẽ thể hiện Calculate ở thanh trạng thái khi bạn có bất kỳ công thức nào chưa tính tóan.


Bạn có thể dùng các shortcut sau đây để tính tóan lại (recalculate) các công thức:

_ F9, tính tóan lại tất cả các công thức trong tất cả các workbook đang mở.

_Shift + F9, chỉ tính tóan lại các công thức trong worksheet hiện tại (active worksheet). Những worksheet khác trong cùng workbook này sẽ không tính tóan lại.

_Ctrl + Shift + F9, bắt buộc tính tóan lại tất cả mọi thứ.

Sự tham chiếu đến ô (Cell) và nhiều ô (Range )

Hầu hết trong các công thức đều tham chiếu đến một ô hay nhiều ô. Sự tham chiếu này sử dụng địa chỉ của ô hay địa chỉ của nhiều ô, hay cũng có thể tham chiếu đến tên của ô đó hay các ô đó. Sự tham chiếu này có 4 kiểu:

Relative (tương đối): sự tham chiếu này hòan tòan là tương đối (fully relative). Khi công thức được copy, thì mối liên hệ giữa các ô sẽ được điều chỉnh dựa vào địa chỉ mới của ô công thức. Ví dụ: A1

Absolute (tuyệt đối): sự tham chiếu này hòan tòan là tuyệt đối. Khi công thức được copy, thì địa chỉ của các ô tham chiếu sẽ không thay đổi. Ví dụ: $A$1

Row Absolute (hàng tuyệt đối): khi công thức được copy thì chỉ có cột tham chiếu sẽ thay đổi, hàng tham chiếu không thay đổi. Ví dụ: A$1

Column Absolute (cột tuyệt đối): khi công thức được copy thì chỉ có hàng tham chiếu sẽ thay đổi, cột tham chiếu không thay đổi. Ví dụ: $A1

Mặc định thì các ô được tham chiếu đến theo kiểu tham chiếu tương đối.

Việc hiểu được các mối liên hệ khi bạn lập công thức là điều rất quan trọng cho việc sao chép các công thức sau này. Ví dụ như khi bạn lập công thức trong định dạng có điều kiện. Xin tham khảo bài Định dạng theo điều kiện. Một ví dụ để bạn có thể hiểu được địa chỉ tuyệt đối và tương đối. Ví dụ sau sẽ tính diện tích của hình chữ nhật được cho bởi chiều rộng (cột B) và chiều dài (hàng 3). Ở ô C4 ta lập công thức như sau

=C$3*$B4 Sau đó các bạn copy công thức trên xuống các ô trong vùng C4:F8, bạn sẽ hiểu được cách dùng công thức với địa chỉ tham chiếu là tuyệt đối hay tương đối.



Về R1C1 notaion

Thông thường Excel sử dụng cách tham chiếu như giải thích ở trên. Mỗi một địa chỉ ô thì bao gồm ký tự đại diện cho cột và chữ số đại diện cho hàng. Ngòai ra Excel cũng hổ trợ cách tham chiếu R1C1. Khi bạn dùng cách tham chiếu này, ví dụ bạn muốn tham chiếu đến ô A2 thì công thức tham chiếu sẽ là R1C2 (R tức là hàng, C tức là cột). Để chuyển sang tham chiếu theo cách này bạn vào Tools\Options và chọn như hình vẽ sau:






Tham chiếu đến Sheets hay workbooks khác

Khi tham chiếu đến một ô hay nhiều ô không cùng trên một worksheet, trước ô tham chiếu bạn phải đưa tên sheet vào, theo sau là "!" và sau cùng là địa chỉ ô mà bạn muốn tham chiếu đến. Ví dụ khi bạn muốn tham chiếu đến ô A1 ở sheet2, bạn viết như sau: =Sheet2!A1

Bạn cũng có thể tham chiếu đến ô ở một workbook khác. Ví dụ tôi muốn tham chiếu đến ô A1 ở workbook Budget.xls, tôi viết công thức như sau:

=[Budget.xls]Sheet1!A1

Nếu tên workbook tham chiếu đến có một hay nhiều khỏang trắng bạn phải đưa vào trong dấu ngoặc '. Ví dụ:

='[Budget For 2003]Sheet1!A1

Vâng, tôi vẫn khuyên bạn không nên tham chiếu đến nhiều giá trị trong các workbook khác. Như vậy sẽ làm chậm quá trình Calculation mỗi khi bạn muốn cập nhật các giá trị.



Sử dụng tên

Một trong những nét đặc trưng của Excel là cung cấp khả năng đặt tên cho rất nhiều lọai. Ví dụ, bạn có thể đặt tên ô (cells), các ô (ranges), các cột (columns), các hàng (rows), đồ thị (charts) và các đối tượng khác.

Đặt tên

Bạn có thể đặt tên cho các ô bằng cách Insert>Names>Define (hay bằng tổ hợp phím nóng Ctrl+F3). Hay cách nhanh hơn bạn có thể sử dụng hộp tên (Name Box).

Sử dụng tên đặc biệt rất quan trọng trong việc viết chương trình bằng VBA để tham chiếu đến cells hay ranges. Lý do tại sao? VBA không tự cập nhật (update) các tham chiếu nếu các cells hay ranges được di chuyển, hay thay đổi. Vì vậy cách tốt nhất nhằm làm cho việc lập trình VBA được dễ dàng hơn là đặt tên các dữ liệu mà bạn cần tham chiếu đến, sau đó sẽ thao tác với tên của khối dữ liệu này trong chương trình của bạn. Việc đặt tên không chỉ tốt cho lập trình VBA, mà còn tốt cho việc lập các công thức thông thường. Ở đây tôi sẽ không chú trọng tới thao tác của việc đặt tên. Các bạn có thể đọc trong phần help của Excel.

Các lỗi của công thức (Formula Errors)

Error Value - Giá trị lỗi Explanation - Giải thích
#DIV/0! Công thức chia cho giá trị 0. Điều này cũng xãy ra khi trong công thức có chia cho ô có rỗng.
#N/A Công thức tham chiếu đến (trực tiếp hay gián tiếp) ô mà giá trị không có hiệu lực (Not available)
[HASHTAG]#NAME[/HASHTAG]? Công thức sử dụng tên mà Excel không nhận ra. Điều này có thể xảy ra khi trong công thức bạn tham chiếu đến tên của ranges mà bạn đã xóa.
[HASHTAG]#NULL[/HASHTAG]! Công thức sử dụng một intersection của 2 khỏang (range) mà chúng không có giao nhau.
[HASHTAG]#NUM[/HASHTAG]! Đây là vấn đề xãy ra với giá trị. Ví dụ, bạn chỉ định (specified) giá trị âm nơi mà yêu cầu giá trị dương.
[HASHTAG]#REF[/HASHTAG]! Công thức tham chiếu đến ô không có giá trị.
[HASHTAG]#VALUE[/HASHTAG]! Công thức bao gồm các đối số không đúng kiểu dữ liệu.

Trên đây là một số vấn đề cơ bản về công thức. Về công thức mảng xin bạn tham khảo đến bài công thức mảng.



Ý kiến của các bạn xin các bạn gởi về levanduyet@yahoo.com

 

[levanduyet]

Rất tiếc, không thể đưa hình lên. Mong thông cảm...

CUNG CẤP TRỢ GIÚP CHO ỨNG DỤNG EXCEL CỦA BẠN

1.Trợ giúp cho ứng dụng Excel của bạn

Một trong những tiêu chí đánh giá một phần mềm đó là phần trợ giúp mà phần mềm đó cung cấp. Các ứng dụng phát triển trong Excel cũng không phải là những ngoại lệ.

Nếu bạn phát triển một ứng dụng đặc biệt trong Excel, thông thường bạn sẽ xem xét để cung cấp trợ giúp cho người dùng cuối. Điều này sẽ làm cho người dùng cảm thấy thoải mái và thuận tiện hơn. Việc cung cấp trợ giúp này nó phụ thuộc vào phạm vi và tính phức tạp của ứng dụng bạn phát triển.

Ta có thể phân trợ giúp này thành hai loại:

_ Hệ thống trợ giúp không chính thức (Unofficial Help System). Nó sẽ sử dụng các thành phần chuẩn của Excel (ví dụ như Form ngừơi dùng) để thể hiện trợ giúp.

_ Hệ thống trợ giúp chính thức (Official Help System). Hệ thống trợ giúp này sử dụng các File CHM được tạo ra bởi hệ thống trợ giúp HTML.

Tạo file trợ giúp không phải là một việc dễ dàng nhưng nó sẽ rất hữu ích cho người dùng ứng dụng của bạn.

2.Hệ thống trợ giúp sử dụng các thành phần chuẩn của Excel.

Có lẻ hầu hết các phương thức trực tiếp cung cấp trợ giúp cho người dùng là sử dụng các thành phần chuẩn của Excel. Nó đơn giản và dể hiểu. Tuy nhiên đối với các ứng dụng phát triển lớn của bạn, hay các ứng dụng phức tạp của bạn thì có lẻ nó sẽ không thể đáp ứng được.

Trong phần này chúng ta sẽ tìm hiểu một số kỹ thuật trợ giúp, nó sử dụng các thành phần của Excel sau:

_Sử dụng Cell comments.

_Sử dụng Text Box.

_Sử dụng trực tiếp Worksheet. Cách đơn giản nhất là bạn đưa các thông tin trợ giúp của bạn vào trong một Worksheet và đặt tên cho nó là "Help" hay "Trợ giúp". Khi cần tìm kiếm trợ giúp về ứng dụng người dùng chỉ việc nhấn vào Tab của Worksheet này để xem thông tin trợ giúp.

_Sử dụng Form người dùng.

2.1. Sử dụng cell comments

Có lẻ một trong cách đơn giản nhất cung cấp trợ giúp cho người dùng là dùng cell comment của Excel. Bạn chỉ việc Click vào ô mà bạn muốn ghi chú vào, sau đó chọn Insert/Comment để đưa ghi chú của bạn vào. Để chắc chắn rằng ghi chú của bạn sẽ xuất hiện khi bạn đưa con trỏ trên ô đó, bạn có thể dùng đoạn mã sau:

Application.DisplayCommentIndicator = xlCommentIndicatorOnly





2.2.Sử dụng Text Box:

Sử dụng Text Box để thể hiện thông tin trợ giúp cũng là một cách thực hiện dễ dàng. Bạn đưa Text Box vào trong WorkSheet của bạn bằng cách Click vào nút Text Box trên thanh công cụ Drawing


Bạn đưa thông tin trợ giúp vào Text Box và thiết đặt chiều rộng và cao của Text Box cho phù hợp.

Thông thường bạn sẽ không muốn Text Box này xuất hiện. Vì vậy chúng ta sẽ thêm 1 nút lệnh (Command Button) để cho hiện ra hoặc dấu đi Text Box này. Giả sử tôi tạo ra một nút lệnh và đưa vào một thủ tục để khi người dùng Click vào nút lệnh này thì thủ tục được thực hiện. Giả sử Text Box bạn tạo ra ở trên có tên là HelpText. Thủ tục đó như sau:

Sub ToggleHelp()
ActiveSheet.TextBoxes("HelpText").Visible = Not ActiveSheet.TextBoxes("HelpText").Visible
End Sub








2.3.Sử dụng worksheet để thể hiện thông tin trợ giúp

Một cách thực hiện khác cũng dễ dàng là đưa thông tin trợ giúp của bạn vào trong một WorkSheet. Sau đó bạn tạo một macro để hiện (active) WorkSheet này. Kế tiếp bạn đưa vào một nút lệnh và liên kết macro này vào nút lệnh. Macro này sẽ được thực hiện khi bạn Click vào nút lệnh này.







Đoạn mã lệnh của macro như sau:

Sub ShowHelp()
' Nhằm làm cho HelpSheet là Sheet hiện hành(Active)
Worksheets("HelpSheet").Activate
' Nhằm làm cho người dùng chỉ di chuyển xung quanh vùng chứa thông tin trợ giúp
ActiveSheet.ScrollArea = "A1:C35"
Range("A1").Select
End Sub



Ngoài ra nhằm tránh việc người dùng thay đổi nội dung trợ giúp chúng ta cũng bảo vệ (protect) worksheet này.

Điểm bất thuận tiện chính của kỹ thuật này là thông tin trợ giúp này không xuất hiện tại vùng làm việc của bạn. Để giải quyết điều này ta có thể viết một macro mở một cửa sổ mới để thể hiện sheet này.

2.4.Thể hiện thông tin trợ giúp bằng Form người dùng

2.4.1.Sử dụng Label để thể hiện thông tin trợ giúp

Hình sau thể hiện Form người dùng với 2 label: một dùng cho chủ đề của trợ giúp. Ví dụ trong hình là Form Letter Printing. Hai dùng để thể hiện nội dung trợ giúp ứng với chủ đề trên. Một SpinButton nhằm giúp người dùng di chuyển giữa các chủ đề. Một nút lệnh nhằm đóng Form này lại.


Các thông tin trợ giúp được lưu trữ trong worksheet có tên là HelpSheet. Vùng chứa thông tin là vùng A1:B5. Cột A dùng để chứa chủ đề của trợ giúp. Cột B dùng để chứa nội dung của trợ giúp tương ứng với cột A.


Trong quá trình thiết kế Form sẽ có dạng như sau: (chú ý: nút Close có tên là CancelButton; label1 có tên là LabelTopic, label2 có tên là LabelText, SpinButton có tên là SpinButton1)








Đoạn mã lệnh của Form như sau:









Private Sub CancelButton_Click()
Unload Me 'Nhằm đóng Form lại
End Sub
Private Sub SpinButton1_Change()
HelpTopic = SpinButton1.Value
LabelTopic.Caption = Sheets("HelpSheet").Cells(HelpTopic, 1)
LabelText.Caption = Sheets("HelpSheet").Cells(HelpTopic, 2)
Me.Caption = " (Help Topic " & HelpTopic & " of " & SpinButton1.Max & ")"
End Sub
Private Sub UserForm_Initialize()
With SpinButton1
.Max = Application.WorksheetFunction.CountA(Sheets("HelpSheet").Range("A:A"))
.Min = 1
.Value = 1
End With
End Sub

2.4.2.Sử dụng "scrolling" Label để thể hiện thông tin trợ giúp

Kỹ thuật này thể hiện thông tin trợ giúp trong một Label control đơn. Bởi vì Label control không có các thanh trượt, do đó Label được đặt trong một Frame control (Frame control có các thanh trượt). Hình sau là ví dụ của một "scrolling" Label, người dùng có thể di chuyển để đọc thông tin trợ giúp bằng cách dùng các thanh trượt của Frame control.


Tương tự trên, nội dung trợ giúp được lấy ở worksheet HelpSheet trong sự kiện UserForm_Initialize của Form. Chú ý trong đoạn mã sẽ điều chỉnh thuộc tính ScrollHeight của Frame control để bảo đảm rằng khi dùng thanh trượt sẽ xem được tất cả các nội dung trong Label control. Đoạn mã của sự kiện UserForm_Initialize như sau:







Private Sub UserForm_Initialize()
Me.Caption = "Các thông tin trợ giúp"
LastRow = Sheets("HelpSheet").Range("A65536").End(xlUp).Row
txt = ""
For r = 1 To LastRow
txt = txt & Sheets("HelpSheet").Cells(r, 1).Text & vbCrLf
Next r
With Label1
.Top = 0
.Caption = txt
.Width = 160
.AutoSize = True
End With
Frame1.ScrollHeight = Label1.Height
Frame1.ScrollTop = 0
End Sub

Tương tự ta cũng có đoạn mã đóng form lại khi người dùng Click vào nút CancelButton.

Private Sub CancelButton_Click()
Unload Me 'Nhằm đóng Form lại
End Sub


Các bạn chú ý rằng Label control không thể hiện được các dạng chữ. Vì vậy để thể hiện như hình trên ta dùng các ký tự "-" liên tục nhau chứa trong các ô ở HelpSheet để phân cách các chủ đề của thông tin trợ giúp.



Mọi góp ý xin các bạn gởi về levanduyet@yahoo.com

 

[levanduyet]

Phần 2, Tạo File Help
2.4.2.Sử dụng "scrolling" Label để thể hiện thông tin trợ giúp

Kỹ thuật này thể hiện thông tin trợ giúp trong một Label control đơn. Bởi vì Label control không có các thanh trượt, do đó Label được đặt trong một Frame control (Frame control có các thanh trượt). Hình sau là ví dụ của một "scrolling" Label, người dùng có thể di chuyển để đọc thông tin trợ giúp bằng cách dùng các thanh trượt của Frame control.


Tương tự trên, nội dung trợ giúp được lấy ở worksheet HelpSheet trong sự kiện UserForm_Initialize của Form. Chú ý trong đoạn mã sẽ điều chỉnh thuộc tính ScrollHeight của Frame control để bảo đảm rằng khi dùng thanh trượt sẽ xem được tất cả các nội dung trong Label control. Đoạn mã của sự kiện UserForm_Initialize như sau:







Private Sub UserForm_Initialize()
Me.Caption = "Các thông tin trợ giúp"
LastRow = Sheets("HelpSheet").Range("A65536").End(xlUp).Row
txt = ""
For r = 1 To LastRow
txt = txt & Sheets("HelpSheet").Cells(r, 1).Text & vbCrLf
Next r
With Label1
.Top = 0
.Caption = txt
.Width = 160
.AutoSize = True
End With
Frame1.ScrollHeight = Label1.Height
Frame1.ScrollTop = 0
End Sub

Tương tự ta cũng có đoạn mã đóng form lại khi người dùng Click vào nút CancelButton.

Private Sub CancelButton_Click()
Unload Me 'Nhằm đóng Form lại
End Sub


Các bạn chú ý rằng Label control không thể hiện được các dạng chữ. Vì vậy để thể hiện như hình trên ta dùng các ký tự "-" liên tục nhau chứa trong các ô ở HelpSheet để phân cách các chủ đề của thông tin trợ giúp.

2.4.3. Sử dụng DropDown control để chọn chủ đề của thông tin trợ giúp


Tương tự như phần 2.4.2 ở trên ở đây khi thiết kế trên form có thêm combobox có tên ComboBoxTopics nhằm chọn chủ đề trợ giúp. Ngoài ra còn có hai nút lệnh là PreviousButton và NextButton nhằm di chuyển qua lại giữa các chủ đề. Chú ý: khi bạn chọn chủ đề trong ComboBox hay khi bạn Click vào các nút PreviousButton, NextButton thì nội dung của chủ đề sẽ được cập nhật trong Label control. Nội dung của thông tin trợ giúp cũng được bố trí trên HelpSheet như sau: cột A để chứa chủ đề của trợ giúp, cột B để chứa nội dung tương ứng với chủ đề bên cột A.



Đoạn mã như sau:

Option Explicit
Dim TopicCount As Integer
Dim CurrentTopic As Integer
Dim HelpSheet As Worksheet
Const HelpSheetName As String = "HelpSheet" 'Tên Sheet chứa nội dung trợ giúp
Const HelpFormCaption As String = APPNAME 'Tên ứng dụng của bạn
Private Sub UserForm_Initialize()
Dim Row As Integer
Set HelpSheet = ThisWorkbook.Sheets(HelpSheetName)
TopicCount = Application.WorksheetFunction.CountA(HelpSheet.Range("A:A"))
For Row = 1 To TopicCount
ComboBoxTopics.AddItem HelpSheet.Cells(Row, 1)
Next Row
ComboBoxTopics.ListIndex = 0
CurrentTopic = 1
UpdateForm
End Sub
Private Sub UpdateForm()
ComboBoxTopics.ListIndex = CurrentTopic - 1
Me.Caption = HelpFormCaption & " (" & CurrentTopic & " of " & TopicCount & ")"
With LabelText
.Caption = HelpSheet.Cells(CurrentTopic, 2)
.AutoSize = False
.Width = 212
.AutoSize = True
End With
With Frame1
.ScrollHeight = LabelText.Height + 5
.ScrollTop = 1
End With
On Error Resume Next
If CurrentTopic = 1 Then PreviousButton.Enabled = False Else PreviousButton.Enabled = True
If CurrentTopic = TopicCount Then NextButton.Enabled = False Else NextButton.Enabled = True
If NextButton.Enabled Then NextButton.SetFocus Else PreviousButton.SetFocus
End Sub
Private Sub ComboBoxTopics_Click()
' Thi hành khi ComboBox thay đổi
CurrentTopic = ComboBoxTopics.ListIndex + 1
UpdateForm
End Sub
Private Sub PreviousButton_Click()
' Thi hành khi bạn Click PreviousButton
If CurrentTopic <> 1 Then
CurrentTopic = CurrentTopic - 1
UpdateForm
End If
End Sub
Private Sub NextButton_Click()
' Thi hành khi bạn Click nextButton
If CurrentTopic <> TopicCount Then
CurrentTopic = CurrentTopic + 1
UpdateForm
End If
End Sub
Private Sub CancelButton_Click()
Unload Me 'Nhằm đóng Form lại
End Sub

 

[levanduyet]

Tạo File Help phần 3
3. Sử dụng WinHelp và hệ thống trợ giúp HTML

3.1 Giới thiệu

Hiện tại, hầu hết các hệ thống cung cấp thông tin trợ giúp cho các ứng dụng trên Windows đều dùng hệ thống trợ giúp Windows (WinHelp). Hệ thống này thể hiện các tập tin HLP và hổ trợ hypertext, cho phép người dùng di chuyển đến các chủ đề khác của thông tin trợ giúp. Tuy nhiên, dường như Microsoft đang cố gắng từng bước thay thế WinHelp bằng HTML help. Hầu hết các ứng dụng mới của Microsoft đều sử dụng HTML help, kể cả bộ Office 2000. Dưới đây là hình ảnh trợ giúp HTML thường thấy trong Excel:



Để tạo các tập tin trợ giúp này các bạn có thể dùng nhiều sản phẩm khác nhau, một trong các sản phẩm đó có thể giới thiệu ở đây là RoboHELP của Blue Sky Software. Các bạn có thể thăm web site của công ty ở điạ chỉ: http://www.blue-sky.com

Ở đây tôi không giới thiệu cụ thể cách tạo file help HTML, các bạn có thể đọc thông tin hay các bài học của từng phần mềm. Ví dụ như đối với RoboHELP như sau:



3.2.Liên kết file trợ giúp với ứng dụng của bạn

Sau khi tạo ra file trợ giúp WinHelp hay HTML Help, bạn có thể liên kết từng phần của file trợ giúp với ứng dụng của bạn bằng một hay hai cách sau: bằng cách sử dụng Project Properties hay bằng cách viết mã VBA.

Trong cửa sổ VBE, chọn Tools/VBAProject Properties...Trong hộp thoại Project Properties bạn chọn tab General và chỉ ra file trợ giúp cho project. Cách tốt nhất là bạn lưu trữ file trợ giúp cho ứng dụng của bạn cùng thư mục với ứng dụng. Khi đó bạn có thể dùng câu lệnh VBA để liên kết file trợ giúp với ứng dụng của bạn như sau: (giả sử file trợ giúp cùng thư mục với ứng dụng và có tên Myfuncs.hlp)

ThisWorkbook.VBProject.HelpFile = ThisWorkbook.Path & "\Myfuncs.hlp"



4.Sử dụng Office Assistant để thể hiện thông tin trợ giúp

Chắc có lẻ các bạn rất quen thuộc với Office Assistant. Có người sẽ thích kiểu trợ giúp này và chắc chắn rằng cũng có người không thích! Office Assistant hoàn toàn có thể lập trình, vì vậy bạn có thể dùng nó để đưa thông tin trợ giúp của bạn vào. Dưới đây là hình ảnh bạn dùng Office Assistant để đưa thông tin trợ giúp vào ứng dụng Excel của bạn:



Tương tự thông tin trợ giúp bạn cũng đặt trong Worksheet HelpSheet



Cột A chứa chủ đề thông tin trợ giúp. Cột B chứa nội dung tương ứng với chủ đề ở cột A. Thủ tục chính sử dụng Office Assistant để thể hiện trợ giúp như sau (đặt trong một module cùng với thủ tục ProcessRequest)

Public Const APPNAME As String = "Elephants R Us"
Dim Topic
Dim HelpSheet
Sub ShowHelp()
Set HelpSheet = ThisWorkbook.Worksheets("HelpSheet")
Application.Assistant.On = True
Topic = 1
With Assistant.NewBalloon
.Heading = "Help Topic " & Topic & ": " & vbCrLf & HelpSheet.Cells(Topic, 1)
.Text = HelpSheet.Cells(Topic, 2)
.Button = msoButtonSetNextClose
.BalloonType = msoBalloonTypeButtons
.Mode = msoModeModeless
.Callback = "ProcessRequest"
.Show
End With
End Sub
Thủ tục bắt đầu bằng việc chắc chắn rằng Office Assistant được bật lên. Kế đó tạo đối tượng Balloon mới (Bạn có thể tìm hiểu cụ thể về đối tượng này trong phần Help của Excel). Chủ đề được đưa vào thuộc tính Heading, còn nội dung được đưa vào thuộc tính Text. Thuộc tính Button sử dụng hằng số msoButtonSetNextClose, điều này có nghĩa là ban đầu Office Assistant chỉ có 2 nút là Next và Close. Thuộc tính Mode được đặt là msoModeModeless, cho phép người dùng vẫn có thể tiếp tục làm việc với ứng dụng trong khi trợ giúp được thể hiện. Thuộc tính Callback chứa tên của thủ tục khi người dùng Click vào nút Next. Cuối cùng Assistant balloon được thể hiện bằng cách dùng phương thức Show.

Sau đây là thủ tục ProcessRequest

Sub ProcessRequest(bln As Balloon, lbtn As Long, lPriv As Long)
NumTopics = Application.WorksheetFunction.CountA(HelpSheet.Range("A:A"))
Assistant.Animation = msoAnimationCharacterSuccessMajor
Select Case lbtn
Case msoBalloonButtonBack
If Topic <> 1 Then Topic = Topic - 1
Case msoBalloonButtonNext
If Topic <> NumTopics Then Topic = Topic + 1
Case msoBalloonButtonClose
bln.Close
Exit Sub
End Select
With bln
.Close
Select Case Topic
Case 1: .Button = msoButtonSetNextClose
Case NumTopics: .Button = msoButtonSetBackClose
Case Else: .Button = msoButtonSetBackNextClose
End Select
.Heading = "Help Topic " & Topic & ": " & vbCrLf & HelpSheet.Cells(Topic, 1)
.Text = HelpSheet.Cells(Topic, 2)
.Show
End With
End Sub
Thủ tục này dùng cấu trúc Select Case để xác định nút nào được thể hiện. Nó thật là hấp dẫn, nếu bạn thích thú với nó bạn có thể tham khảo trên online help của Excel.

Tóm lại ngoài việc thể hiện thông tin trợ giúp bằng WinHelp hay HTML, thì đa số các trường hợp khác lưu thông tin trợ giúp trên một Worksheet, chủ đề của thông tin trợ giúp ở cột A, còn chi tiết của chủ đề sẽ ở cột B. Như vậy sẽ dễ dàng cho việc chỉnh sửa và thêm bớt của bạn. Một kỹ thuật nữa đôi khi cũng cần cho các bạn là: có một số trường hợp bạn chỉ muốn thể hiện thông tin trợ giúp khi người dùng thao tác trên một vùng nào đó mà thôi. Giả sử trong worksheet của tôi có vùng với tên data, tôi muốn chỉ khi nào người dùng Click chuột phải trên vùng này thì mới thể hiện thông tin trợ giúp, đoạn mã của tôi như sau:

Private Sub Worksheet_BeforeRightClick(Byval Target as Excel.Range, Cacel as _ Boolean)
if Union(Target.Range("A1"),Range("data")).Address=Range("data").Address then
Call ShowHelp()
Cancel=True?
End If
End Sub
Vâng, hy vọng rằng với bài viết trên sẽ giúp các bạn tạo trợ giúp cho ứng dụng Excel của mình.

 

[levanduyet]

Dùng Format Conditional... để định dạng cột ngày Chủ nhật trong bảng chấm công

Tôi xin giới thiệu với các bạn việc sử dụng Format conditional... để thay đổi định dạng của cột ngày Chủ nhật trong bảng chấm công.

Thông thường ngoài các cột như mã nhân viên, tên nhân viên,...thì bảng chấm công sẽ có các cột thể hiện các ngày trong tháng. Tôi muốn rằng qua năm sau tôi lại sử dụng lại các sheet này để làm bảng chấm công cho năm . Do đó nếu tôi làm công việc thủ công là chọn cột các ngày là ngày Chủ nhật trong tháng, rồi Fill color thì công việc thật là nhàm chán. Tôi muốn là tôi chỉ thay đổi ngày đầu tiên của bảng chấm công thì nó sẽ tự động thay đổi màu của các cột trong bảng chấm công là ngày Chủ nhật.

Giả sử bảng chấm công của tôi như sau:



Trong ô C4 tôi nhập ngày đầu tiên của bảng chấm công. Ô C5 tôi nhập công thức sau: =C4+1, sau đó tôi copy công thức này qua các ô bên phải (các ô C6, C7, C8,...). Chọn khoảng cần định dạng trong bảng chấm công, giả sử ở đây tôi chọn khoảng C5:Q20. Sau đó chọn Format | Conditional...

Rồi sau đó nhập công thức và chọn Format như hình sau. Sau đó chọn OK.


Sau đó các bạn thử bằng cách, thay ô C4 bằng các ngày khác thì việc tô màu các cột là ngày Chủ nhật cũng sẽ thay đổi sau.

Chúc các bạn vui với điều mình vừa khám phá.

Lê Văn Duyệt

Mọi góp ý các bạn gởi về levanduyet@yahoo.com

 

[levanduyet]

Định dạng theo điều kiện (Conditional Formatting) Phần 1

Ở đây sẽ mô tả công cụ định dạng theo điều kiện. Công cụ mạnh này được đưa vào phiên bản Excel97. Nó không có trong những phiên bản trước.

Định dạng theo điều kiện là gì?

Định dạng theo điều kiện (Conditional Formatting -CF) là một công cụ cho phép bạn áp dụng định dạng cho một ô (cell) hay nhiều ô (range of cells) trong bảng tính và sẽ thay đổi định dạng tùy theo giá trị của ô hay giá trị của công thức. Ví dụ như bạn có thể tạo cho định dạng của ô đó là đậm khi giá trị của nó lớn hơn 100. Khi giá trị của ô thoả điều kiện thì các định dạng bạn tạo ra ứng với điều kiện đó sẽ được áp dụng cho ô đó. Nếu giá trị của ô không thoả điều kiện bạn tạo ra thì định dạng của ô đó sẽ áp dụng định dạng mặc định (Default formatting)

Một ô có thể có 3 định dạng theo điều kiện. Ví dụ như nếu giá trị của ô lớn hơn 200, thì nó sẽ được thể hiện là màu đỏ, nhưng nếu gía trị ở giữa khoảng 100 và 200 thì nó sẽ được thể hiện là màu xanh.

Bạn chú ý rằng định dạng theo điều kiện giống như việc thêm một hay nhiều công thức vào mỗi ô mỗi khi bạn sử dụng, vì vậy áp dụng định dạng theo điều kiện cho một số lớn các ô có thể gây ra việc thực hiện chương trình bị chậm đi. Do đó bạn hãy chú ý khi áp dụng cho một số lớn các ô.

Định dạng theo điều kiện đơn giản

Định dạng theo điều kiện đơn giản nhất là sử dụng lựa chọn (Option) Cell Value is trong hộp định dạng theo điều kiện (CF dialog box), và sử dụng một trong các toán tử so sánh sẵn có. Hộp định dạng theo điều kiện cho Excel 2000 được thể hiện ở hình dưới đây.

Hộp thoại này chỉ ra điều kiện định dạng cho ký tự trong ô có màu đỏ khi giá trị trong ô nằm giữa hai giá trị là 10 và 20. Ngoài toán tử between còn có các toán tử so sánh khác như greater than hay less than…






Để áp dụng định dạng theo điều kiện cho một ô hay nhiều ô, đầu tiên bạn chọn khoảng mà bạn muốn được định dạng theo điều kiện, sau đó bạn mở hộp thoại CF từ menu Format. Kế tiếp bạn thay đổi toán tử between bằng các toán tử nào mà bạn muốn. Kế tiếp bạn nhập các giá trị cho điều kiện này. Kế tiếp bạn nhấn (click) vào nút Format trên hộp thoại để chọn định dạng. Không phải tất cả các định dạng đều có thể áp dụng cho định dạng theo điều kiện. Ví dụ, bạn không thể thay đổi Font chữ hay kích thước Font chữ trong định dạng theo điều kiện. Cuối cùng bạn nhấn OK để áp dụng định dạng của bạn. Bạn có thể thêm vào định dạng theo điều kiện thứ hai hay thứ ba. Mỗi một định dạng theo điều kiện có thể có định dạng khách nhau.

Thứ tự của các điều kiện

Khi bạn có hơn một điều kiện cho một ô, chỉ có định dạng theo điều kiện đầu tiên đúng được áp dụng. Còn những định dạng theo điều kiện khác không có giá trị. Ví dụ, giả sử rằng bạn có 3 định dạng theo điều kiện cho ô A1 như sau:

1) Bold Text (chữ đậm) khi giá trị lớn hơn 10
2) Red Text (chữ màu đỏ) khi giá trị lớn hơn 20
3) Gray Background (màu nền xám) khi giá trị lớn hơn 30

Trong trường hợp này, nếu giá trị của ô A1 là 100 thì text trong ô A1 sẽ được in đậm nhưng không màu đỏ và nền không có màu xám, bởi vì điều kiện đầu tiên được thoả mãn, các điều kiện còn lại không có giá trị. Vì vậy bạn phải đặt điều kiện của bạn theo một thứ tự đúng.

1) Gray Background (màu nền xám) khi giá trị lớn hơn 30
2) Red Text (chữ màu đỏ) khi giá trị lớn hơn 20
3) Bold Text (chữ đậm) khi giá trị lớn hơn 10

Trong ví dụ này , ô A1 sẽ xuất hiện với nền màu xám nếu giá trị lớn hơn 30, xuất hiện với màu đỏ nếu giá trị trong khoảng 21 và 30, xuất hiện với chữ in đậm nếu giá trị trong khoảng 11 và 20 và định dạng mặc định nếu giá trị trong khoảng 0 và 10.

Các đìêu kiện không bao giời kết hợp với nhau. Điều này có nghĩa trong ví dụ ở trên, nếu giá trị ô là 40 thì ô A1 sẽ xuất hiện với màu nền xám (điều kiện 1), nhưng không xuất hiện với chữ đỏ (điều kiện 2) hay không xuất hiện với chữ được in đậm (điều kiện 3).

Logic của định dạng theo điều kiện có thể được mô tả như sau:

If Condition1 = True Then
Apply Format1
Else
If Condition2 = True Then
Apply Format2
Else
If Condition3 = True Then
Apply Format3
Else
Apply DefaultFormat
End If
End If
End If




Logic của định dạng theo điều kiện không phải là

If Condition1 = True Then
Apply Format1
End If
If Condition2 = True Then
Apply Format2
End If
If Condition3 = True Then
Apply Format3
End If
Hiểu được sự khác biệt giữa hai cấu trúc logic ở trên rất là quan trọng.

 

[levanduyet]

Định dạng theo điều kiện (Phần 2)
Sử dụng công thức trong định dạng theo điều kiện

Ngoài việc sử dụng so sánh Cell Value Is , bạn có thể xây dựng những công thức riêng của bạn để xác định khi nào thì định dạng theo điều kiện được áp dụng. Để sử dụng công thức riêng (custom formula) bạn thay đổi Cell Value Is sang Formula Is trong hộp thoại CF, và đưa công thức của bạn vào text box vừa xuất hiện. Công thức của bạn nên trả về giá trị True hay False. Nếu công thức bạn trả về giá trị True thì định dạng theo điều kiện được áp dụng. Nếu công thức của bạn trả về giá trị False thì định dạng theo điều kiện sẽ không được áp dụng.

Một thuận lợi của việc sử dụng công thức riêng (custom formula) trong định dạng theo điều kiện là cho phép bạn thay đổi định dạng của một ô dựa trên giá trị của một ô khác. Ví dụ, bạn muốn ô A1 xuất hiện màu đỏ khi ô B1 lớn hơn 10, bạn có thể sử dụng công thức sau =IF(B1>10,TRUE,FALSE) , hay đơn giản hơn =B1>10 . Bạn có thể sử dụng bất kỳ công thức nào của Excel, ngoại trừ một sô trường hợp sau:

Trong công thức không thể tham chiếu đến một vùng ở một worksheet hay workbook khác.
Bạn không thể sử dụng những hàm trong module Add-in. Nhưng bạn có thể sử dụng các hàm dựa trên VBA trong công thức bạn lập. (a VBA based function)
Địa chỉ Tuyệt đối và Tương đối trong định dạng theo điều kiện

Khi bạn sử dụng công thức người dùng (custom formular) trong định dạng theo điều kiện, bạn cần nhận thức được sự khác nhau giữa địa chỉ tuyệt đối và địa chỉ tương đối. Nếu bạn sử dụng định dạng theo điều kiện để áp dụng cho nhiều ô (range of cells), thì bạn sử dụng địa chỉ tương đối. Ví dụ, giả sử rằng chúng ta muốn áp dụng định dạng theo điều kiện cho khoảng A1:A10, sẽ được in đậm nếu giá trị trong khoảng B1:B10 lớn hơn 10. Chúng ta có thể dùng công thức =B1>10 để làm điều này. Tức là giả sử ô B2 có giá trị là 11 thì giá trị trong ô A2 sẽ được in đậm. Đó là điều mà chúng ta thường muốn. Tuy nhiên giả sử rằng chúng ta muốn định dạng khoảng A1:A10 in đậm nếu giá trị ô B1 lớn hơn 10, tức là mỗi ô trong khoảng A1:A10 sẽ luôn luôn được so sánh với B1. Trong trường hợp này chúng ta phải sử dụng công thức =$B$1>10.



Sử dụng tên trong định dạng theo điều kiện

Như đã nói ở trên, các hàm người dùng (custom functions) trong định dạng theo điều kiện không thể tham chiếu đến các ô trong một worksheet khác trong cùng một workbooks. Tuy nhiên bạn có thể khắc phục điều này bằng cách sử dụng tên. Định nghĩa một tên tham chiếu đến một khoảng worksheet khác, và sử dụng tên đó trong công thức của bạn (với chú ý các địa chỉ tuyệt đối và tương đối như đã nói ở phần trên.

Ví dụ, giả sử rằng bạn muốn ô A1 trên Sheet1 màu đỏ nếu giá trị bạn nhập vào trong ô A1 không có trong danh sách các giá trị trong khoảng B1:B10. Nếu bạn lập công thức như sau thì sẽ bị báo lỗi COUNTIF(Sheet2!$B$1:$B$10,A1)=0 . Để khắc phục điều này bạn đặt tên Mylist tham chiếu đến khoảng =Sheet2!$B$1:$B$10 và sử dụng tên trong công thức của bạn như sau: =COUNTIF(MyList,A1)=0

Áp dụng định dạng theo điều kiện để phát hiện nhập trùng số liệu

Highligh khi nhập trùng số liệu

Giả sử rằng tôi muốn khi tôi nhập dữ liệu vào trong vùng có tên là range1 (giả sử range1 tham chiếu đến vùng Sheet1!$A$1:$A$10), nếu dữ liệu tôi bị trùng thì nó sẽ in đậm hay tô nền cho tôi (bạn chọn kiểu format bằng cách click nút Format) thì tôi có thể nhập vào công thức sau =IF(COUNTIF(Range1, A1)>1,TRUE,FALSE)


Hy vọng rằng một số kiến thức trên sẽ giúp ích một phần nào công việc của bạn.

Lê Văn Duyệt

Mọi ý kiến góp ý xin bạn liên lạc về:

levanduyet@yahoo.com

 

[levanduyet]

GIỚI THIỆU VỚI CÁC BẠN MỘT CÁI NHÌN TỔNG QUÁT VỚI EXCEL 2000
Khi bạn sắp phát triển các ứng dụng với Excel, bạn nên xem xét các đối tượng (Objects), các yếu tố của Excel mà bạn phải thao tác với chúng.

Ứng dụng bản thân Excel (the Excel application itself).
Excel Workbook (an Excel Workbook).
Worksheet trong Workbook (a Worksheet in a Workbook).
Range trong Worksheet (a Range in a Worksheet).
ListBox trên Form người dùng (a ListBox control on a UserForm).
Chart sheet (a Chart sheet).
Chart trên Chart sheet (a Chart on a Chart sheet).
Chart series on a Chart (a Chart series on a Chart).
Chú ý rằng các đối tượng tồn tại theo thứ tự bậc (từ của người dich dùng) như hình sau:



Excel có hơn 100 đối tượng (objects) bạn có thể điều khiển trực tiếp hay bằng VBA. Bạn chú ý rằng các sản phẩm của Office 2000 (nói riêng, Office nói chung) đều có các đối tượng của riêng nó. Do đó muốn lập trình với đối tượng nào thì bạn phải tìm hiểu về chúng cụ thể như các property (thuộc tính), method (phương thức) của chúng. Trong các bài sau tôi sẽ hướng dẫn bạn từng bước để bạn có thể làm chủ các đối tượng của Excel.

Workbooks

Một trong những đối tượng phổ biến nhất của Excel là Workbook. Mọi thứ bạn làm trong Excel được đặt trong một workbook và được lưu với một file mà đuôi là ".xls".

Excel 97 và Excel 2000 dùng chung một dạng file (the same file format). Các phiên bản trước sẽ không mở được các file ở hai phiên bản này mặc dù ở hai phiên bản này bạn có thể lưu file ở phiên bản trước. Một workbook có thể chứa đựng bất kỳ số sheets nào (giới hạn chỉ bởi bộ nhớ mà thôi). Có 4 dạng của sheet:

Worksheets.

Chart sheets.

XLM macro sheets (đã không còn dùng nhưng vẫn hổ trợ).

Dialog sheets (đã không còn dùng nhưng vẫn hổ trợ).

Bạn có thể mở bao nhiêu workbook nếu bạn thích (mỗi một workbook có một cửa sổ riêng), nhưng tại một thời điểm thì chỉ có một workbook là active (tức là khi bạn làm việc sẽ chỉ làm việc với một workbook mà thôi, điều này bạn có thể bị lầm tưởng!). Và tương tự đối với một workbook tại một thời điểm thì chỉ có một worksheet là active mà thôi.

Bạn có thể chuyển từ sheet này qua sheet kia bằng Tab bên dưới. Bạn có thể đặt tên cho các sheet, bạn có thể đặt tên tiếng việt, nhưng tôi đề nghị các bạn không nên làm điều này! Bạn có thể di chuyển vị trí các sheet lên hay xuống thì tùy bạn.

Worksheet

Một trong những kiểu phổ biến nhất của sheet là worksheet. Mỗi một worksheet có 256 cột và 65,536 hàng. Và bạn phải nhớ cho một điều là bạn có thể xóa, ẩn chúng chứ bạn không thể tăng thêm số hàng hay số cột! (Excel 97 chỉ có 16,384 hàng).

How big is a Worksheet - Một Worksheet thì lớn như thế nào?

Có lẻ có một lúc nào đó bạn sẽ hỏi "Một worksheet thì lớn như thế nào?", nếu ta lấy số hàng nhân cho số cột, tức là: (256x65,536)=16,777,216 ô, và nhớ rằng đây chỉ là một worksheet! Nếu bạn dùng chế độ VGA chuẩn thì với độ cao bình thường của hàng và độ rộng bình thường của côt bạn có thể nhìn thấy 9 cột và 18 hàng tại một thời điểm (hay 162 ô). Nếu bạn lấy số 16,777,216 /162 thì bạn sẽ thấy nó lớn như thế nào! Nếu bạn nhập vào mỗi ô một con số và giả sử rằng mỗi lần nhập như vậy bạn mất 1 giây. Nó sẽ làm cho bạn mất khoảng 194 ngày liên tục. Nó sẽ dài hơn nếu bạn uống cà phê và ăn uống !

Có bao giờ bạn thắc mắc tại sao chỉ có 256 cột x 65,536 hàng? Tại sao lại là 256 mà không là 250 hay 365? Số hàng và số cột là kết quả của hệ thống số nhị phân. 256 là 2^8, đó là giá trị lớn nhất có thể được lưu trong 8 bit. Số hàng trong một a worksheet là 65,536, đó là con số 2^16. Phiên bản cũ của excel chỉ chứa 16,384 hàng, đó là con số 2^14.

Như bạn đã biết một ô trong một worksheet có thể chứa các giá trị (bao gồm cả ngày), công thức (formula), giá trị đúng/sai (Boolean) hay văn bản. Bạn cũng có thể đưa cả đối tượng đồ họa vào như là các đối tượng chart, map, drawing, các control, hình ảnh và embedded.

Chart sheets

Chart sheet, thông thường chứa một đồ thị. Rất nhiều người dùng bỏ qua chart sheet. Dùng chart sheet thì không bắt buộc nhưng nó sẽ làm cho việc in ấn dễ dàng hơn một ít. Hai phần còn lại là XLM macro sheet và Dialog sheet tôi sẽ không nói đến bởi vì các bạn sẽ không phải dùng đến chúng trong phiên bản này!

Chúc các bạn thành công.

Lê Văn Duyệt (Sưu tầm và dịch)

 

[levanduyet]

TẠO BẢNG CHỌN DỮ LIỆU (DATA SELECTOR)

Trong các doanh nghiệp nhỏ dữ liệu thường được bố trí trên Excel. Một số chủ doanh nghiệp không quan tâm đến việc bố trí các dữ liệu này. Công việc thiết kế và sử dụng dữ liệu này hoàn toàn giao cho các kế toán viên. Một số kế toán viên khi thiết kế và bố trí các dữ liệu lại không quan tâm đến việc sử dụng lại các dữ liệu sau này khi doanh nghiệp phát triển. Việc thiết kế các bảng lưu trữ, các bảng mã cho khách hàng, nhân viên bán hàng, các sản phẩm bán ra,...là công việc rất quan trọng ban đầu để móc nối các dữ liệu với nhau trong công việc sử lý số liệu như: theo dõi công nợ, theo dõi doanh số, phân tích theo các yêu cầu...vv.

Vấn đề tôi đặt ra ở đây là khi móc nối các dữ liệu với nhau bạn sẽ phải làm việc nhiều đến việc lựa chọn một số mã từ các bảng mã. Làm sao để công việc lựa chọn của bạn được dễ dàng? Liệu Excel có cung cấp cho ta công cụ này không? Vâng, Excel có cung cấp cho ta một số công cụ nhưng theo kinh nghiệm bản thân, tôi nghĩ cách tốt nhất vẫn là tự mình thiết kế cho mình một bảng chọn. Còn các số liệu khác bạn có thể lấy ra từ các hàm chuẩn của Excel.

Ở đây tôi xin đưa ra một ví dụ nhỏ về việc tạo bảng chọn, để chọn mã sản phẩm từ bảng sản phẩm.

Ví dụ trong sheet MaSanPham, tôi có bảng mã sản phẩm (Hình 1) như sau:



Hình 1

Tôi đặt tên cho vùng A2:B570 có tên là MaSanPham. Bằng cách đánh dấu vùng A2:B570 vào Insert / Name / Define. Trong hộp thoại phần Names in Workbook bạn gõ và MaSanPham (Hình 2), sau đó nhấn nút OK.



Hình 2

Bây giờ đến phần thiết kế form để người dùng chọn lựa mã sản phẩm.

Từ cửa sổ Excel bạn nhấn tổ hợp phím Alt + F11. Cửa sổ VBE xuất hiện (Hình 7), sau đó bạn chọn Insert/UserForm (hình3). Ta đặt tên form là frmDataSelector, thuộc tính Caption là Data Selector. Trên form ta đặt các đối tượng như hình 4 gồm có: 1 TextBox có tên là TxtCode, 1 nút lệnh có tên là cmdOK, 1 nút lệnh có tên là cmdCancel, 1 ListView có tên là LVDataSelector. Đối tượng ListView trên thanh Toolbox như hình 5, nếu không có bạn phải vào Tools/Additional Controls và chọn như hình 6.

Hình 3

Hình 4

Hình 5

Hình 6



Hình 7

Sau đó bạn chọn Insert/Module đặt tên Module là DataSelector và gõ vào các hàm và thủ tục sau:

Option Explicit
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RangeNameExists(Nname) As Boolean
' Kiem tra xem Ten bang co ton tai hay khong?
' Neu ton tai thi tra ve TRUE
Dim n As Name
RangeNameExists = False
For Each n In ActiveWorkbook.Names
If UCase(n.Name) = UCase(Nname) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Day la ham de xuat cac du lieu tu Bang da duoc dat ten
' sang mot mang
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function TableToArray(ByVal TableName As String)
Dim arr
Dim vRange As Range
Dim i As Long, j As Long, m As Long, n As Long
If Not RangeNameExists(TableName) Then Exit Function 'Neu khong ton tai thi thoat
On Error Resume Next
Set vRange = Range(TableName)
i = vRange.Rows.count
j = vRange.Columns.count
ReDim arr(1 To i, 1 To j)
For m = 1 To i
For n = 1 To j
arr(m, n) = vRange(m, n).Value
Next n
Next m
TableToArray = arr
Set vRange = Nothing
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Chuyen tu mang sang ListView va dinh dang ListView
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ArrayToListview(ByVal VlistView As Listview, ByVal InputArray)
Dim m As Long
Dim i As Integer, j As Integer
Dim bHang As Long, bCot As Long, bHeader As Integer
Dim it As ListItem
Dim anItem
If Not IsObject(VlistView) Then Exit Sub
On Error Resume Next
'Dem so hang va so cot trong InputArray
bHang = UBound(InputArray, 1)
bCot = UBound(InputArray, 2)
'Dinh dang ListView
VlistView.View = lvwReport
VlistView.FullRowSelect = True
VlistView.MultiSelect = False
VlistView.Gridlines = True
VlistView.LabelEdit = lvwManual
VlistView.HideColumnHeaders = True
bHeader = VlistView.ColumnHeaders.count
Select Case bHeader 'Xac dinh so cot cua ListView
Case Is < bCot
For i = bHeader + 1 To bCot
VlistView.ColumnHeaders.Add i
Next i
Case Is = bCot
'Khong lam gi ca
Case Is > bCot
'Khong lam gi ca
End Select
'Dien cac gia tri tu Inputarray vao Listview
For i = 1 To bHang
For j = 1 To bCot
anItem = InputArray(i, j)
If j = 1 Then
Set it = VlistView.ListItems.Add()
it.Text = anItem
Else
it.SubItems(j - 1) = anItem
End If
Next j
Next i
'Dat do rong cac cot
For i = 1 To bCot
VlistView.ColumnHeaders(i).Width = 150
Next i
Set it = Nothing
Exit Sub
Tbloi:
MsgBox "Xin loi, khong the dua mang vao Listview " , vbCritical, "Th?b?
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Dua tu bang vao mang, sau do dua tu mang vao Listview
'Va di nhien ban cung co the chuyen tu bang vao ListView
Sub NhapDuLieu()
On Error Resume Next
Call DataSelector("MaVatTu")
End Sub
Sub DataSelector(Tenbang As String)
Dim bMang1
On Error Resume Next
bMang1 = TableToArray(Tenbang)
Call ArrayToListview(frmDataSelector.LVDataSelector, bMang1)
frmDataSelector.Show
End Sub
Đoạn mã của frmDataSelector như sau:

Private Sub cmdCancel_Click()
Unload Me 'Thoat
End Sub
Private Sub cmdOK_Click()
Dim bGiatrichon
On Error Resume Next
bGiatrichon = LVDataSelector.SelectedItem.Text
ActiveCell.Value = bGiatrichon 'Dat gia tri ban chon vao o hien tai
End Sub
'Muc dich cua ham sau nham cuon danh sach trong Listview den ma tuong tu
'trong danh sach khi nguoi su dung go vao Textbox txtCode cac ky tu dau tien cua ma.
Private Sub txtCode_Change()
Dim it As ListItem
On Error Resume Next
btim = Me.txtCode.Text
Set it = Me.LVDataSelector.FindItem(btim, lvwText, , lvwPartial)
bindex = it.Index
Me.LVDataSelector.ListItems.Item(bindex).Selected = True
Me.LVDataSelector.ListItems.Item(bindex).EnsureVisible
Set it = Nothing
End Sub Giả sử bây giờ tôi muốn lấy mã sản phẩm từ bảng MaSanPham, trong sheet MaSanPham, khi tôi nhấn chuột phải ở cột 1 của Sheet2 thì đoạn mã trong Sheet2 như sau:


Hình 8

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As _ Boolean)
If Target.Column = 1 Then
Cancel = True
Call NhapDuLieu
End If
End Sub

Hình 9

Bây giờ bạn hãy trở về cửa sổ Excel, chọn một ô bất kỳ ở cột 1 của Sheet2, bạn nhấn chuột phải thì frmDataSelector hiện ra như hình 9, bạn hãy thử gõ vào các ký tự đầu tiên của mã sản phẩm bạn cần...và bạn sẽ thấy nó hoạt động như thế nào.

Bạn có thể cải tiến đoạn mã trong module DataSelector bằng việc sát nhập hàm TableToArray và thủ tục ArrayToListview thành một.

Chúc các bạn thành công.

Mọi ý kiến góp ý xin các bạn gởi về:

levanduyet@yahoo.com

 

[levanduyet]

Một số hàm VBA thông dụng
VBA có rất nhiều hàm built-in hữu dụng (useful built-in functions), nhưng thiếu khả năng để thực hiện một số công việc chung. Ví dụ như, nếu ứng dụng của bạn cần xem xét một File có tồn tại hay không bạn phải viết một hàm người dùng để làm điều này.

Sau đây là 6 hàm VBA rất đơn giản, nhưng lại rất hữu dụng. Bạn chỉ việc đơn giản copy mã và dán vào trong module của bạn.

FileExists - Trả về TRUE nếu File tồn tại.

FileNameOnly- Tách tên File từ đường dẫn.

PathExists - Trả về TRUE nếu đường dẫn tồn tại.

RangeNameExists - Trả về TRUE nếu tên range tồn tại.

SheetExists - Trả về TRUE nếu tên sheet tồn tại.

WorkBookIsOpen - Trả về TRUE nếu workbook đã mở.


--------------------------------------------------------------------------------

Hàm kiểm tra sự tồn tại của File
Private Function FileExists(fname) As Boolean
' Trả về TRUE nếu File tồn tại
Dim x As String
x = Dir(fname)
If x <> "" Then FileExists = True _
Else FileExists = False
End Function

--------------------------------------------------------------------------------

Hàm tách tên File từ đường dẫn
Private Function FileNameOnly(pname) As String
' Tách tên File từ đường dẫn
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = Application.PathSeparator Then
FileNameOnly = temp
Exit Function
End If
temp = Mid(pname, i, 1) & temp
Next i
FileNameOnly = pname
End Function

--------------------------------------------------------------------------------

Hàm kiểm tra sự tồn tại của đường dẫn
Private Function PathExists(pname) As Boolean
' Trả về TRUE nếu đường dẫn tồn tại
Dim x As String
On Error Resume Next
x = GetAttr(pname) And 0
If Err = 0 Then PathExists = True _
Else PathExists = False
End Function

--------------------------------------------------------------------------------

Hàm kiểm tra sự tồn tại Tên của một khoảng (Range Name)
Private Function RangeNameExists(nname) As Boolean
' Trả về TRUE nếu tên range tồn tại
Dim n As Name
RangeNameExists = False
For Each n In ActiveWorkbook.Names
If UCase(n.Name) = UCase(nname) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function

--------------------------------------------------------------------------------

Hàm kiểm tra sheet có tồn tại hay không
Private Function SheetExists(sname) As Boolean
' Trả về TRUE nếu tên sheet tồn tại
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

--------------------------------------------------------------------------------

Hàm kiểm tra Workbook có mở hay không
Private Function WorkbookIsOpen(wbname) As Boolean
' Trả về TRUE nếu workbook đã mở
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function
Hy vọng rằng các hàm trên sẽ giúp ích cho các bạn phần nào trong lập trình VBA cho Excel.

Sưu tầm từ internet. Mọi góp ý xin các bạn gởi về địa chỉ mail sau:

levanduyet@yahoo.com

 

[levanduyet]

BẢNG TÍNH

Các phần mềm bảng tính ngày nay có rất nhiều như Lotus 1-2-3, Quattro Pro, Microsoft Excel (Ở đây tôi không muốn nói đến lịch sử phát triển của các phần mềm này. Nếu các bạn muốn tìm hiểu các bạn có thể tìm kiếm trên Internet). Mỗi phần mềm đều có những điểm mạnh khác nhau, nhưng nhìn chung thì các chức năng chính của một bảng tính đều như nhau. Để các bạn có thể chọn một phần mềm bảng tính phù hợp với yêu cầu của bạn thì bạn nên xem xét các yếu tố sau:

Chính sách của tập đòan, công ty: hầu hết các công ty khi đã chọn một phần mềm bảng tính nào thì sẽ sử dụng chúng trong một thời gian rất dài hoặc khó có thể lọai bỏ sử dụng nó.

Tính trì trệ (inertia): khi đã sử dụng một phần mềm bảng tính thì khả năng sử dụng những phần mềm bảng tính khác rất là khó vì tính trì trệ của người dùng.

Dễ sử dụng (familiarity).

Các thành phần phụ (standout features).

Sự đề nghị: thông thường một công ty khi mới bắt đầu sử dụng một phần mềm bảng tính nào thì dựa vào lời đề nghị của bạn bè, các công ty và phương tiện truyền thông đại chúng.

Khả năng tương thích.

Sự ổn định của nhà sản xuất phần mềm bảng tính.

Khả năng lập trình phát triển thêm các ứng dụng.

Khả năng hổ trợ người dùng.

Cuối cùng là giá cả phần mềm bảng tính.

Đưa ra các dữ kiện trên, không có nghĩa là bạn phải chọn Microsoft Excel ! Chọn hay không thì tùy bạn, bạn có thể đánh giá dựa vào các tiêu chí trên hay một vài tiêu chí khác của bạn. Nhưng các bạn biết không, khi các bạn tìm kiếm các thông tin về bảng tính trên internet thì 90% là các thông tin về bảng tính Excel !

Tại sao Excel thì rất hay cho các nhà phát triển?

Vâng, thật an tòan để đánh cuộc rằng các ứng dụng trên nền tảng bảng tính sẽ gia tăng quan trọng trong một vài năm tới. Excel 2000, một sản phẩm lập trình cao, là một chọn lựa dễ dàng cho phát triển các ứng dụng bảng tính bởi vì nó hổ trợ ngôn ngữ VBA và nó cung cấp dễ dàng cách tạo các hộp thọai người dùng. Một số điểm chính của Excel như sau:

Cấu trúc file (File structure). Sự định hướng multisheet (multisheet orientation) làm cho việc tổ chức các yếu tố (elements) của ứng dụng và lưu chúng trong cùng một file được dễ dàng. Ví dụ như, một file workbook có thể chứa nhiều worksheet và chart sheet. Các Form người dùng và module VBA được lưu cùng với workbook nhưng ở dạng không thấy (invisible) đối với người dùng cuối.

Visual basic for application. Ngôn ngữ macro này giúp bạn lập trình trực tiếp trong Excel và phát triển tùy theo mục đích bảng tính của mình.

Easy access to UserForm controls - Dễ dàng truy cập vào các điều khiển trên Form người dùng.

Custom dialog boxes - Bạn sẽ dễ dàng tạo các hộp thọai trông chuyên nghiệp hơn.

Customizable menus - Bạn có thể thay đổi, thêm vào, hay tạo menu mới.

Customizable shortcut menu - Excel là bảng tính duy nhất cho phép bạn thao tác với right-click, context-sensitive shortcut menu.

Customizable toolbars - Và đối với toolbar cũng vậy.

Microsoft query - Bạn có thể truy cập vào các dữ liệu quan trọng từ môi trường bảng tính. Dữ liệu nguồn bao gồm các dạng file dữ liệu chuẩn, file text, và web pages.

Data Access Objects and ActiveX Data Objects (tức là DAO, ADO), chúng sẽ giúp bạn thao tác với các dữ liệu bên ngòai một cách dễ dàng.

Extensive protection options - Các lực chọn bảo vệ rộng. Các ứng dụng của bạn có thể được bảo vệ không cho người dùng thay đổi.

Ability to create "compiled" add-in - Bạn có thể tạo các file add-in dễ dàng.

Custom worksheet function - Sử dụng VBA, bạn có thể tạo các hàm người dùng một cách dễ dàng.

Vâng, với các bài theo sau bạn sẽ từng bước, từng bước tiếp cận với các vấn đề được nêu trên. Tôi chắc chắn rằng các bạn sẽ từ từ ham thích lập trình VBA trong Excel ...cho mà coi!

Chúc các bạn thành công.

Lược dịch

Ngày 14/04/2004

Lê Văn Duyệt

 

[levanduyet]

DÙNG CONDITIONAL FORMATTING ĐỂ THAY ĐỔI ĐỊNH DẠNG Ô HIỆN HÀNH

Có người đặt trường hợp là làm thế nào để thay đổi màu trong một ô (ô này nằm trong một khoảng nào đó, theo yêu cầu của người sử dụng) khi người dùng di chuyển tơi. Tôi xin giới thiệu một cách là dùng Conditional Formatting

Đầu tiên để làm được điều này tôi cần có một hàm để trả về hàng hiện tại hay cột hiện tại của ô hiện tại và hàm này được đặt trong module1. Đoạn mã đó như sau: Option Explicit
Function AC(Row As Boolean) As Long
' Trả về hàng hay cột của ô hiện hành
AC = 0
On Error Resume Next
If Row Then
AC = ActiveCell.Row
Else
AC = ActiveCell.Column
End If
End Function




Chúng ta cũng cần sự trợ giúp của Worksheet_SelectionChange, với phương thức Calculate của ActiveSheet. Đoạn mã trong module của worksheet như sau:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
Sau đó bạn chọn khoảng mà bạn muốn định dạng, chọn Format | Conditional Formatting... và điền vào như hình sau:



Sau đó bạn chọn OK. Khi đó nếu bạn di chuyển trong khoảng này thì ô hiện hành sẽ thay đổi màu mỗi khi bạn di chuyển.

Lược dịch từ internet.

Chúc các bạn thành công.

Lê Văn Duyệt

 

[levanduyet]

Vâng,
Thưa các bạn. Với những thủ thuật tôi mới vừa gởi lên hy vọng rằng sẽ hữu ích cho các bạn trong công việc. Mọi ý kiến xin các bạn góp ý về:
levanduyet@yahoo.com
Ngày 7/11/2004, 1:16'ZZZZZZZZZZZZZZZ

 

[levanduyet]

Sau đây là một số thủ thuật nhằm tăng tốc chương trìnhTăng tốc và không cho màn hình nhấp nháy (flickering):

Sub NoScreenRePainting()
Application.ScreenUpdating=False
'Dat code cua ban o day
Application.ScreenUpdating=True
End Sub
Ngăn chặn việc tính toán trong khi thi hành mã:
Sub NoCalculations()
Application.Calculation = xlCalculationManual
'Dat code cua ban o day
Application.Calculation = xlCalculationAutomatic
End Sub
Tăng tốc nếu bạn có Events của Worksheet hay Workbook:
Sub StopAllEvents()
Application.EnableEvents = False
'Dat code cua ban o day
Application.EnableEvents = True
End Sub
Sử dụng khai báo With khi làm việc với các đối tượng:
Sub WithARange()
With Range("A1")
.Value = 100
.Font.Bold = True
.Interior.ColorIndex = 6
.Copy Destination:=Range("B1")
End With
End Sub
Sử dụng VbNullString thay vì dùng = ""
Sub EmptyText()
Dim strWords As String
strWords = "Cats"
MsgBox strWords
strWords = vbNullString
MsgBox strWords
End Sub
Đưa vào công thức tương đối vào nhiều ô (a range of cells):
Nhanh hơn là AutoFill hay Copy.
Sub NoAutoFillOrCopy()
Range("A1:A200").FormulaR1C1 = "=SUM(RC[1]:RC[5])"
End Sub
Tránh sử dụng Copy và Paste bất cứ khi nào có thể:
Sub NoCopyAndPaste()
'Thay vi:
Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")
'Thi su dung:
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
'Hay:
Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula
'Thay vi:
Sheet1.Range("A1:A200").Copy
Sheet1.Range("A1:A200").PasteSpecial xlPasteValues
'Thi su dung:
Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value
End Sub
Luôn luôn khai báo biến đúng!

Để xem nhanh các biến đã định nghĩa:
Chọn biến mà bạn muốn định nghĩa.
Bạn vào View>Definition (Shift+F2)

Để trở về vị trí trước:
Bạn vào View>Last Postition (Ctrl+Shift+F2).

Trả lại bộ nhớ từ biến Object :

Sub ReleaseObjectMemory()
'Co the la bat ky bien Object nao
Dim wSheet as Worksheet
'Dat bien Object
Set wSheet = Sheet1
'Dat code cua ban o day
'Giai phong bo nho
Set wSheet = Nothing
End Sub
Tránh sử dụng If, Else bất cứ khi nào có thể
Thông thường người ta sử dụng cấu trúc If, Else để kiểm tra điều kiện đúng hay sai. Tuy vậy có một phương thức nhanh hơn "một tí". Hai ví dụ sau đây, ví dụ đầu sử dụng phương thức thường sử dụng, ví dụ hai sử dụng phương thức nhanh hơn. Và dĩ nhiên đối với những ví dụ nhỏ thì thường sự khác nhau không lớn lắm.

Sub TrueOrFalseSlower()
Dim bYesNo As Boolean
Dim i As Integer
If i = 5 Then
bYesNo = True
Else
bYesNo = False
End If
MsgBox bYesNo
End Sub
Và đây là cách tốt hơn
Sub TrueOrFalseFaster()
Dim bYesNo As Boolean
Dim i As Integer
bYesNo = (i = 25)
MsgBox bYesNo
End Sub

Tương tự như vậy cách chung để chuyển đổi biến giữa True và False phụ thuộc vào các điều kiện, ta có thể làm theo cách tốt hơn như sau:
Cách thông thường:
Sub ToggleTrueOrFalseSlower()
Dim bYesNo As Boolean
If bYesNo = False Then
bYesNo = True
Else
bYesNo = False
End If
MsgBox bYesNo
End Sub

Đây là cách tốt hơn
Sub ToggleTrueOrFalseFaster()
Dim bYesNo As Boolean
bYesNo = Not bYesNo
MsgBox bYesNo
End Sub
Lược dịch từ internet.

Mọi ý kiến của các bạn xin gởi cho tôi theo địa chỉ email sau:

levanduyet@yahoo.com

 

[levanduyet]

Xoá vùng lọc và điều kiện lọc của AutoFilter và Advanced FilterCông cụ AutoFilter và Advanced Filter là công cụ mạnh trong Excel, nó giúp cho người sử dụng lọc và lấy khối dữ liệu cần thiết.



_Đối với AutoFilter

Khi sử dụng công cụ này nếu chú ý, bạn sẽ thấy rằng Excel nhớ vùng trước đó bạn lọc. Trong một số trường hợp thì điều này hữu ích. Nhưng nếu bạn muốn làm việc với các vùng khác thì thật là bất tiện. Excel lưu giữ vùng lọc của bạn với tên _FilterDatabase. Bạn không thể xoá tên này bằng cách thông thường là vào Insert/Name/Define.



Giả sử tôi có vùng dữ liệu muốn lọc là vùng A1:E5 của Sheet1.



Tôi Click và ô A2 và chọn Data/Filter/AutoFilter. Sau đó tôi chọn Insert/Name/Define. Hộp thoại Define Name xuất hiện. Nhưng bạn sẽ không thấy một tên nào cả (giả sử rằng trước đó bạn chưa đặt tên cho vùng nào cả).



Tên _FilterDatabase là loại hidden. Vì vậy bạn sẽ không thấy được bằng cách này. Bây giờ giả sử tôi nhập vào ô A6 với nội dung Lê Quốc và ô B6 với nội dung Cường các ô khác tôi không nhập (bạn cũng có thể nhập nội dung vào các ô khác, ở đây do làm ví dụ tôi giản lược bớt). Tôi chọn điều kiện lọc ở cột địa chỉ là: Quận Thủ Đức.

Kết quả lọc của tôi sẽ như sau:



Bạn thấy rằng hàng bạn vừa nhập vào cũng bị Hide luôn. Bây giờ tôi chọn điều kiện lọc ở cột Diachi là All để tôi bỏ điều kiện lọc vừa rồi. Sau đó tôi xoá dữ liệu vừa nhập vào ở ô A6 và B6. Sau đó tôi lại lập lại điều kiện lọc ở cột Diachi là Quận Thủ Đức. Bạn sẽ thấy hàng số 6 cũng bị dấu luôn.

Vâng đó là bất tiện của AutoFilter.

_Đối với AdvancedFilter

Tương tự đối với AdvancedFilter. Tôi chọn Data/Filter/AdvancedFilter với các dữ liệu như sau:



Kết quả tôi được các dữ liệu ở hàng 16 và 17 dưới đây.



Đến lúc này nếu bạn chọn Insert/Name/Define bạn sẽ thấy hai tên Criteria, và Extract trong hộp thoại Define Name



Với công cụ AdvanceFilter ngoài hai vùng có tên trên bạn cũng được vùng có tên là _FilterDatabase. Nhưng như đã nói ở trên, tên này không thể thấy hay xoá được theo cách thông thường. Tuy nhiên, bạn có thể sử dụng đoạn mã VBA đơn giản sau để xoá và gọi hộp thoại AdvanceFilter:

Sub ShowAdvancedFilterDialog()
' Xoa cac ten
On Error Resume Next
With ActiveWorkbook
.Names("_FilterDatabase").Delete
.Names("Criteria").Delete
.Names("Extract").Delete
End With
On Error GoTo 0
' Goi hop thoai
Application.Dialogs(xlDialogFilterAdvanced).Show
End Sub
Nếu bạn không sử dụng AdvanceFilter, thì bạn có thể sửa lại đoạn mã trên như sau:

Sub DeleteNames()
' Xoa cac ten
On Error Resume Next
With ActiveWorkbook
.Names("_FilterDatabase").Delete
End With
End Sub
Để sử dụng thủ tục này bạn copy thủ tục trên và đưa vào VBA module. Khi cần sử dụng bạn dùng phím nóng Alt + F8, sau đó chọn thủ tục mà bạn muốn chạy và chọn Run.

Tóm lại một khi bạn không muốn Excel tự động lấy vùng lọc trước đó thì bạn sử dụng các thủ tục trên.

Chúc các bạn thành công. Hy vọng bài viết trên sẽ giúp ích các bạn phần nào trong công việc.

Mọi góp ý xin gởi cho tôi theo địa chỉ email sau:

levanduyet@yahoo.com

Lê Văn Duyệt.