Office VBA, mỗi ngày một ví dụ....

[paulsteigel]

Thế thì bạn gửi cho tớ nhé: ngocdd@itprog.gov.vn
Nick yahoo của tớ là dangdinhngoc, Okay! tớ lại phải quay lại vấn đề này vậy, mặc dù mdb security đã được bàn luận khá nhiều.... nhưng tớ sẽ không muốn đi lạc đề khỏi chủ đề Office VBA đâu!!!
Tks bạn nhiều nhé.
Tiện đây tôi bổ sung thêm 1 thuật toán mã hoá cũng khá tiện dụng là RC4 mặc dù hiện nay người ta đã ít dùng nó hơn:
* Chìa khoá mã hoá: AJHGJHSJAH6433ASJHASAL981467
Bạn hãy cất chìa khoá này đâu đó trong máy tính hoặc registry để tránh bị đánh cắp
* Thuật toán xử lý:
Cách gọi hàm mã hoá:***=RC4([chuỗi cần mã hoá],[chìa khoá]
Function RC4(ByRef pStrMessage, ByRef pStrKey)
Dim lBytAsciiAry(255)
Dim lBytKeyAry(255)
Dim lLngIndex
Dim lBytJump
Dim lBytTemp
Dim lBytY
Dim lLngT
Dim lLngX
Dim lLngKeyLength

' Validate data
If Len(pStrKey) = 0 Then Exit Function
If Len(pStrMessage) = 0 Then Exit Function

' transfer repeated key to array
lLngKeyLength = Len(pStrKey)
For lLngIndex = 0 To 255
lBytKeyAry(lLngIndex) = Asc(Mid(pStrKey, ((lLngIndex) Mod (lLngKeyLength)) + 1, 1))
Next

' Initialize S
For lLngIndex = 0 To 255
lBytAsciiAry(lLngIndex) = lLngIndex
Next

' Switch values of S arround based off of index and Key value
lBytJump = 0
For lLngIndex = 0 To 255

' Figure index to switch
lBytJump = (lBytJump + lBytAsciiAry(lLngIndex) + lBytKeyAry(lLngIndex)) Mod 256

' Do the switch
lBytTemp = lBytAsciiAry(lLngIndex)
lBytAsciiAry(lLngIndex) = lBytAsciiAry(lBytJump)
lBytAsciiAry(lBytJump) = lBytTemp

Next
lLngIndex = 0
lBytJump = 0
For lLngX = 1 To Len(pStrMessage)
lLngIndex = (lLngIndex + 1) Mod 256 ' wrap index
lBytJump = (lBytJump + lBytAsciiAry(lLngIndex)) Mod 256 ' wrap J+S()

' Add/Wrap those two
lLngT = (lBytAsciiAry(lLngIndex) + lBytAsciiAry(lBytJump)) Mod 256

' Switcheroo
lBytTemp = lBytAsciiAry(lLngIndex)
lBytAsciiAry(lLngIndex) = lBytAsciiAry(lBytJump)
lBytAsciiAry(lBytJump) = lBytTemp

lBytY = lBytAsciiAry(lLngT)

' Character Encryption ...
RC4 = RC4 & Chr(Asc(Mid(pStrMessage, lLngX, 1)) Xor lBytY)
Next

End Function

[paulsteigel]

Lâu quá hôm nay mới ghé qua được....
Hôm nay tớ kính biếu các bác một cách giúp Office hiển thị được các thông điẹm (Message) tiếng việt bằng Unicode dựng sẵn!.
Chúng ta thiết kế một form giống như form hiển thị bằng lệnh msgbox của VB sau đó đặt các nút bấm, hình ảnh cần thiết vào để xử lý.
Viết một hàm với tến là Msgbox() để override hàm msgbox của VB sau đó chúng ta sẽ có một msgbox mới có thể hiển thị các thông điệp tiếng việt Unicode rất tốt, hihi....
Ai muốn có mã nguồn thì gửi cho tớ nhé! ngocdd@itprog.gov.vn
Nay kính thư...

[namlh]

Chao ban,

Minh tao listbox trong Excel va viet 01 macro trong Excel de lay du lieu tu Access, nhung trong du lieu co mot so field la null nen se phat sinh loi:

Sub LisboxLoad()

Dim objMyConn, objMyRecordset, strSQL
Dim x, y, i, j As Single
Dim MyArray() As String
LstCustName.ColumnCount = 5
LstCustName.ColumnWidths = "2 in;0.5 in;0.5 in;0.5 in;0.5 in"
Set objMyConn = CreateObject("ADODB.Connection")
Set objMyRecordset = CreateObject("ADODB.Recordset")

objMyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=F:\Nam\Macro\Excel.mdb;"
objMyConn.Open
strSQL = "select * from CustomerInfo"
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open Source:=strSQL, CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, Options:=adCmdText
x = objMyRecordset.RecordCount
y = objMyRecordset.Fields.Count - 1
ReDim MyArray(x, y)
i = 0

While Not objMyRecordset.EOF
For j = 0 To y

If IsNull(objMyRecordset.Field(j)) Then
MyArray(i, j) = ""

Else
MyArray(i, j) = objMyRecordset.Fields(j)
End If

Minh dung ham isnull de kiem tra xem neu field null thi gan cho mang la "" nhung phat sinh loi khi dung ham isnull.

Ban vui long giup minh lam sao de tranh loi nay hoac co cach nao de lay du lieu nhieu cot vao Listbox trong truong hop co mot so filed null. Chang han:

1 2 3

Nam Le Hai
An
Phuong My

Cam on ban rat nhieu.

Nam

[paulsteigel]

Bạn Namlh à: Thế này nhé, bạn dùng hàm isnull không sai song nếu theo những gì bạn đăng ở đây thì bạn bị sai một số chỗ như thế này:
If IsNull(objMyRecordset.Field(j)) Then
Thuộc tính field không phải là như vậy mà là fields.
Bạn chỉ cần sửa thành fields là xong.
Tiện đây tôi cũng góp ý thêm với bạn một số chỗ:
Bạn nên khai báo kiểu dữ liệu cho các biến một cách hoàn chỉnh:
VB sẽ không coi i,j,k,l as single là một kiểu khai báo chuẩn mà nếu đã định khai báo thì phải làm thế này: i as single, j as single ....
Theo nữa do việc bạn không khai báo kiểu của objMyRecordset nên bạn đã không khai thác được chức năng tự hiển thị thuộc tính của IDE do đó đã gặp lỗi trên đây.
Tiếp nữa. Bạn đã xác định là listbox có 5 cột, vậy bạn cũng nên giới hạn cái Array ở 5 cột, vì nếu y ở đây không phải là 4 mà là hơn thế nữa thì có thể bạn sẽ phải khổ vì dữ liệu sẽ không hiện đúng như mình muốn...
Túm lại tôi chép ra một đoạn mã nguồn để bạn tiện tham khảo nhé:
Dim objMyConn As Connection, objMyRecordset As Recordset, strSQL As String
Dim x As Single, y As Single, i As Single, j As Single
Dim MyArray() As String
' nên sửa chỗ này, thay vì dùng scripting object, ta nên dùng tham chiếu thì tiện nhất. Hoặc nếu bạn muốn tránh việc sai thuộc tính thì nên đặt tham chiếu trước và khai báo như tôi đã làm sau đó bỏ tham chiếu và chuyển khai báo thành kiểu object .... hehe phức tạp quá đúng không? Nhưng cách này sẽ làm giảm khả năng gặp lỗi...
Set objMyConn = CreateObject("ADODB.Connection")
Set objMyRecordset = CreateObject("ADODB.Recordset")

objMyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=F:\Nam\Macro\Excel.mdb;"
objMyConn.Open
strSQL = "select * from CustomerInfo;"
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open Source:=strSQL, CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, Options:=adCmdText

' Get value for x and y properly
x = objMyRecordset.RecordCount
y = objMyRecordset.Fields.Count

' if you are not so sure howmany column shall be, Okay do this
LstCustName.ColumnCount = y + 1

' Okay for better viewing your list, check for columns and assign colum width properly
LstCustName.ColumnWidths = "2 in;0.5 in;0.5 in;0.5 in;0.5 in"

ReDim MyArray(x, y)
'i = 0 you dont need this as i will be automatically assiged with value of 0

While Not objMyRecordset.EOF
For j = 0 To y
If IsNull(objMyRecordset.Fields(j + 2)) Then
MyArray(i, j) = ""
Else
MyArray(i, j) = objMyRecordset.Fields(j + 2)
End If
Next
i = i + 1
objMyRecordset.MoveNext
Wend
LstCustName.List = MyArray
' better clean up the connection setting here
objMyRecordset.Close
objMyConn.Close
Nay kính thư...

[paulsteigel]

Kiểu này thì tớ phải đổi tên chủ đề thành mỗi tuần một ví dụ thôi! Công việc cũng nhiều nên bây giờ vào ddth mỗi ngày và viết bài cho nó thấy sao mà khó thế ... nhưng thôi dù sao tớ cũng cố gắng vậy.
Mình thật sự mong mỏi có thể viết một cách hoàn chỉnh về bộ tài liệu hướng dẫn VBA cho tất cả những ai muốn bắt đầu một việc gì đó với VBA từ bước đơn giản nhất cho đến bước phức tạp hơn một chút.
Theo yêu cầu của một số bạn mới bắt đầu với VBA, tôi sẽ quay lại từ đầu và cùng các bạn bước những bước đầu tiên để có thể viết cho mình được một ứng dụng đơn giản với VBA. Trong quá trình viết, có gì sai, rất mong các cao thủ đóng góp và cho ý kiến nhé. Tôi luôn hy vọng sẽ có ai đó cùng tham gia để giúp cho ý tưởng này ngày càng hoàn thiện.
Các bác cho ý kiến đóng góp nhé....

[paulsteigel]

Không thấy ai góp ý nhỉ…
Thôi thế cũng được tôi sẽ cứ bắt đầu. 10 bài viết đầu của tôi sẽ tập trung vào các vấn đề sau:
1. Sơ lược về Office VBA – 2 bài
2. Đặc tính cơ bản của VBA trong các ứng dụng Office – 2 bài
3. Kiến thức đầu tiên về Macro 3 bài
4. Thử làm một Macro đầu tiên với Excel – 2 bài
5. Giải thuật và một số kinh nghiệm về thuật toán – 1 bài. phần này tổng kết các kinh nghiệm lập trình trước các bài toán đơn giản - rất mong mọi người tham gia phần này đông vui nhé.
Sau khi hoàn thành 10 bài viết này, nếu có thêm yêu cầu, ta sẽ vẫn tiếp tục với phần 6 là Macro trong word, Power point … khoảng 3 bài nữa (chẳng hiểu sao mình lại thích số 13)
Kết thúc phần I tôi sẽ chuyển sang phần 2, các kỹ năng cho người mới bắt đầu với Access
Phần này dự định sẽ bao gồm các mục sau
1. Access dưới góc nhìn là một ứng dụng ( chủ yếu nói đến khả năng lập trình trong Access) – sơ lược thôi – cái này giúp các bạn xem Access có thể giúp bạn làm gì.
(Tôi vẫn chưa hình dung rõ là nên đặt đề mục này thế nào, các cao thủ xem và điều chỉnh giúp nhé.)
2. Cấu trúc dữ liệu - phần này tôi rất mong có đóng góp của các cao thủ về Data Schema và cấu trúc dữ liệu.
3. Làm việc với Form, báo cáo và Query trong acces – bao gồm một số ví dụ và hướng dẫn đơn giản. Ngoài ra cũng kèm theo một số thủ thuật.
4. Các kỹ thuật với thực đơn trong ứng dụng nhiều module – Một số ví dụ và các kỹ thuật đơn giản để quản lý thực đơn.
5. Đối tượng ActiveX và COM trong Access. – chưa xác định
6. Đóng gói và bảo mật. – chưa xác định
Trên đây là đề xuất ban đầu đối với loạt bài viết về Office VBA. Kính mong các bạn tham gia và cho ý kiến đóng góp nhé. Nếu có ý kiến trực tiếp, các bạn cứ thư vào ngocddang@gmail.com nhé.
Nay kính thư.

[Solana]

Toi co 1 so kho khan khi lam viec tren Excel va da post len dien dan nho tu van giup nhung khong thay ai tra loi. Hy vong post o day se duoc ban paulsteigel chieu co doc va tra loi giup

Toi co 2 sheet trong 1 file Excel. Sheet1 la danh sach doi tuong (vi dung nhu: hoten, ngaysinh, ...). Toi muon Sheet2 loc du lieu tu Sheet1 (vi du cac doi tuong sinh nam 1990), khi thay doi o Sheet1 thi Sheet2 cung tu dong cap nhat. Xin cac ban huong dan giup cach thuc hien

Xin cam on nhieu.

[paulsteigel]

Thực sự là tôi chưa hiểu ý bạn lắm song tôi cũng thử nói lại xem mình hiểu có đúng không nhé?
+ Bạn có một sheet lưu trữ các thông tin về họ tên, ngày sinh.
+ Sheet2 có các thông tin lấy từ sheet1
+ Bạn thay đổi một số dữ liệu ở sheet1 và sheet2 phải thay đổi theo.
Điều khó hiểu ở đây là: sheet2 lọc dữ liệu gì từ sheet1? Cơ cấu lọc thế nào.
Còn nếu chỉ để giải quyết những vấn đề tôi gạch đầu dòng thì chỉ cần hàm Vlookup cũng đã quá được rồi.
Bạn nói rõ hơn nhé, hoặc mail cho tôi: ngocdd@itprog.gov.vn
Nay kính thư

[Solana]

Cam on ban da tra loi.

Toi xin giai thich ro hon: Sheet2 co cac truong (cot) giong nhu Sheet1 nhung cac dong (row) chi khop voi nhung gia tri loc thoi. Vi du toi muon Sheet2 hien thi cac doi tuong sinh nam 1990 chang han. No tuong tu cau lenh SQL:

SELECT * FROM Sheet1 WHERE YEAR([ngaysinh])=1990

Dieu quan trong o day la ket qua tra ve nam o Sheet2

Toi da thu cac ham VLOOKUP, HLOOKUP deu khong duoc vi gia tri tra ve cua cac ham nay la value (hoac array of value), khong phai la cac records (hay rows)

Toi nghi day la van de rat nhieu nguoi quan tam. Rat mong duoc ban huong dan

[paulsteigel]

Vâng tôi xin trả lời thế này
+ Excel là công cụ hỗ trợ tính toán bảng tính mạnh song nó lại không phải là công cụ tốt để trích lọc dữ liệu dạng các truy vấn SQL. Bản thân Excel cần khá nhiều tài nguyên để phục vụ cho quá trình theo dõi tính toán và các công thức. Do vậy yêu cầu của bạn là, với mỗi thay đổi bạn thực hiện tại sheet1, ngay lập tức cần phản ánh tại sheet2 là có thể được nhưng không hiệu quả do quá trình trích lọc dữ liệu sẽ làm Excel bận bịu và làm chậm hệ thống. Để giải quyết hiện tượng này. Bạn nên thiết kế một macro, để mỗi khi bạn kết thúc điều chỉnh tại sheet1, hãy thực thi macro này bằng tay để Excel rảnh tay lọc dữ liệu và chuyển qua ghi đè lên sheet2.
Với ít dữ liệu (dưới 300 dòng) thì có vẻ ổn nhưng nếu nhiều hơn thì cũng mệt đấy, gõ xong bạn sẽ phải chờ 1 đến 2 giây đề dữ liệu update tại Sheet2. Do đó tôi khuyến nghị nên dùng cách update sau khi hoàn tất điều chỉnh tại sheet1.
+ Nếu bạn muốn tránh ghi đè dữ liệu thì bảng tính sheet1 của bạn cần có một đặc điểm nào đó để nhận dạng các dòng (giống như trường khoá trong bảng dữ liệu). Khi macro thực hiện nó sẽ tìm các dòng đó tại Sheet1 và điều chỉnh dữ liệu của dòng với đặc điểm đó tại sheet2.
Về Macro, tôi giới thiệu với bạn cách sau –Giả định rằng sheet2 chứa giá trị kết xuất của lọc tất cả các đối tượng có năm sinh là ***x từ sheet1.
Cách1: Bạn hãy dùng chức năng advance filter
Cách này nhanh song đôi khi không kiểm soát được kết quả.
Cách 2: lọc tuần tự
+ Kiểm tra tất cả các dòng, đến dòng nào có dữ liệu trùng với dữ liệu cần lọc thì tạm dừng
+ Tìm dòng có khoá giống với dòng vừa tìm thấy tại sheet1, nếu không thấy thì dừng ở dòng cuối cùng và copy cả dòng đó ở sheet1 sang sheet2. Nếu tìm thấy thì chỉ ghi đè các thông tin ở dòng này tại sheet1 sang.
Vậy bạn cần 2 thuật toán:
a. Thuật toán tìm dòng có dữ liệu giống dữ liệu cần lọc và trả về số dòng
b. Thuật toán copy dữ liệu.
Tôi gợi ý đến thế. Nếu bạn cần phải viết cụ thể thuật toán thì mail cho tôi nhé.
Nay kính thư