ARTICLE · 1207229
海外投标神器-数字金额转英文大写-Excel自定义函数实操教程
$ ./start_bid_os.sh --dhaka > loading modules ......... 100% > kernel: BID-OS v2.0| region: DHAKA, BD 海外投标神器 数字金额转英文大写（符合属地国表达习惯），一键编译 Excel VBA 自定义函数 ConvertBDTToEnglish 实操手册 [ PRESS ENTER TO CONTINUE _ ] |
// 01痛点现场：被英文大写支配的深夜[PAIN POINT] |
做孟加拉国项目的兄弟，先回答我一个灵魂拷问——
写标书、签合同、开信用证，金额栏是不是还得写符合孟加拉国表达习惯的英文大写金额？比如：1234567.89 塔卡，你得规规矩矩写成：
One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-Seven Taka and Eighty-Nine Paisa Only |
一行两行还好说，几十上百行的 BOQ 报价清单，你逐行手敲英文？手敲到抽筋只是体力问题，拼错单词、写错单复数、把 One Million 写成 One Millions，才是社死现场——评标专家一眼扫到，专业度评分当场掉地上。
更气人的是，VLOOKUP、SUMIFS 样样齐全的 Excel，偏偏没有一个“数字转英文金额”的自带函数。
[ ✓ ] 好消息 今天给你装备一枚我长期在线使用的 VBA 自定义函数：ConvertBDTToEnglish。塔卡数字进去，标准英文大写出来，小数部分自动转 Paisa。复制、粘贴、开火，全程手把手。
// 02先看跑分：回车即输出，整列一秒编译[DEMO] |
装好之后，在任意单元格输入：
> =ConvertBDTToEnglish(A2) |
A2 里放数字 1234567.89，回车，系统返回：
< One Million Two Hundred Thirty-Four Thousand Five Hundred < Sixty-Seven Taka and Eighty-Nine Paisa Only |
再来几组压力测试：
> =ConvertBDTToEnglish(500000) < Five Hundred Thousand Taka Only > =ConvertBDTToEnglish(1250.50) < One Thousand Two Hundred Fifty Taka and Fifty Paisa Only > =ConvertBDTToEnglish(0) < Zero Taka Only |
就这么简单。下拉填充，整列 BOQ 金额英文大写一秒生成，从此告别深夜单词听写。
// 03装机教程：六步启动，不会写代码也能装[SETUP] |
▸ STEP 1 / 打开文件 正常打开你要装函数的报价表。建议先另存一份副本，稳字当头。
▸ STEP 2 / 召唤 VBA 编辑器 齐按 Alt + F11，会弹出标题为 “Microsoft Visual Basic for Applications” 的新窗口——这就是你的改装车间。没反应就先点一下表格任意单元格，让 Excel 处于激活状态。
▸ STEP 3 / 插入模块 左侧“工程-VBAProject”面板里，找到当前文件名，右键 → 插入 → 模块，右侧出现空白代码窗口，那就是代码的机位。
▸ STEP 4 / 粘贴代码 把下面整段代码从 Option Explicit 到最后一个 End Function 完整复制进去，一个字符都别漏。
▸ STEP 5 / 关闭并另存 关掉 VBA 编辑器。重点：文件 → 另存为，类型必须选 “Excel 启用宏的工作簿 (*.xlsm)”。存成普通 .xlsx，代码会被直接吞掉，下次打开函数全没。
▸ STEP 6 / 开火调用 回到表格，空白单元格输入 =ConvertBDTToEnglish(A2)，回车。下拉整列，收工。
Option Explicit ' 将BDT金额转换为英文表达（支持大数字并修复语法错误） ' 用法: =ConvertBDTToEnglish(金额单元格) Function ConvertBDTToEnglish(ByVal Amount As Variant) As String IfIsEmpty(Amount) Then ConvertBDTToEnglish= "" ExitFunction EndIf IfTypeName(Amount) = "String" Then IfTrim(Amount) = "" Then ConvertBDTToEnglish= "" ExitFunction EndIf EndIf DimstrAmount As String DimnumericCheck As Double OnError Resume Next numericCheck= CDbl(Amount) IfErr.Number <> 0 Or numericCheck < 0 Then ConvertBDTToEnglish= "Invalid Input (Must be non-negative number)" ExitFunction EndIf OnError GoTo 0 strAmount= Format(Amount, "0.00") DimdotPos As Integer dotPos= InStr(strAmount, ".") DimwholeStr As String, decimalStr As String IfdotPos > 0 Then wholeStr= Left(strAmount, dotPos - 1) decimalStr= Mid(strAmount, dotPos + 1, 2) IfLen(decimalStr) < 2 Then decimalStr = decimalStr & String(2 - Len(decimalStr), "0") Else wholeStr= strAmount decimalStr= "00" EndIf IfwholeStr = "0" And decimalStr = "00" Then ConvertBDTToEnglish= "Zero Taka Only" ExitFunction EndIf DimResult As String IfwholeStr <> "0" Then Result= ConvertLargeNumberToWords(wholeStr) & " Taka" IfdecimalStr <> "00" Then Result= Result & " and " & ConvertNumberToWords(CLng(decimalStr)) & " Paisa" EndIf Else Result= ConvertNumberToWords(CLng(decimalStr)) & " Paisa" EndIf ConvertBDTToEnglish= ProperCase(Result) & " Only" End Function Function ConvertLargeNumberToWords(ByVal NumStr As String) As String Dimscales() As String scales= Split(",Thousand,Million,Billion,Trillion,Quadrillion,Quintillion,Sextillion,Septillion,Octillion,Nonillion,Decillion", ",") DimreversedStr As String, Chunk As String Dimi As Integer, scaleIndex As Integer Dimword As String NumStr= Trim(NumStr) DoWhile Left(NumStr, 1) = "0" And Len(NumStr) > 1 NumStr= Mid(NumStr, 2) Loop IfNumStr = "0" Then ConvertLargeNumberToWords= "" ExitFunction EndIf reversedStr= StrReverse(NumStr) scaleIndex= 0 i= 1 DoWhile i <= Len(reversedStr) Chunk= StrReverse(Mid(reversedStr, i, 3)) i= i + 3 IfVal(Chunk) > 0 Then DimchunkWord As String chunkWord= ConvertThreeDigits(CLng(Chunk)) IfscaleIndex > 0 Then chunkWord= chunkWord & " " & scales(scaleIndex) EndIf Ifword = "" Then word= chunkWord Else word= chunkWord & " " & word EndIf EndIf scaleIndex= scaleIndex + 1 IfscaleIndex > UBound(scales) Then scaleIndex = UBound(scales) Loop ConvertLargeNumberToWords= word End Function Function ConvertNumberToWords(ByVal Num As Long) As String IfNum = 0 Then ConvertNumberToWords= "" ExitFunction EndIf ConvertNumberToWords= ConvertThreeDigits(Num) End Function Function ConvertThreeDigits(ByVal Num As Integer) As String Dimones() As String DimTens() As String ones= Split("Zero,One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten,Eleven,Twelve,Thirteen,Fourteen,Fifteen,Sixteen,Seventeen,Eighteen,Nineteen", ",") Tens= Split(",,Twenty,Thirty,Forty,Fifty,Sixty,Seventy,Eighty,Ninety", ",") Dimword As String Dimhundreds As Integer DimRemainder As Integer hundreds= Num \ 100 Remainder= Num Mod 100 Ifhundreds > 0 Then word= ones(hundreds) & " Hundred" EndIf IfRemainder > 0 Then Ifhundreds > 0 Then word= word & " and " EndIf IfRemainder < 20 Then word= word & ones(Remainder) Else Dimten As Integer Dimone As Integer ten= Remainder \ 10 one= Remainder Mod 10 word= word & Tens(ten) Ifone > 0 Then word= word & "-" & ones(one) EndIf EndIf EndIf ConvertThreeDigits= word End Function Function ProperCase(ByVal text As String) As String Dimwords() As String Dimi As Integer Dimexceptions As Variant exceptions= Array("and", "in", "on", "of", "the", "a", "an") words= Split(text, " ") Fori = LBound(words) To UBound(words) IfTrim(words(i)) = "" Then GoTo NextWord IfInStr(words(i), "-") > 0 Then DimhyphenWords() As String hyphenWords= Split(words(i), "-") Dimj As Integer Forj = LBound(hyphenWords) To UBound(hyphenWords) IfNot IsInArray(LCase(hyphenWords(j)), exceptions) Then hyphenWords(j)= UCase(Left(hyphenWords(j), 1)) & LCase(Mid(hyphenWords(j), 2)) Else hyphenWords(j)= LCase(hyphenWords(j)) EndIf Nextj words(i)= Join(hyphenWords, "-") Else IfNot IsInArray(LCase(words(i)), exceptions) Then words(i)= UCase(Left(words(i), 1)) & LCase(Mid(words(i), 2) Else words(i)= LCase(words(i)) EndIf EndIf NextWord: Nexti ProperCase= Join(words, " ") End Function Function IsInArray(ByVal find As String, arr As Variant) As Boolean Dimelement As Variant IsInArray= False ForEach element In arr Ifelement = find Then IsInArray= True ExitFunction EndIf Nextelement End Function |
粘贴完，窗口里花花绿绿一大片，别慌，这就是编译就绪的样子。
// 04异常处理：四个你一定会撞上的 BUG[WARNING] |
[ ! ] 宏已被禁用 首次打开 .xlsm，顶部会弹黄色警告，点“启用内容”函数才能跑；不点的话单元格会报 #NAME?，本质就是宏没启动。
[ ! ] 标书别直接发函数版 业主电脑默认禁用宏，发 .xlsm 过去满屏 #NAME? 当场出丑。正确姿势：内部用函数生成，选中整列 → 复制 → 选择性粘贴为“值”，另存普通 .xlsx 再发，干干净净纯文本。
[ ! ] 负数会报错 函数只处理非负金额，输入负数返回 Invalid Input (Must be non-negative number)。报价都是正数，不影响使用。
[ ! ] 空单元格 A2 为空时返回空字符串，不报错；整列下拉也安全，有数字的行才生成英文。
// 05硬核参数：这枚函数强在哪[FEATURES] |
[CURRENCY] 塔卡专属 整数自动加 Taka，小数自动加 Paisa，结尾 Only，完全对齐孟加拉银行与合同的标准写法。 | [BIG NUMBER] 超大数字不溢出 按字符串逐段处理，几个亿的项目金额也不崩，不靠数值类型硬扛。 |
[PROPER CASE] 大小写自动规范 实词首字母大写，and / of / the 自动小写，连字符词也处理，免手动。 | [SEGMENT] 千分段位自动分段 Thousand / Million / Billion 自动归位，不会闹出 ten hundred 这种低级笑话。 |
// 06装备入库：搭一次，受益每个项目[SIGN OFF] |
这种小工具看着不起眼，关键时刻能救你大量时间。过去写标书，金额英文大写一栏要对着数字一个词一个词敲，敲完还得复查拼写；现在——输入数字，回车，完事。
工具的意义就在这里：你搭一次模板，以后每个项目都站在它的肩膀上。
$ echo "Bid submitted. Good luck in Dhaka." > Bid submitted. Good luck in Dhaka. [ SYSTEM LOGGED OFF — 200 OK ] |
代码都在上面，复制即用；装完记得存成 .xlsm 模板，下次新项目直接开机。有问题评论区见，祝各位投标顺利，中大标。
— END —
感谢读到这里
喜欢本期内容，欢迎点赞、关注❤️
往期精彩：
