{"id":1463,"date":"2023-09-19T00:09:39","date_gmt":"2023-09-18T17:09:39","guid":{"rendered":"https:\/\/trannghiait.com\/?p=1463"},"modified":"2023-09-19T00:22:02","modified_gmt":"2023-09-18T17:22:02","slug":"tach-van-ban-thanh-hang-bang-vba-chuc-nang-tuy-chinh","status":"publish","type":"post","link":"https:\/\/trannghiait.com\/index.php\/2023\/09\/19\/tach-van-ban-thanh-hang-bang-vba-chuc-nang-tuy-chinh\/","title":{"rendered":"T\u00e1ch v\u0103n b\u1ea3n th\u00e0nh nhi\u1ec1u h\u00e0ng trong Excel b\u1eb1ng VBA (Ph\u1ea7n 2)"},"content":{"rendered":"\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_64 counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >M\u1ee5c l\u1ee5c<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/trannghiait.com\/index.php\/2023\/09\/19\/tach-van-ban-thanh-hang-bang-vba-chuc-nang-tuy-chinh\/#Neu_ban_khong_co_ham_TEXTSPLIT_trong_phien_ban_Excel_cua_minh_ban_co_the_giai_quyet_mot_cach_hieu_qua_la_tao_ham_cua_rieng_minh_bang_VBA\" title=\"N\u1ebfu b\u1ea1n kh\u00f4ng c\u00f3 h\u00e0m TEXTSPLIT trong phi\u00ean b\u1ea3n Excel c\u1ee7a m\u00ecnh, b\u1ea1n c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3 l\u00e0 t\u1ea1o h\u00e0m c\u1ee7a ri\u00eang m\u00ecnh b\u1eb1ng VBA.\">N\u1ebfu b\u1ea1n kh\u00f4ng c\u00f3 h\u00e0m TEXTSPLIT trong phi\u00ean b\u1ea3n Excel c\u1ee7a m\u00ecnh, b\u1ea1n c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3 l\u00e0 t\u1ea1o h\u00e0m c\u1ee7a ri\u00eang m\u00ecnh b\u1eb1ng VBA.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/trannghiait.com\/index.php\/2023\/09\/19\/tach-van-ban-thanh-hang-bang-vba-chuc-nang-tuy-chinh\/#Dat_ma_nay_o_dau\" title=\"\u0110\u1eb7t m\u00e3 n\u00e0y \u1edf \u0111\u00e2u?\">\u0110\u1eb7t m\u00e3 n\u00e0y \u1edf \u0111\u00e2u?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/trannghiait.com\/index.php\/2023\/09\/19\/tach-van-ban-thanh-hang-bang-vba-chuc-nang-tuy-chinh\/#Lam_cach_nao_de_su_dung_chuc_nang_tuy_chinh_VBA_nay\" title=\"L\u00e0m c\u00e1ch n\u00e0o \u0111\u1ec3 s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh VBA n\u00e0y?\">L\u00e0m c\u00e1ch n\u00e0o \u0111\u1ec3 s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh VBA n\u00e0y?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/trannghiait.com\/index.php\/2023\/09\/19\/tach-van-ban-thanh-hang-bang-vba-chuc-nang-tuy-chinh\/#Nhung_dieu_can_biet_khi_su_dung_ham_do_VBA_tao\" title=\"Nh\u1eefng \u0111i\u1ec1u c\u1ea7n bi\u1ebft khi s\u1eed d\u1ee5ng h\u00e0m do VBA t\u1ea1o\">Nh\u1eefng \u0111i\u1ec1u c\u1ea7n bi\u1ebft khi s\u1eed d\u1ee5ng h\u00e0m do VBA t\u1ea1o<\/a><\/li><\/ul><\/nav><\/div>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Neu_ban_khong_co_ham_TEXTSPLIT_trong_phien_ban_Excel_cua_minh_ban_co_the_giai_quyet_mot_cach_hieu_qua_la_tao_ham_cua_rieng_minh_bang_VBA\"><\/span>N\u1ebfu b\u1ea1n kh\u00f4ng c\u00f3 h\u00e0m TEXTSPLIT trong phi\u00ean b\u1ea3n Excel c\u1ee7a m\u00ecnh, b\u1ea1n c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3 l\u00e0 t\u1ea1o h\u00e0m c\u1ee7a ri\u00eang m\u00ecnh b\u1eb1ng VBA.<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u00e3 VBA s\u1ebd t\u1ea1o m\u1ed9t h\u00e0m chia v\u0103n b\u1ea3n trong m\u1ed9t \u00f4 th\u00e0nh c\u00e1c h\u00e0ng (d\u1ef1a tr\u00ean d\u1ea5u ph\u00e2n c\u00e1ch \u0111\u00e3 ch\u1ec9 \u0111\u1ecbnh).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>'Code developed by Sumit Bansal from https:\/\/trumpexcel.com\n\nFunction SplitCellToRows(CellValue As Range, Delimiter As String)\n    Dim SplitValues() As String\n    \n    'Split the value by the specified delimiter into an array\n    SplitValues = Split(CellValue.Value, Delimiter)\n    \n    'Go thorugh each element of the array and remove any leading or trailing spaces\n    For i = LBound(SplitValues) To UBound(SplitValues)\n            SplitValues(i) = Trim(SplitValues(i))\n    Next i\n    \n    'Return the array\n    SplitCellToRows = WorksheetFunction.Transpose(SplitValues)\n    \nEnd Function<\/code><\/pre>\n\n\n\n<p>H\u00e0m tr\u00ean (\u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 SplitCellToRows) nh\u1eadn hai \u0111\u1ed1i s\u1ed1:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Tham chi\u1ebfu \u00f4 c\u1ee7a \u00f4 c\u00f3 v\u0103n b\u1ea3n mu\u1ed1n chia th\u00e0nh h\u00e0ng<\/li>\n\n\n\n<li>D\u1ea5u ph\u00e2n c\u00e1ch trong d\u1ea5u ngo\u1eb7c k\u00e9p<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Dat_ma_nay_o_dau\"><\/span>\u0110\u1eb7t m\u00e3 n\u00e0y \u1edf \u0111\u00e2u?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 c\u00e1c b\u01b0\u1edbc \u0111\u1ec3 \u0111\u1eb7t m\u00e3 n\u00e0y v\u00e0o VB Editor:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Gi\u1eef ph\u00edm ALT r\u1ed3i nh\u1ea5n ph\u00edm F11.&nbsp;\u0110i\u1ec1u n\u00e0y s\u1ebd m\u1edf tr\u00ecnh so\u1ea1n th\u1ea3o VB.&nbsp;Ngo\u00e0i ra, b\u1ea1n c\u0169ng c\u00f3 th\u1ec3&nbsp;nh\u1ea5p v\u00e0o tab Nh\u00e0 ph\u00e1t tri\u1ec3n&nbsp;r\u1ed3i nh\u1ea5p v\u00e0o bi\u1ec3u t\u01b0\u1ee3ng Visual Basic trong d\u1ea3i b\u0103ng.<\/li>\n\n\n\n<li>Trong menu so\u1ea1n th\u1ea3o VB, b\u1ea5m v\u00e0o t\u00f9y ch\u1ecdn Ch\u00e8n v\u00e0 sau \u0111\u00f3 b\u1ea5m v\u00e0o M\u00f4-\u0111un.&nbsp;\u0110i\u1ec1u n\u00e0y s\u1ebd ch\u00e8n m\u1ed9t m\u00f4-\u0111un m\u1edbi n\u01a1i ch\u00fang ta s\u1ebd sao ch\u00e9p-d\u00e1n m\u00e3 ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh \u1edf tr\u00ean.<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2023\/09\/Insert-a-new-module-in-the-vb-editor.png\" alt=\"Ch\u00e8n m\u1ed9t m\u00f4-\u0111un m\u1edbi v\u00e0o tr\u00ecnh so\u1ea1n th\u1ea3o vb\" class=\"wp-image-40803\"\/><\/figure><\/div>\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li>Sao ch\u00e9p-d\u00e1n m\u00e3 VBA \u1edf tr\u00ean v\u00e0o c\u1eeda s\u1ed5 m\u00e3 m\u00f4-\u0111un.<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2023\/09\/Copy-and-paste-the-vba-code-into-the-module-code-window.png\" alt=\"Sao ch\u00e9p v\u00e0 d\u00e1n m\u00e3 vba v\u00e0o c\u1eeda s\u1ed5 m\u00e3 m\u00f4-\u0111un\" class=\"wp-image-40802\"\/><\/figure><\/div>\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li>B\u1ea5m v\u00e0o bi\u1ec3u t\u01b0\u1ee3ng L\u01b0u tr\u00ean thanh c\u00f4ng c\u1ee5 (ho\u1eb7c s\u1eed d\u1ee5ng Control + S)<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2023\/09\/Save-the-code.png\" alt=\"L\u01b0u m\u00e3\" class=\"wp-image-40800\"\/><\/figure><\/div>\n\n\n<ol class=\"wp-block-list\" start=\"5\">\n<li>\u0110\u00f3ng VB Editor \u0111\u1ec3 quay l\u1ea1i b\u1ea3ng t\u00ednh.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Lam_cach_nao_de_su_dung_chuc_nang_tuy_chinh_VBA_nay\"><\/span>L\u00e0m c\u00e1ch n\u00e0o \u0111\u1ec3 s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng t\u00f9y ch\u1ec9nh VBA n\u00e0y?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 v\u00ed d\u1ee5 v\u1ec1 \u0111\u1ecba ch\u1ec9 c\u1ee7a t\u00f4i trong \u00f4 A2 m\u00e0 t\u00f4i mu\u1ed1n chia th\u00e0nh c\u00e1c h\u00e0ng.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2023\/09\/Address-in-cell-A2-for-text-to-rows.png\" alt=\"\u0110\u1ecba ch\u1ec9 trong \u00f4 A2 cho v\u0103n b\u1ea3n th\u00e0nh h\u00e0ng\" class=\"wp-image-40783\"\/><\/figure><\/div>\n\n\n<p>T\u00f4i c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c b\u00ean d\u01b0\u1edbi \u0111\u1ec3 nh\u1eadn k\u1ebft qu\u1ea3 trong \u00f4 A4:=SplitCellToRows(A2,&#8221;,&#8221;&#8221;)<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2023\/09\/Using-the-vba-user-defined-function-in-the-worksheet.png\" alt=\"S\u1eed d\u1ee5ng h\u00e0m vba do ng\u01b0\u1eddi d\u00f9ng x\u00e1c \u0111\u1ecbnh trong b\u1ea3ng t\u00ednh\" class=\"wp-image-40801\"\/><\/figure><\/div>\n\n\n<p>C\u00f4ng th\u1ee9c tr\u00ean c\u00f3 hai \u0111\u1ed1i s\u1ed1:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>A2<\/strong>&nbsp;\u2013 \u0110\u00e2y l\u00e0 tham chi\u1ebfu \u00f4 c\u1ee7a \u00f4 c\u00f3 \u0111\u1ecba ch\u1ec9<\/li>\n\n\n\n<li><strong>\u201c,\u201d<\/strong>&nbsp;\u2013 V\u00ec d\u1ea5u ph\u00e2n c\u00e1ch c\u1ee7a t\u00f4i l\u00e0 d\u1ea5u ph\u1ea9y n\u00ean t\u00f4i \u0111\u00e3 ch\u1ec9 \u0111\u1ecbnh n\u00f3 trong d\u1ea5u ngo\u1eb7c k\u00e9p.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Nhung_dieu_can_biet_khi_su_dung_ham_do_VBA_tao\"><\/span>Nh\u1eefng \u0111i\u1ec1u c\u1ea7n bi\u1ebft khi s\u1eed d\u1ee5ng h\u00e0m do VBA t\u1ea1o<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>N\u1ebfu sau n\u00e0y mu\u1ed1n s\u1eed d\u1ee5ng l\u1ea1i ch\u1ee9c n\u0103ng n\u00e0y, b\u1ea1n c\u1ea7n l\u01b0u t\u1ec7p c\u1ee7a m\u00ecnh b\u1eb1ng ph\u1ea7n m\u1edf r\u1ed9ng s\u1ed5 l\u00e0m vi\u1ec7c h\u1ed7 tr\u1ee3 macro (.xlsm)<\/li>\n\n\n\n<li>Kh\u00f4ng gi\u1ed1ng nh\u01b0 c\u00e1c h\u00e0m trang t\u00ednh kh\u00e1c, H\u00e0m do ng\u01b0\u1eddi d\u00f9ng x\u00e1c \u0111\u1ecbnh (UDF) \u0111\u01b0\u1ee3c t\u1ea1o b\u1eb1ng vb s\u1ebd kh\u00f4ng hi\u1ec3n th\u1ecb cho b\u1ea1n b\u1ea5t k\u1ef3&nbsp;<em>th\u00f4ng tin<\/em>&nbsp;ho\u1eb7c tr\u1ee3 gi\u00fap n\u00e0o v\u1ec1 c\u00e1c \u0111\u1ed1i s\u1ed1 c\u1ee7a h\u00e0m.&nbsp;B\u1ea1n c\u1ea7n bi\u1ebft h\u00e0m s\u1ebd l\u1ea5y bao nhi\u00eau v\u00e0 lo\u1ea1i \u0111\u1ed1i s\u1ed1 n\u00e0o.<\/li>\n\n\n\n<li>N\u1ebfu b\u1ea1n chia s\u1ebb s\u1ed5 l\u00e0m vi\u1ec7c n\u00e0y v\u1edbi ng\u01b0\u1eddi kh\u00e1c, h\u00e0m n\u00e0y s\u1ebd kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng tr\u00ean t\u1ec7p c\u1ee7a h\u1ecd tr\u1eeb khi h\u1ecd c\u0169ng th\u00eam m\u00e3 h\u00e0m v\u00e0o VB Editor c\u1ee7a h\u1ecd.<\/li>\n<\/ul>\n\n\n\n<p>\u0110\u00e2y l\u00e0 m\u1ed9t s\u1ed1 ph\u01b0\u01a1ng ph\u00e1p b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng \u0111\u1ec3 chia v\u0103n b\u1ea3n th\u00e0nh c\u00e1c h\u00e0ng trong Excel.<\/p>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>N\u1ebfu b\u1ea1n kh\u00f4ng c\u00f3 h\u00e0m TEXTSPLIT trong phi\u00ean b\u1ea3n Excel c\u1ee7a m\u00ecnh, b\u1ea1n c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3 l\u00e0 t\u1ea1o h\u00e0m c\u1ee7a <a class=\"mh-excerpt-more\" href=\"https:\/\/trannghiait.com\/index.php\/2023\/09\/19\/tach-van-ban-thanh-hang-bang-vba-chuc-nang-tuy-chinh\/\" title=\"T\u00e1ch v\u0103n b\u1ea3n th\u00e0nh nhi\u1ec1u h\u00e0ng trong Excel b\u1eb1ng VBA (Ph\u1ea7n 2)\">[&#8230;]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-1463","post","type-post","status-publish","format-standard","hentry","category-kien-thuc-excel"],"_links":{"self":[{"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/posts\/1463","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/comments?post=1463"}],"version-history":[{"count":2,"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/posts\/1463\/revisions"}],"predecessor-version":[{"id":1470,"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/posts\/1463\/revisions\/1470"}],"wp:attachment":[{"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/media?parent=1463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/categories?post=1463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trannghiait.com\/index.php\/wp-json\/wp\/v2\/tags?post=1463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}