Hàm VBA phân cách định dạng số

letientambn · 9/11/23

Chào ACE!
Em mới bắt đầu với VBA. Em có tạo hàm để chuyển đổi định dạng số sang chữ số có dấu phân cách hàng nghìn và phần thập phân.
Áp dụng hàm cho những ô giá trị số thông thường (như -0,1 hay 111,123 hay -100000,21234 hay 10000231...) thì được.
Tuy nhiên khi áp dụng với ô là kết quả của 1 hàm thì có trục trặc. Ví dụ như khi áp dụng với ô có giá trị như là: =9*1,1 hoặc =5*1000... thì được, nhưng khi áp dụng với =1,1*100 hay 100*1,1 thì báo lỗi value (mặc dù áp dụng với giá trị 110 chạy bình thường).
Code VBA em viết như sau. Mong nhận được sự giúp đỡ của ACE ạ! Em cảm ơn!
Function TEXTNUM(gt As Variant) As String
If gt < 0 Then dau = "-" Else dau = ""
duong = Abs(gt)
nguyen = Application.WorksheetFunction.RoundDown(duong, 0)
If duong = nguyen Then
ptp = ""
ElseIf duong <> nguyen Then
ptp = "," & mid(duong & "_", Application.WorksheetFunction.Find(",", duong & "_", 1) + 1, Application.WorksheetFunction.Find("_", duong & "_", 1) - Application.WorksheetFunction.Find(",", duong & "_", 1) - 1)
End If
a = nguyen
b = Len(a)
pn = ""
Do While b > 3
pn = "." & right(a, 3) & pn
b = b - 3
a = left(a, b)
Loop
If b <= 3 Then pn = a & pn
TEXTNUM = dau & pn & ptp
End Function

Maika8008 · 9/11/23

Không biết mục đích của bạn là gì nhưng tại sao không dùng hàm text của Excel?

letientambn · 9/11/23

Maika8008 đã viết:
Không biết mục đích của bạn là gì nhưng tại sao không dùng hàm text của Excel?

Hàm text không chuyển đổi linh hoạt được phần thập phân. Mỗi 1 số lại phải áp dụng 1 hàm text khác nhau (ví dụ 1000000000 thì xài text("#.###"), 1,1 thì xài text("#,#"), 2000,22 thì xài text("#.###,##"), do vậy rất rườm rà mà với biến số thay đổi thì lại phải đổi hàm text).

Maika8008 · 9/11/23

letientambn đã viết:
Hàm text không chuyển đổi linh hoạt được phần thập phân. Mỗi 1 số lại phải áp dụng 1 hàm text khác nhau (ví dụ 1000000000 thì xài text("#.###"), 1,1 thì xài text("#,#"), 2000,22 thì xài text("#.###,##"), do vậy rất rườm rà mà với biến số thay đổi thì lại phải đổi hàm text).

Vậy bạn thử thay nguyen = Application.WorksheetFunction.RoundDown(duong, 0) bằng
On Error Resume Next
nguyen = Val(Left(gt, Application.WorksheetFunction.Find(",", gt)))
If Err.Number Then nguyen = gt

letientambn · 9/11/23

Maika8008 đã viết:
Vậy bạn thử thay nguyen = Application.WorksheetFunction.RoundDown(duong, 0) bằng
On Error Resume Next
nguyen = Val(Left(gt, Application.WorksheetFunction.Find(",", gt)))
If Err.Number Then nguyen = gt

Em thử mà k được bác. Sửa vậy lỗi dấu ngăn cách hàng nghìn luôn. Bác có cách nào để lấy riêng toàn bộ phần thập phân k ạ?

Maika8008 · 9/11/23

letientambn đã viết:
Em thử mà k được bác. Sửa vậy lỗi dấu ngăn cách hàng nghìn luôn. Bác có cách nào để lấy riêng toàn bộ phần thập phân k ạ?

Tôi thử rồi mới gửi lên đấy chứ, sao lại không được. Bạn đưa con số nào bị lỗi lên xem!

huuthang_bd · 9/11/23

