{"id":444,"date":"2025-08-26T16:00:06","date_gmt":"2025-08-26T16:00:06","guid":{"rendered":"https:\/\/loufab.alwaysdata.net\/blogaccess\/?p=444"},"modified":"2025-08-26T16:10:06","modified_gmt":"2025-08-26T16:10:06","slug":"vba-replace-vba-booste-au-regex","status":"publish","type":"post","link":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/2025\/08\/26\/vba-replace-vba-booste-au-regex\/","title":{"rendered":"VBA : Replace VBA boost\u00e9 au RegEx"},"content":{"rendered":"\n<p>La fonction <strong>Replace()<\/strong> de <strong>VBA <\/strong>est largement employ\u00e9e par les d\u00e9veloppeurs. Mais lorsqu&rsquo;ils souhaitent faire une recherche moins statique, ils regardent avec envie l&rsquo;op\u00e9rateur <strong>Like <\/strong>sans pouvoir faire un mix des 2.<br><br>Etant confront\u00e9 \u00e0 des traitements de <strong>chaines de caract\u00e8res<\/strong>, je me suis cr\u00e9\u00e9 une petite fonction d\u00e9riv\u00e9e du <strong>Replace, <\/strong>avec une touche de <strong>VBScript.RegEx<\/strong>.<\/p>\n\n\n\n<p>Voici ce que cela donne :<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">Function ReplaceReg(ByVal expression As String, ByVal findPattern As String, ByVal replace As String, _\n                    Optional ByVal caseSensitive As Boolean = False) As String\n'---------------------------------------------------------------------------------------\n' Procedure : ReplaceReg\n' Author    : Fabrice CONSTANS (C\u00b2ED - c2ed.software@gmail.com)\n' Date      : 24\/09\/2024\n' Purpose   : Un Replace() VBA avec du RegEx\n' Parameters: expression \u00e0 analyser, findPattern pattern regex, replace ce qui doit \u00eatre\n'             mis \u00e0 la place du pattern, caseSensitive tenir compte de la casse. \n' Copyright : \u00a9 L'int\u00e9gralit\u00e9 du code ci-dessous est la propri\u00e9t\u00e9 de son auteur (Author)\n'             Son utilisation est strictement limit\u00e9e au pr\u00e9sent logiciel.\n' Pattern regex : https:\/\/documentation.help\/MS-VBScript-fr\/vsproPattern.htm\n' Exemple de patterns :\n'LIKE      RegEx\n'chaton*   ^chaton.\n'chat*n    chat.n\n'chat[A,E] chat[AE]\n'chat=,    chat=.,\n'chat      chat$\n'---------------------------------------------------------------------------------------\n\n    On Error GoTo Errsub\n    Dim regEx As Object\n    Set regEx = CreateObject(VBScript.RegExp)\n    \n    With regEx\n        .pattern = pattern\n        .Global = True\n        .IgnoreCase = Not caseSensitive\n    End With\n    \n    ReplaceReg = regEx.replace(expression, replace)\n    Exit Function\n\nErrsub\n    ReplaceReg = inputText ' retourne le texte original en cas d\u2019erreur\n    \nEnd Function<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Le contrat d&rsquo;utilisation<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>expression <\/strong>: texte \u00e0 modifier.<\/li>\n\n\n\n<li><strong>findPattern <\/strong>: pattern RegEx conforme au standard <a href=\"https:\/\/documentation.help\/MS-VBScript-fr\/vsproPattern.htm\" data-type=\"link\" data-id=\"https:\/\/documentation.help\/MS-VBScript-fr\/vsproPattern.htm\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">VBScript.Regex<\/a><\/li>\n\n\n\n<li><strong>replace<\/strong> : texte de remplacement<\/li>\n\n\n\n<li><strong>caseSensitive <\/strong>indique si la casse doit \u00eatre prise en compte.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Les patterns<\/h2>\n\n\n\n<p>Les expressions r\u00e9guli\u00e8res <strong>RegEx <\/strong>sont bien plus puissant que le l&rsquo;op\u00e9rateur <strong>Like<\/strong>, mais plus complexe \u00e0 maitriser.<br>En revanche, l&rsquo;op\u00e9rateur <strong>Like <\/strong>est disponible \u00e0 la fois <strong>SQL <\/strong>et en <strong>VBA, <\/strong>o\u00f9 il fonctionne de la m\u00eame mani\u00e8re.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Remplacement global<\/h2>\n\n\n\n<p>Comme le <strong>Replace <\/strong>dans son utilisation le plus basique, notre fonction remplace toutes les occurrences rencontr\u00e9es. C&rsquo;est la propri\u00e9t\u00e9 <strong>Globle=True<\/strong> qui garantie se comportement. Si on utilise <strong>False<\/strong>, seule la premi\u00e8re occurrence sera trait\u00e9e.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Perfectible ? Toujours !<\/h2>\n\n\n\n<p>La fonction est perfectible : on pourrait y ajouter des options comme <strong>start <\/strong>ou <strong>count <\/strong>pour limiter les remplacements. Mais dans la majorit\u00e9 des cas, elle fait le job.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>start <\/strong>qui exclue les X premiers caract\u00e8re de l&rsquo;<strong>expression<\/strong><\/li>\n\n\n\n<li><strong>count <\/strong>qui op\u00e8re X remplacement<\/li>\n<\/ul>\n\n\n\n<p>Je n&rsquo;utilise jamais le <strong>Start<\/strong>, par contre le <strong>Count <\/strong>peut s&rsquo;av\u00e9rer utile dans certains cas.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Exemple d&rsquo;utilisation<\/h2>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">? replaceReg(\"ODBC;DRIVER={MySQL ODBC 8.0 UNICODE Driver};SERVER=192.168.10.1;DATABASE=baseLocale;Option=43;charset=UTF8;\",\";SERVER=.*?;\",\";SERVER=azure.microsoft.com;\")\nODBC;DRIVER={MySQL ODBC 8.0 UNICODE Driver};SERVER=azure.microsoft.com;DATABASE=baseLocale;Option=43;charset=UTF8;<\/pre>\n\n\n\n<p>On a remplac\u00e9 n&rsquo;importe quelle adresse du server par <strong>;SERVER=azure.microsoft.com;<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>Si vous traitez des cha\u00eenes complexes en <strong>VBA<\/strong>, pensez <strong>RegEx<\/strong>. Et si vous aimez cette fonction, n\u2019h\u00e9sitez pas \u00e0 l\u2019utiliser \u2014 en conservant l\u2019en-t\u00eate bien s\u00fbr.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>La fonction Replace() de VBA est largement employ\u00e9e par les d\u00e9veloppeurs. Mais lorsqu&rsquo;ils souhaitent faire une recherche moins statique, ils regardent avec envie l&rsquo;op\u00e9rateur Like sans pouvoir faire un mix des 2. Etant confront\u00e9 \u00e0 des traitements de chaines de &hellip; <a href=\"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/2025\/08\/26\/vba-replace-vba-booste-au-regex\/\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[7],"class_list":["post-444","post","type-post","status-publish","format-standard","hentry","category-code-vba","tag-code-vba"],"_links":{"self":[{"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/posts\/444","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/comments?post=444"}],"version-history":[{"count":9,"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/posts\/444\/revisions"}],"predecessor-version":[{"id":453,"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/posts\/444\/revisions\/453"}],"wp:attachment":[{"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/media?parent=444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/categories?post=444"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/loufab.alwaysdata.net\/blogaccess\/index.php\/wp-json\/wp\/v2\/tags?post=444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}