Đố vui về VBA!

anhtuan1066 · 30/12/07

Nhằm cũng cố kiến thức về VBA cho các bạn mới bắt đầu và cả những bạn đang ứng dụng mà chưa hiểu nhiều về nó, tôi mở topic này với mong mõi qua những câu hỏi vui, các bạn sẽ nhận định lại sự hiểu biết cũa mình... (Kễ cã chính tôi cũng đang tập tành nên có rất nhiều cái chưa biết)
Mong rằng topic sẽ mang đến cho các bạn những khám phá thú vị với những cái tưỡng chừng như đã biết
Mong nhận dc bài viết về câu đố cũa các cao thủ! Còn các bạn mới thì đừng ngại khi đưa ra ý kiến cũa mình.. Có sai có sữa sẽ hoàn thiện!
Tôi xin mỡ màn trước bằng 1 câu hỏi đơn giãn
ANH TUẤN

CÂU HỎI 1: Tại sao biến K ko hoạt động?
Tôi muốn khi nhấn vào 1 button thì cell A1 sẽ tăng lên 1 đơn vị... Tôi đã làm như sau:
-Tạo 1 Command Button (nút nhấn thuộc thanh Control Toolbox), click phải chuột lên nút nhấn, chọn View code, rồi gõ vào đoạn code sau:

PHP:

Private Sub CommandButton1_Click()
   K = K + 1
   Range("A1").Value = K
End Sub

Ban đầu K chưa có gì, xem như =0, nhấn nút lần thứ nhất thì K dc tăng thêm 1, vậy K hiện tại sẽ bằng 1, và gán K vào cell A1 thì đương nhiên A1 sẽ =1... Nhấn nút lần 2, K lại dc tăng thêm 1 nên hiện tại K sẽ =2 và cell A1 cũng sẽ =2... vân vân.. từ đó diễn tiến tiếp...
Hi.. hi.. Điều này nghe qua có vẽ rất hợp lý, ấy thế mà khi nhấn nút nó chỉ hoạt động dc duy nhất 1 lần (A1 = 1) rồi thôi ko nhút nhít nữa...
Các bạn có thể giãi thích tại sao lại như thế ko? Tại sao những lần nhấn nút sau đó K lại ko tăng thêm tí nào (vì thực tế A1 vẫn cứ = 1 hoài) ?
ANH TUẤN

sealand · 25/11/13

Câu đố này xuất phát từ việc khai báo biến mà hiện thời mình vẫn đang thắc mắc chưa hiểu rõ. Anh em tham khảo 2 đoạn Code cho CommandButton1 sau nha:

1/Code này nhấn sẽ thay đổi Title của Form:

Mã:

Private Sub CommandButton1_Click()
Dim Frm As Object
Set Frm = CommandButton1.Parent
Frm.Caption = "WELLCOME"
End Sub

2/Code sau không đổi Title mà lại in xuống nền Form:

Mã:

Private Sub CommandButton1_Click()
Dim Frm As MSforms.UserForm
Set Frm = CommandButton1.Parent
Frm.Caption = "WELLCOME"
End Sub

Hì, vừa trả lời vừa hỏi kể cũng kỳ.

siwtom · 25/11/13

sealand đã viết:
Câu đố này xuất phát từ việc khai báo biến mà hiện thời mình vẫn đang thắc mắc chưa hiểu rõ. Anh em tham khảo 2 đoạn Code cho CommandButton1 sau nha:

1/Code này nhấn sẽ thay đổi Title của Form:

Mã:

Private Sub CommandButton1_Click() Dim Frm As Object Set Frm = CommandButton1.Parent Frm.Caption = "WELLCOME" End Sub

2/Code sau không đổi Title mà lại in xuống nền Form:

Mã:

Private Sub CommandButton1_Click() Dim Frm As MSforms.UserForm Set Frm = CommandButton1.Parent Frm.Caption = "WELLCOME" End Sub

Hì, vừa trả lời vừa hỏi kể cũng kỳ.

Hơi bị bất ngờ đấy. Đúng là nhiều khi không biết con ma nó đi đường mòn nào.

Nếu ta có trên Form các controls như sau
View attachment 113002
và code