letientambn đã viết:
Hàm text không chuyển đổi linh hoạt được phần thập phân. Mỗi 1 số lại phải áp dụng 1 hàm text khác nhau (ví dụ 1000000000 thì xài text("#.###"), 1,1 thì xài text("#,#"), 2000,22 thì xài text("#.###,##"), do vậy rất rườm rà mà với biến số thay đổi thì lại phải đổi hàm text).

Định dạng trực tiếp thì hơi khó chứ dùng hàm thì muốn viết sao cũng được hết.

Mã:

=TEXT(A1,"#,##0"&IF(INT(A1)<>A1,"."&REPT("#",15),""))

letientambn · 9/11/23

Maika8008 đã viết:
Tôi thử rồi mới gửi lên đấy chứ, sao lại không được. Bạn đưa con số nào bị lỗi lên xem!

Em thay như bác mà test thử nó ra vậy. Không biết còn sửa chỗ nào khác nữa không

Bài đã được tự động gộp: 9/11/23

huuthang_bd đã viết:
Định dạng trực tiếp thì hơi khó chứ dùng hàm thì muốn viết sao cũng được hết.

Mã:

=TEXT(A1,"#,##0"&IF(INT(A1)<>A1,"."&REPT("#",15),""))

Trả kết quả với số nguyên dư phần thập phân bác

huuthang_bd · 9/11/23

letientambn đã viết:
Em thay như bác mà test thử nó ra vậy. Không biết còn sửa chỗ nào khác nữa không

Bài đã được tự động gộp: 9/11/23

Trả kết quả với số nguyên dư phần thập phân bác

Nếu máy bạn thiết lập như vậy thì công thức sửa dấu chấm thành dấu phẩy và ngược lại. Công thức như sau:

Mã:

=TEXT(A1;"#.##0"&IF(INT(A1)<>A1;","&REPT("#";15);""))

letientambn · 9/11/23

huuthang_bd đã viết:
Nếu máy bạn thiết lập như vậy thì công thức sửa dấu chấm thành dấu phẩy và ngược lại. Công thức như sau:

Mã:

=TEXT(A1;"#.##0"&IF(INT(A1)<>A1;","&REPT("#";15);""))

Dạ trả kết quả ok rồi bác. Em cảm ơn.
Tiếc hàm text này trong vba nó hiểu khác. Bác có cách nào thiết kế được Function cho nó k ạ?

huuthang_bd · 9/11/23

letientambn đã viết:
Dạ trả kết quả ok rồi bác. Em cảm ơn.
Tiếc hàm text này trong vba nó hiểu khác. Bác có cách nào thiết kế được Function cho nó k ạ?

Trong VBA cũng vậy thôi có khác gì đâu bạn.

Mã:

Function TEXTNUM(ByVal So As Double) As String
    Dim DinhDang As String
    DinhDang = "#,##0"
    If So <> Int(So) Then
        DinhDang = DinhDang & "." & String(15, "#")
    End If
    TEXTNUM = Format(So, DinhDang)
    If Application.International(xlDecimalSeparator) = "," Then
        TEXTNUM = Replace(TEXTNUM, ".", "_")
        TEXTNUM = Replace(TEXTNUM, ",", ".")
        TEXTNUM = Replace(TEXTNUM, "_", ",")
    End If
End Function

letientambn · 9/11/23

huuthang_bd đã viết:

Trong VBA cũng vậy thôi có khác gì đâu bạn.

Mã:

Function TEXTNUM(ByVal So As Double) As String
    Dim DinhDang As String
    DinhDang = "#,##0"
    If So <> Int(So) Then
        DinhDang = DinhDang & "." & String(15, "#")
    End If
    TEXTNUM = Format(So, DinhDang)
    If Application.International(xlDecimalSeparator) = "," Then
        TEXTNUM = Replace(TEXTNUM, ".", "_")
        TEXTNUM = Replace(TEXTNUM, ",", ".")
        TEXTNUM = Replace(TEXTNUM, "_", ",")
    End If
End Function

Em cảm ơn bác rất nhiều ạ!

VetMini · 9/11/23

huuthang_bd đã viết:
Định dạng trực tiếp thì hơi khó chứ dùng hàm thì muốn viết sao cũng được hết.

Mã:

=TEXT(A1,"#,##0"&IF(INT(A1)<>A1,"."&REPT("#",15),""))

