Phương thức SpecialCells trong User Defined Function (1 người xem)

[ndu96081631]

Tôi đang tìm hiểu về phương thức SpecialCells trong UDF. Tôi làm cuộc thì nghiệm sau:
A- THÍ NGHIỆM THỨ NHẤT: Đếm các cell rổng
Tôi làm như sau:

Sub Test()
  Dim Rng As Range, TempRng As Range
  Set Rng = Application.InputBox("Chon vung", Type:=8)
  Set TempRng = Rng.SpecialCells(4)
  MsgBox TempRng.Cells.Count
End Sub

Sub này chạy không có vân đề, nhưng khi chuyển sang UDF:

Function SpecCount(Rng As Range) As Long
  Dim TempRng As Range
  Set TempRng = Rng.SpecialCells(4)
  SpecCount = TempRng.Cells.Count
End Function

thì nó đếm sai bét hết cả
B- THÍ NGHIỆM THỨ HAI: Tính tổng các cell đang hiện (Visible cell)

Sub Test()
  Dim Rng As Range, VRng As Range, Clls As Range, Temp As Double
  Set Rng = Application.InputBox("Chon vung", Type:=8)
  Set VRng = Rng.SpecialCells(2, 1).SpecialCells(12)
  For Each Clls In VRng
    Temp = Temp + Clls
  Next
  MsgBox Temp
End Sub

Sub này chạy cũng không có vấn đề, nhưng khi chuyển nó thành UDF

Function SumSpec(Rng As Range) As Double
  Dim VRng As Range, Clls As Range, Temp As Double
  Set VRng = Rng.SpecialCells(2, 1).SpecialCells(12)
  For Each Clls In VRng
    Temp = Temp + Clls
  Next
  SumSpec = Temp
End Function

Thì lại chạy sai
Vậy xin hỏi nguyên nhân nằm ở chổ nào? Có cãm giác dường như phương thức SpecialCells chạy không chính xác trong các UDF ???
Nhờ các cao thủ giãi thích hộ!

 

[ndu96081631]

Ôi thôi! Tôi tìm được rồi!
Thì ra là:

UDF action being ignored.

Excel will not allow a UDF written in VBA to alter anything except the value of the cell in which it is entered.
You cannot make a VBA UDF which directly:

• Alters the value or formula or properties of another cell.
• Alters the formatting of the cell in which it is entered.
• Alters the environment of Excel. This includes the cursor.
• Uses FIND, SpecialCells, CurrentRegion, CurrentArray, GOTO, SELECT, PRECEDENTS etc : although you can use Range.End.
• Note you can use FIND in Excel 2002/2003.

Cãm ơn sự quan tâm của các bạn!
Tiếc quá đi mất với mấy cái will not allow này

 

[ChanhTQ@]

Cứ chân phương thì được

Option Explicit
Function SpecCount(Rng As Range) As Long
  Dim TempRng As Range
  For Each TempRng In Rng
   If TempRng.Value = "" Then SpecCount = SpecCount + 1
  Next TempRng
  Exit Function
End Function

Function SumSpec(Rng As Range) As Double
  Dim VRng As Range, Clls As Range, Temp As Double
  For Each Clls In Rng
    SumSpec = SumSpec + Clls
  Next
End Function

 

[yeudoi]

Bạn có thể giới thiệu thêm cho mọi người hiểu thêm về phương thức SpecialCells.
Mình thấy cái này cũng hay đấy.

 

[Po_Pikachu]

Tốt nhất Bác nên viết hết những gì bác biết về phương thức SpecialCells luôn đi, cho dễ thông kê sau này! Và cũng dễ học nữa. Chứ thiếu chổ này chỗ kia thì kỳ quá!
Thân

 

[SA_DQ]

Tốt nhất Bác nên viết hết những gì bác biết về phương thức SpecialCells

Các bạn có thể đến đây;
Trên diễn đàn ta cũng có nhưng không rõ bằng.

http://webketoan.vn/forum/showthread.php?t=51963

http://www.giaiphapexcel.com/forum/showthread.php?t=1278
http://www.giaiphapexcel.com/forum/showthread.php?p=7385​

 

[chibi]

Spam tí: Thằng SpecialCells hay ra phết.

 

[ndu96081631]

Option Explicit
Function SpecCount(Rng As Range) As Long
  Dim TempRng As Range
  For Each TempRng In Rng
   If TempRng.Value = "" Then SpecCount = SpecCount + 1
  Next TempRng
  Exit Function
End Function