Mã:

Private Sub CommandButton1_Click()
Dim Frm As [COLOR=#ff0000]MSforms.UserForm[/COLOR] ' MSforms.Frame   ' Object
    Set Frm = CommandButton1.Parent
    Frm.Caption = "WELLCOME"
End Sub

Private Sub CommandButton2_Click()
Dim Frm As [COLOR=#ff0000]MSforms.UserForm[/COLOR]
    Set Frm = CommandButton2.Parent
    Frm.Caption = "WELLCOME"
End Sub

thì nhấn CommandButton2 cho WELLCOME ở dưới thanh tiêu đề, nhưng nhấn CommandButton1 thì có WELLCOME ở Frame2 vì đối tượng Frame2 được trả về. Trong CommandButton1_Click thì Frm có thể khai báo là Object, MSforms.Frame, MSforms.UserForm đều được và kết quả trả về luôn là Frame2, tức container trực tiếp của CommandButton2.

Trong 2 trường hợp dưới đây thì số lượng controls, chủng loại và cấu trúc đều như nhau nhưng tùy khai báo trong CommandButton1_Click

Với khai báo Dim Frm As MSforms.UserForm thì ta có
View attachment 113003

Với khai báo Dim Frm As Object hoặc Dim Frm As MSforms.Page ta có

Ai mà ngờ được những rắc rối nay? botay.toantap.com

thanhlanh · 26/11/13

ndu96081631 đã viết:
Giả sử tôi có 3 sheet
- Sheet thứ nhất có tên là "Nguyễn" và Sheet CodeName là "S1"
- Sheet thứ hai có tên là "Anh" và Sheet CodeName là "S2"
- Sheet thứ ba có tên là "Tuấn" và Sheet CodeName là "S3"
-----------
Giờ ta gõ:
- Cell A1 chữ S1
- Cell A2 chữ S2
- Cell A3 chữ S3
Xin hỏi: Dùng code thế nào để:
- Kết quả tại B1 là Nguyễn
- Kết quả tại B2 là Anh
- Kết quả tại B3 là Tuấn
-------------
Tức biến đầu vào là Sheet CodeName (dạng String) và kết quả đầu ra là Sheet Name (dạng String)

Câu hỏi ngược lại: Viết một hàm để tìm Sheet CodeName nếu biết Sheet Name. (Chỉ một dòng Code tương tự bài #521)
Mở rộng: Dùng vòng lặp duyệt qua các WorkSheets để đổi tên tất cả các Sheet CodeName.

Mời tất cả các bạn tham gia cho vui!

siwtom · 27/11/13

thanhlanh đã viết:
Câu hỏi ngược lại: Viết một hàm để tìm Sheet CodeName nếu biết Sheet Name. (Chỉ một dòng Code tương tự bài #521)
Mở rộng: Dùng vòng lặp duyệt qua các WorkSheets để đổi tên tất cả các Sheet CodeName.

Mời tất cả các bạn tham gia cho vui!

Mã:

Sub RenameSheetCodeName(ByVal wb As Workbook, ByVal SheetName As String, ByVal newSheetCodeName As String)
Dim CodeName As String
    CodeName = wb.Sheets(SheetName).CodeName    ' đọc Sheet CodeName nếu biết Sheet Name
    wb.VBProject.VBComponents(CodeName).name = newSheetCodeName
End Sub

thanhlanh · 27/11/13

siwtom đã viết:

Mã:

Sub RenameSheetCodeName(ByVal wb As Workbook, ByVal SheetName As String, ByVal newSheetCodeName As String)
Dim CodeName As String
    CodeName = wb.Sheets(SheetName).CodeName    ' đọc Sheet CodeName nếu biết Sheet Name
    wb.VBProject.VBComponents(CodeName).name = newSheetCodeName
End Sub

Chưa đạt yêu cầu anh siwtom ơi!
Làm khó sư phụ tí, hi hi ... lâu lâu mới có dịp múa rìu qua mắt thợ dại gi không múa, mình đúng sai tính sau.

huuthang_bd · 27/11/13

thanhlanh đã viết:
Chưa đạt yêu cầu anh siwtom ơi!
Làm khó sư phụ tí, hi hi ... lâu lâu mới có dịp múa rìu qua mắt thợ dại gi không múa, mình đúng sai tính sau.

PHP:

Sheets(SheetName).CodeName

Code này sao không đạt yêu cầu nhỉ? Mà cái này hình như có nói trên GPE rồi.

thanhlanh · 27/11/13

huuthang_bd đã viết:
PHP:

Sheets(SheetName).CodeName

Code này sao không đạt yêu cầu nhỉ? Mà cái này hình như có nói trên GPE rồi.

Cho danh mục các tên Sheet vào cột A, với dòng code đó, bạn làm sao để viết hàm để lấy CodeName các sheet đã liệt kê vào cột B đây?, yêu cầu là viết hàm mà, hàm đó cũng có thể dùng trong VBA.
Rồi thêm câu hỏi khuyến mãi nữa: Thủ tục đổi tên tất cả các Sheet CodeName trong các WorkSheets

PS: Bạn chỉ giùm đường dẫn chổ nào đã có "cái này" trên GPE!

ndu96081631 · 27/11/13

thanhlanh đã viết:
Cho danh mục các tên Sheet vào cột A, với dòng code đó, bạn làm sao để viết hàm để lấy CodeName các sheet đã liệt kê vào cột B đây?, yêu cầu là viết hàm mà, hàm đó cũng có thể dùng trong VBA.

Ủa! Anh nói sao em không hiểu nhỉ? Dòng code đó là đạt rồi còn gì? Có viết thành HÀM thì cũng là dòng code ấy thôi. Ví dụ:

Mã:

Function SheetCodeName(ByVal SheetName As String) As String
  SheetCodeName = Sheets(SheetName).CodeName
End Function

Hay anh còn ẩn ý gì trong "trái ổi" nữa?
--------------------------------
(Code này nếu viết rõ hơn phải có thêm biến Workbook nữa mới chính xác)

huuthang_bd · 27/11/13

thanhlanh đã viết:
Cho danh mục các tên Sheet vào cột A, với dòng code đó, bạn làm sao để viết hàm để lấy CodeName các sheet đã liệt kê vào cột B đây?, yêu cầu là viết hàm mà, hàm đó cũng có thể dùng trong VBA.
Rồi thêm câu hỏi khuyến mãi nữa: Thủ tục đổi tên tất cả các Sheet CodeName trong các WorkSheets

PS: Bạn chỉ giùm đường dẫn chổ nào đã có "cái này" trên GPE!

Hàm lấy CodeName

PHP:

 Function CodeShName(ByVal ShName As String) As String
CodeShName = Sheets(ShName).CodeName
End Function

Công thức cho B1

Mã:

=CodeShName(A1)

Sử dụng trong VBA

PHP:

Sub Test()
MsgBox CodeShName("Sheet1")
End Sub

Đổi tên tất cả các Sheet CodeName

PHP:

Sub ChangeAllCodeShName()
Dim Sh As Worksheet, i As Long
For Each Sh In Sheets
    If Sh.Type = xlWorksheet Then
        i = i + 1
        ActiveWorkbook.VBProject.VBComponents(Sh.CodeName).Name = "CodeShName" & i
    End If
Next
End Sub

Đây là một trong các Link nói về lấy và đổi CodeName của Sheet.
http://www.giaiphapexcel.com/forum/showthread.php?5048-Code-Name

thanhlanh · 27/11/13

huuthang_bd đã viết:
Hàm lấy CodeName

PHP:

Function CodeShName(ByVal ShName As String) As String CodeShName = Sheets(ShName).CodeName End Function

Công thức cho B1

Mã:

=CodeShName(A1)

Sử dụng trong VBA

PHP:

Sub Test() MsgBox CodeShName("Sheet1") End Sub

Đổi tên tất cả các Sheet CodeName

PHP:

Sub ChangeAllCodeShName() Dim Sh As Worksheet, i As Long For Each Sh In Sheets If Sh.Type = xlWorksheet Then i = i + 1 ActiveWorkbook.VBProject.VBComponents(Sh.CodeName).Name = "CodeShName" & i End If Next End Sub

Đây là một trong các Link nói về lấy và đổi CodeName của Sheet.
http://www.giaiphapexcel.com/forum/showthread.php?5048-Code-Name

Hì mình chịu bạn rồi, sory nha!, tại vì phương án của mình đưa ra hơi khác, không nghờ bạn lại có một cách khác
Mình dùng Properties tương tự như HLMT làm ở đây.

siwtom · 27/11/13

thanhlanh đã viết:
Cho danh mục các tên Sheet vào cột A, với dòng code đó, bạn làm sao để viết hàm để lấy CodeName các sheet đã liệt kê vào cột B đây?, yêu cầu là viết hàm mà, hàm đó cũng có thể dùng trong VBA.
Rồi thêm câu hỏi khuyến mãi nữa: Thủ tục đổi tên tất cả các Sheet CodeName trong các WorkSheets

PS: Bạn chỉ giùm đường dẫn chổ nào đã có "cái này" trên GPE!

Bắt bẻ thế thì bó tay rồi. Vì tôi lười nên viết một sub thôi nhưng có chỉ cách lấy tên. Còn chuyện viết thành hàm thì khó gì.

VetMini · 27/11/13

thanhlanh đã viết:
Cho danh mục các tên Sheet vào cột A, với dòng code đó, bạn làm sao để viết hàm để lấy CodeName các sheet đã liệt kê vào cột B đây?, yêu cầu là viết hàm mà, hàm đó cũng có thể dùng trong VBA.
Rồi thêm câu hỏi khuyến mãi nữa: Thủ tục đổi tên tất cả các Sheet CodeName trong các WorkSheets

...

Chẳng liên quan gì tới tôi nhưng xin phép cho tôi thắc mắc: tại sao lại phải dùng hàm? Nếu dùng hàm thì trả về cái gì? Công việc là lấy dữ liệu cột A, kết quả ghi vào cột B. Vậy thì mục đích của trị được hàm trả về là gì?

sealand · 27/11/13

Bạn cần Hàm thì ta vẫn sử dụng Hàm được chớ sao.
Mình ngại viết lại Code lấy danh sách từ sheet mà đổi luôn Sub của HuuThang thử xem

Mã:

Function ChangeAllCodeShName() As Boolean
Dim Sh As Worksheet, i As Long
For Each Sh In Sheets
    If Sh.Type = xlWorksheet Then
        i = i + 1
        ActiveWorkbook.VBProject.VBComponents(Sh.CodeName).Name = "CodeShName" & i
    End If
Next
ChangeAllCodeShName = True
End Function
'------------------------------------------------------------------------------------
Sub Test()
MsgBox ChangeAllCodeShName()

End Sub

Nhất định bạn không thể bảo đây chẳng phải là Hàm

ndu96081631 · 27/11/13

Sub chỉ chạy được bằng cách nhấn Button

- Giả định tôi có 1 Sub:

Mã:

Sub GiGiDo()
  .....
  .....
End Sub

- Tôi vẽ 1 Button và Assign Macro Button này với Sub GiGiDo
- Xin hỏi: Tôi phải viết gì trong Sub để chỉ cho phép chạy code khi nhấn vào Button mà thôi ---> Có nghĩa là không chạy được trực tiếp trong cửa sổ lập trình, cũng không chạy được bằng cách Alt + F8 để gọi Sub
-------------------
Nói thêm: Nội dung Sub GiGiDo các bạn muốn viết cái gì tùy ý, miễn đạt được mục đích như dòng màu xanh ở trên
Câu đố này rất dễ

sealand · 27/11/13

Thì bắt nó truyền tham số gì đó ví dụ

Mã:

Sub Test(Name As String)
MsgBox "OK"
End Sub

và nút lệnh cũng truyền vớ vẩn chi đó

Mã:

Private Sub CommandButton1_Click()
Test ""
End Sub

Tóm lại, Sub nhận tham số chỉ chạy từ 1 sub hay hàm khác có truyền cho nó tham số. Để tránh truyền ta có thể viết:

Mã:

Sub Test(Optional Name As String = "")
MsgBox "OK"
End Sub

Và Sub goi giờ có thể viết

Mã:

Private Sub CommandButton1_Click()
Test 
End Sub

thanhlanh · 27/11/13

siwtom đã viết:
Bắt bẻ thế thì bó tay rồi. Vì tôi lười nên viết một sub thôi nhưng có chỉ cách lấy tên. Còn chuyện viết thành hàm thì khó gì.

Không phải là bắt bẻ nhưng do khác với của em nên chưa biết cách sử dụng, cảm ơn anh

VetMini đã viết:
Chẳng liên quan gì tới tôi nhưng xin phép cho tôi thắc mắc: tại sao lại phải dùng hàm? Nếu dùng hàm thì trả về cái gì? Công việc là lấy dữ liệu cột A, kết quả ghi vào cột B. Vậy thì mục đích của trị được hàm trả về là gì?

Có lẽ bạn chưa biết Sheet CodeName, bạn xem hàm trong file bên dưới.

sealand đã viết:
Bạn cần Hàm thì ta vẫn sử dụng Hàm được chớ sao.
Mình ngại viết lại Code lấy danh sách từ sheet mà đổi luôn Sub của HuuThang thử xem

Mã:

Function ChangeAllCodeShName() As Boolean Dim Sh As Worksheet, i As Long For Each Sh In Sheets If Sh.Type = xlWorksheet Then i = i + 1 ActiveWorkbook.VBProject.VBComponents(Sh.CodeName).Name = "CodeShName" & i End If Next ChangeAllCodeShName = True End Function '------------------------------------------------------------------------------------ Sub Test() MsgBox ChangeAllCodeShName() End Sub

Nhất định bạn không thể bảo đây chẳng phải là Hàm

Em nhất định bảo đấy là hàm rồi nhưng không phải hàm em cần, hu hu ...

Còn đây là code của mình:

Mã:

Sub EditWorksheetCodenames()
    Dim Ws As Worksheet, i As Integer
    If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
    For Each Ws In ActiveWorkbook.Worksheets
        i = i + 1
        On Error Resume Next
        'Cells(i, 1) = Ws.Name
        Ws.Parent.VBProject.vbcomponents(Ws.CodeName).Properties("_CodeName") = "GPE" & i
        'On Error GoTo 0
    Next Ws
End Sub


Function shCodeName(ByVal shName As String) As String
    On Error Resume Next    ' Neu khong tim thay Sheet shName
    shCodeName = ActiveWorkbook.VBProject.vbcomponents(Sheets(shName).CodeName).Properties("_CodeName")
End Function

ndu96081631 · 27/11/13

sealand đã viết:
Thì bắt nó truyền tham số gì đó ví dụ

Mã:

Sub Test(Name As String) MsgBox "OK" End Sub

và nút lệnh cũng truyền vớ vẩn chi đó

Mã:

Private Sub CommandButton1_Click() Test "" End Sub

Tóm lại, Sub nhận tham số chỉ chạy từ 1 sub hay hàm khác có truyền cho nó tham số. Để tránh truyền ta có thể viết:

Mã:

Sub Test(Optional Name As String = "") MsgBox "OK" End Sub

Và Sub goi giờ có thể viết

Mã:

Private Sub CommandButton1_Click() Test End Sub

Hình như anh đang trả lời câu đố của em?
Em nói rõ là BUTTON cơ mà

ndu96081631 đã viết:
- Tôi vẽ 1 Button và Assign Macro Button này với Sub GiGiDo

BUTTON thì mới Assign Macro được chứ anh!
Còn anh thì lại sửa thành COMMANDBUTTON, 2 loại control khác nhau hoàn toàn
Thêm nữa, em ghi Sub GiGiDo() hoàn toàn không có cái vụ "tham số truyền" ở đây! Chỉ hỏi mọi người về NỘI DUNG của sub thôi
Tóm lại: Nội dung code của Sub này sẽ quyết định tất cả

ndu96081631 · 27/11/13

thanhlanh đã viết:

Còn đây là code của mình:

Mã:

Sub EditWorksheetCodenames()
    Dim Ws As Worksheet, i As Integer
    If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
    For Each Ws In ActiveWorkbook.Worksheets
        i = i + 1
        On Error Resume Next
        'Cells(i, 1) = Ws.Name
        Ws.Parent.VBProject.vbcomponents(Ws.CodeName).Properties("_CodeName") = "GPE" & i
        'On Error GoTo 0
    Next Ws
End Sub


Function shCodeName(ByVal shName As String) As String
    On Error Resume Next    ' Neu khong tim thay Sheet shName
    shCodeName = ActiveWorkbook.VBProject.vbcomponents(Sheets(shName).CodeName).Properties("_CodeName")
End Function

Cái này là do người ta cứ suy nghĩ quá xa mà không cho rằng vấn đề đơn giản hơn nhiều đấy anh à!
Lấy Codename đó là chuyện bình thường thôi

siwtom · 27/11/13

ndu96081631 đã viết:
- Giả định tôi có 1 Sub:

Mã:

Sub GiGiDo() ..... ..... End Sub

- Tôi vẽ 1 Button và Assign Macro Button này với Sub GiGiDo
- Xin hỏi: Tôi phải viết gì trong Sub để chỉ cho phép chạy code khi nhấn vào Button mà thôi ---> Có nghĩa là không chạy được trực tiếp trong cửa sổ lập trình, cũng không chạy được bằng cách Alt + F8 để gọi Sub
-------------------
Nói thêm: Nội dung Sub GiGiDo các bạn muốn viết cái gì tùy ý, miễn đạt được mục đích như dòng màu xanh ở trên
Câu đố này rất dễ

Cái này mà là trong Delphi thì quá dễ. Cái "khung" của ButtonClick là thế này

Mã:

procedure TForm1.Button1Click([COLOR=#ff0000]Sender[/COLOR]: TObject);
begin
    ' code A kiểm tra Sender
end;

Nếu ta nhấn Button1 và trong procedure/sub đọc ra thì Sender chính là đối tượng Button1.
Nhưng nếu ta gọi thế này:

Mã:

procedure TForm1.Button2Click(Sender: TObject);
begin
        Button1Click(Form1); '<-- tức CALL sub Button1Click
end;

thì code A sẽ trả về đối tượng Sender = Form1. THay cho Form1 có thể truyền bắt cứ con, cháu, chắt, chít nào của TObject (Object), tức class/object di truyền từ TObject (Object), hoặc truyền NIL (trong VBA tương ứng là NOTHING.
------------
Trong VBA thì chắc là Caller

Mã:

Sub GiGiDo()
    If TypeName(Application.Caller) = "String" Then
        If Application.Caller = "Button1" Then
            MsgBox "Xin moi xin moi"
        Else
            MsgBox "bye-bye"
        End If
    Else
        MsgBox "bye-bye"
    End If
End Sub

Sub test()
    GiGiDo
End Sub

Gọi test không được, nhấn Button2 (cùng macro) cũng không được

thanhlanh · 27/11/13

ndu96081631 đã viết:
Cái này là do người ta cứ suy nghĩ quá xa mà không cho rằng vấn đề đơn giản hơn nhiều đấy anh à!
Lấy Codename đó là chuyện bình thường thôi

Thì ndu cũng thường nhắc nhở rằng topic này là để vui, mới lạ, độc đáo mà.
Trước khi hỏi mình cũng tìm hiểu Properties của Sheet CodeName đâu có trên GPE đâu. Hỏi để tìm giải pháp mới, với mục đích làm phong phú cho Forum chớ chưa hẳn đã hay hoặc hay nhất.
Trước đây mình đã thấy một thấy một số cao thủ nói rằng Sheet CodeName là thuộc tính Read-only rất khó Edit bằng code sao gọi là chuyện bình thường được?

Đố vui về VBA!

Thành viên gạo cội

Thành viên gạo cội

Thành viên gắn bó

Thành viên tích cực

Thành viên gắn bó

Thành viên tích cực

Chuyên gia GPE

Thành viên tích cực

Huyền thoại GPE

Chuyên gia GPE

Thành viên tích cực

Thành viên gắn bó

Ăn cùng góc phố

Thành viên gạo cội

Huyền thoại GPE

Thành viên gạo cội

Thành viên tích cực

File đính kèm

Huyền thoại GPE

Huyền thoại GPE

Thành viên gắn bó

Thành viên tích cực