Tôi có đề cập vụ này vài lần rồi. Thấy VBA dễ quá nên bà con lười học cách dùng bảng tính.
Gặp những người như vậy, bạn không có cách nào thuyết phục đâu. Họ có 1001 cớ để bảo vệ ý của mình.

VetMini · 9/11/23

Maika8008 đã viết:
Không biết mục đích của bạn là gì ...

Lý do người ta đồng bộ định dang cột là để dễ đọc.
Nhất là cột số. Định dạng là để có thể liếc sơ qua cột, biết được đại khái trình trạng dữ liệu chung (điển hình: tỷ lệ số lớn/ số nhỏ, có số nào đáng để ý - quá lớn , hoặc quá nhỏ)

Nếu không cần hoặc không biết cách quản lý dữ liệu thì người có thể đẻ ra hàng tá kiểu mà người ta cho rằng đẹp mắt.

Chú thích: Thằng/Mụ sếp nào đọc nổi cái báo cáo có số trình bày đủ kiểu như này thì tôi cũng phục chúng có thiên tài.

letientambn · 9/11/23

VetMini đã viết:
Lý do người ta đồng bộ định dang cột là để dễ đọc.
Nhất là cột số. Định dạng là để có thể liếc sơ qua cột, biết được đại khái trình trạng dữ liệu chung (điển hình: tỷ lệ số lớn/ số nhỏ, có số nào đáng để ý - quá lớn , hoặc quá nhỏ)

Nếu không cần hoặc không biết cách quản lý dữ liệu thì người có thể đẻ ra hàng tá kiểu mà người ta cho rằng đẹp mắt.

Chú thích: Thằng/Mụ sếp nào đọc nổi cái báo cáo có số trình bày đủ kiểu như này thì tôi cũng phục chúng có thiên tài.

Em ở bên định giá nên sử dụng nhiều kiểu dữ liệu (giá trị tài sản lớn, diện tích không nguyên hay tỉ lệ %...). Cơ bản nhiều loại dữ liệu nên mới muốn làm Function để xài cho tiện vì công việc thôi bác.

Bài đã được tự động gộp: 9/11/23

VetMini đã viết:
Lý do người ta đồng bộ định dang cột là để dễ đọc.
Nhất là cột số. Định dạng là để có thể liếc sơ qua cột, biết được đại khái trình trạng dữ liệu chung (điển hình: tỷ lệ số lớn/ số nhỏ, có số nào đáng để ý - quá lớn , hoặc quá nhỏ)

Nếu không cần hoặc không biết cách quản lý dữ liệu thì người có thể đẻ ra hàng tá kiểu mà người ta cho rằng đẹp mắt.

Chú thích: Thằng/Mụ sếp nào đọc nổi cái báo cáo có số trình bày đủ kiểu như này thì tôi cũng phục chúng có thiên tài.

Liên quan tới rất nhiều giấy tờ, hợp đồng nên cần sự chính xác cao.

cantl · 9/11/23

Bài này hợp lý ấy chứ các bác.
Nhiều khi cần diễn giải công thức mua nhà ở Phố cổ chẳng hạn: =rand()*100*1.000.000.000
Thì có dấu "." sẽ dễ hình dung hơn, và tránh nhập thiếu số.

Bài đã được tự động gộp: 9/11/23

letientambn đã viết:
Em ở bên định giá nên sử dụng nhiều kiểu dữ liệu (giá trị tài sản lớn, diện tích không nguyên hay tỉ lệ %...). Cơ bản nhiều loại dữ liệu nên mới muốn làm Function để xài cho tiện vì công việc thôi bác.

Bài đã được tự động gộp: 9/11/23

Liên quan tới rất nhiều giấy tờ, hợp đồng nên cần sự chính xác cao.

Có bản hoàn thiện chưa bạn?

letientambn · 9/11/23

cantl đã viết:
Bài này hợp lý ấy chứ các bác.
Nhiều khi cần diễn giải công thức mua nhà ở Phố cổ chẳng hạn: =rand()*100*1.000.000.000
Thì có dấu "." sẽ dễ hình dung hơn, và tránh nhập thiếu số.
View attachment 296578