Function SumSpec(Rng As Range) As Double
  Dim VRng As Range, Clls As Range, Temp As Double
  For Each Clls In Rng
    SumSpec = SumSpec + Clls
  Next
End Function

Vấn đề không phải là tìm giãi pháp (vì tôi làm được rồi, bằng cách khác)... Chủ yếu tôi muốn tìm hiểu tại sao phương thức SpecialCells không làm việc trong UDF thôi!
Cuối cùng đã phát hiện: Thì ra ông Bill ổng cấm như vậy! Đành bó tay

Tốt nhất Bác nên viết hết những gì bác biết về phương thức SpecialCells luôn đi, cho dễ thông kê sau này! Và cũng dễ học nữa. Chứ thiếu chổ này chỗ kia thì kỳ quá!
Thân

Cách học nhanh nhất về SpecialCells là bạn record lấy macro quá trình bạn thao tác Ctrl + G\Special ... chọn mục gì đó rồi xem macro ghi lại thế nào ---> Từ đó rút ra kinh nghiệm

 

[TrungChinhs]

Cách học nhanh nhất về SpecialCells là bạn record lấy macro quá trình bạn thao tác Ctrl + G\Special ... chọn mục gì đó rồi xem macro ghi lại thế nào ---> Từ đó rút ra kinh nghiệm

Tôi mới biết sơ qua một số chức năng của SpecialCells qua các bài viết trên GPE và làm thử làm theo cách của ndu thì thu được một số kết quả tạm dịch như sau:

1- Comments: Chọn các ô có ghi chú
2- Constants: Chọn các ô chứa hằng số (các ô không rỗng)
3- Formulas:
___3.1- Numbers: Chọn các ô có công thức trả về kết quả là số (vd B3 = 1+2)
___3.2- Text: Chọn các ô có công thức trả về kết quả là chữ (vd B3 = "C"&"h")
___3.3- Logicals: Chọn các ô có công thức trả về kết quả là câu Logical (vd B3 = true)
___3.4- Errors: Chọn các ô có công thức trả về kết quả là thông báo lỗi (vd B3 = #REF!)
4- Blanks: Chọn các ô trống (ô có giá trị = 0 hoặc "" không được coi là ô rỗng)
5- Current region: Chọn toàn bộ khối có dữ liệu liên tục (khối hình chữ nhật không bị chia cắt bởi dòng hoặc cột rỗng)
6- Current array: ?
- Objects: Chọn tất cả các đối tượng Objects
7- Row differences: ?
8- Column differences: ?
9- Precedents: ?
10- Dependents:
___10.1- Direct only: ?
___10.2- All levels: ?
11- Last cell: Chọn các ô cuối cùng trong range (khi rút dòng - thu nhỏ vùng phải Save trước khi chọn lệnh này thì kết quả mới đúng)
12- Visible cells only: Chọn tất cả các ô được hiển thị (không chọn các dòng ẩn)
13- Conditional formats: Chọn các ô có định dạng bằng Conditional formats
14- Data validation:
___14.1- All: Chọn các ô có Data validation
___14.2- Same: ?

Các ô màu đỏ đậm không biết có chức năng gì (riêng mục 7, 8 thì thấy nó tác dụng giống giống như mục 2) các mục khác khi thử nó báo No cells were found nên chịu luôn (mình không biết tiếng anh)
Nhờ các bạn kiểm tra, chỉnh sửa các mục đã dịch nếu thấy chưa sát. Dịch tiếp các mục còn lại, có ví dụ thì càng tốt. Thanks !

 

[ST-Lu!]

Theo em phần 7- Object không được coi là specialcell, các thông số về sau phần 7 phải giảm đi 1
ví dụ: visible cell only 13 tương ứng với specialcells(12)

 

[SA_DQ]

Với những hàm người dùng.

Mình còn gặp trường hợp như vầy nữa kia:
Viết xong hàm & thử ở cửa sổ Immediate thì kết quả hoàn toàn đúng, nhưng ra trang tính thì loạn số liệu;

Về chuyện này thì mình còn mù mờ lắm & vì vậy, chưa biết giải thích ra sao (?)

Rất mong nhận được những hướng dẫn. Rất cảm ơn!

 

[Lệnh Hồ Đại Hiệp]

Để hiểu chính xác về Special Cells Method mời các bác tham khảo ở đây chút xíu:
Qua đó mới hiểu chính xác các tham số của Method này: expression.SpecialCells(Type, Value)

http://msdn.microsoft.com/en-us/library/bb178148.aspx
http://www.ozgrid.com/VBA/special-cells.htm

--CV--

 

[TrungChinhs]

Theo em phần 7- Object không được coi là specialcell, các thông số về sau phần 7 phải giảm đi 1
ví dụ: visible cell only 13 tương ứng với specialcells(12)

@ ST-Lu: đúng như phát hiện của bạn, Macro không chấp nhận Selection.SpecialCells(7).Select mà phải là Selection.DrawingObjects.Select và Visible cell only 13 tương ứng với specialcells(12).
Bạn dịch tiếp các phần còn lại sang tiếng việt giúp (mình đã xem bài theo link của bạn Lệnh Hồ Đại Hiệp nhưng toàn tiếng anh mình không hiểu)

 

[ST-Lu!]

@ ST-Lu: đúng như phát hiện của bạn, Macro không chấp nhận Selection.SpecialCells(7).Select mà phải là Selection.DrawingObjects.Select và Visible cell only 13 tương ứng với specialcells(12).
Bạn dịch tiếp các phần còn lại sang tiếng việt giúp (mình đã xem bài theo link của bạn Lệnh Hồ Đại Hiệp nhưng toàn tiếng anh mình không hiểu)

Hic!
Tất cả những cái trên cũng hỏi anh NDU mà thôi. Có rất nhiều cái bôi đậm em chưa hiểu ví dụ current Array nó là cái gì?

Các cao thủ chỉ thêm

 

[ndu96081631]

Hic!
Tất cả những cái trên cũng hỏi anh NDU mà thôi. Có rất nhiều cái bôi đậm em chưa hiểu ví dụ current Array nó là cái gì?

Các cao thủ chỉ thêm

Nó dùng để chọn nguyên vùng chứa công thức mãng từ 1 cell có công thức mãng

Select a range of cells that contains an array formula
1. Click a cell in the array formula
2. On the Edit menu, click Go To.
3. Click Special.
4. Click Current array.

 

[leonguyenz]

Ở bài viết: [h=1]/(/hững ghi chép về phương thức SpecialCells[/h]

[B]Sub FillBlanks()[/B]
'-------------------
Case 5[COLOR=Blue] ‘đ: Xóa giá trị các ô chứa số liệu, nhưng không là công thức [/COLOR]
    ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, [COLOR=#ff0000][B]23[/B][/COLOR]).ClearContents
'------------------
[B]End Sub[/B]

Em tính hỏi trên topic đó, lại thấy topic này thảo luận nhiều về SpecialCells nên post bài ở đây.
Và:

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, [B][COLOR=#ff0000]23[/COLOR][/B])

Các Thầy cho em hỏi Value = 23 trong phương thức SpecialCells có ý nghĩa gì? Em đã search nhưng chưa có kết quả.
Xin cảm ơn!

 

[SA_DQ]

Chàng này hơn bị lười biếng;
Mình chỉ gợi í thôi nha (với E2003):
Vô menu Edit -> Go to ta sẽ vô tiếp hộp thoại "Go To Special'
Khi bấm vô nút Constants ta sẽ thấy
Lần lượt 4 nút được kích hoạt; Đó là

Numbers
Text
Logicals
Errors

Tiếp theo trên trang tính trống nào đó ta thữ tạo 4 loại dữ liệu trên đây ở 1 số ô

Sau đó cho chạy macro để xem những ô nào biến khỏi tầm mắt!

Chúc vui!

 

[ndu96081631]

Ở bài viết: /(/hững ghi chép về phương thức SpecialCells


Em tính hỏi trên topic đó, lại thấy topic này thảo luận nhiều về SpecialCells nên post bài ở đây.
Và:

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, [B][COLOR=#ff0000]23[/COLOR][/B])

Các Thầy cho em hỏi Value = 23 trong phương thức SpecialCells có ý nghĩa gì? Em đã search nhưng chưa có kết quả.
Xin cảm ơn!

Cách dễ nhất để tìm hiểu là bấm F1 hỏi anh Bill:
- Mở Excel
- Bấm Alt + F11
- Bấm Ctrl + G
- Gõ từ khóa SpecialCells
- Bôi đen từ khóa rồi F1 ---> Có ngay câu trả lời











Có phải tổng 4 thằng Value ấy sẽ = 23 không?

 

[leonguyenz]

Có phải tổng 4 thằng Value ấy sẽ = 23 không?

Kaka, hôm qua em thử, vẫn chưa hiểu lắm, cứ chọn cả 4 thằng nó ra 23, mà không hiểu, tưởng là nó mặc định, ai zè là "Tổng những thằng đã chọn".