Bài đã được tự động gộp: 9/11/23

Có bản hoàn thiện chưa bạn?

Có rồi bác
Function TEXTNUM(ByVal So As Double) As String
Dim DinhDang As String
DinhDang = "#,##0"
If So <> Int(So) Then
DinhDang = DinhDang & "." & String(15, "#")
End If
TEXTNUM = Format(So, DinhDang)
If Application.International(xlDecimalSeparator) = "," Then
TEXTNUM = Replace(TEXTNUM, ".", "_")
TEXTNUM = Replace(TEXTNUM, ",", ".")
TEXTNUM = Replace(TEXTNUM, "_", ",")
End If
If right(TEXTNUM, 1) = "," Then TEXTNUM = left(TEXTNUM, Len(TEXTNUM) - 1)
End Function

cantl · 9/11/23

letientambn đã viết:
=5*1000

Ủa, tớ tưởng hàm phải chuyển cái này về chuỗi =5*1.000 chứ. Mới đáng bàn nè.

Nếu mỗi số kia thì tớ format cells cho nhanh.

VetMini · 9/11/23

cantl đã viết:
Bài này hợp lý ấy chứ các bác.
Nhiều khi cần diễn giải công thức mua nhà ở Phố cổ chẳng hạn: =rand()*100*1.000.000.000
Thì có dấu "." sẽ dễ hình dung hơn, và tránh nhập thiếu số.
...

cantl đã viết:
Ủa, tớ tưởng hàm phải chuyển cái này về chuỗi =5*1.000 chứ. Mới đáng bàn nè.

Nếu mỗi số kia thì tớ format cells cho nhanh.

Đã nói gặp dân không biết cách đọc số trên bảng rồi.
Người ta đã muốn vòi code rồi thì có 1001 cớ để tránh công thức.
(không rành code, viết VBA để "hợp đồng nên cần sự chính xác cao" [sic]. Chịu thua)

letientambn · 10/11/23

VetMini đã viết:
Đã nói gặp dân không biết cách đọc số trên bảng rồi.
Người ta đã muốn vòi code rồi thì có 1001 cớ để tránh công thức.
(không rành code, viết VBA để "hợp đồng nên cần sự chính xác cao" [sic]. Chịu thua)

Có phải cái gì nó cũng được cho xài bảng đâu bác

)))) Hợp đồng bác ạ. Chứ mấy cái báo cáo xàm xàm thì xài bảng chứ ai rảnh mà chuyển qua định dạng chữ. Em mới thử tiếp xúc tìm hiểu nên không rành nhiều, mày mò mãi không được nên mới nhờ giúp đỡ thôi. Công thức dài mà áp dụng cho nhiều chỗ, nhiều dạng (nối chữ trong văn bản, không phải để ô riêng để format cell) chứ có phải cứ để cột mà kéo đâu. Nên em đã giải thích là "do công việc" rồi. May mà có bác huuthang_bd nhiệt tình giúp đỡ.

Bài đã được tự động gộp: 10/11/23

cantl đã viết:
Ủa, tớ tưởng hàm phải chuyển cái này về chuỗi =5*1.000 chứ. Mới đáng bàn nè.

Nếu mỗi số kia thì tớ format cells cho nhanh.

Dùng cái này để diễn giải mà bác. Ví dụ ô dữ liệu bảng có các cột diện tích, đơn giá, hệ số... thì hoàn toàn có thể dùng hàm này để nối với nhau để chuyển qua kiểu diện tích * đơn giá * hệ số. Mà cái này chủ yếu do diễn giải ra trong hợp đồng chứ mấy cái báo cáo thì để nguyên dữ liệu bảng đó là xong rồi.

Hàm VBA phân cách định dạng số

Thành viên mới

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

Thành viên mới

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

Thành viên mới

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

Chuyên gia GPE

Thành viên mới

File đính kèm

Chuyên gia GPE

Thành viên mới

Chuyên gia GPE

Thành viên mới

Ăn cùng góc phố

Ăn cùng góc phố

Thành viên mới

!!! Giải thoát !!!

Thành viên mới

!!! Giải thoát !!!

Ăn cùng góc phố

Thành viên mới