vba 使用 Excel 计算分子量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27050976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating Molecular Weight Using Excel
提问by Ryan_C
I have come across a bit of problem here. I have a spreadsheet with about 9,000 organic compounds and I am trying to compute the molecular weight of all of them.
我在这里遇到了一些问题。我有一个包含大约 9,000 种有机化合物的电子表格，我正在尝试计算所有这些化合物的分子量。
Normally, this would be easy: it's simply the number of elements in the molecular formula multiplied by the element's molecular weight and then you add them all up. The problem is, the spreadsheet has the molecular formulas listed out as a string.
通常，这很容易：它只是分子式中元素的数量乘以元素的分子量，然后将它们全部加起来。问题是，电子表格将分子式列为字符串。
For example, the molecular weight for "acetonitrile" is listed in a column as: C2H3N.
例如，“乙腈”的分子量在列中列为：C2H3N。
What I would like to do is write a function that scans that cell's contents and says, "Okay, every time I come across something that is text, look at the numbers immediately following it until you hit another text and then stop. Then, take that number and multiply by that particular element's molecular weight" (I will take care of the summation of the molecular weights later because I feel that is is the easy part).
我想做的是编写一个函数来扫描该单元格的内容并说：“好吧，每次我遇到文本时，查看紧随其后的数字，直到遇到另一个文本，然后停止。然后，取该数字乘以该特定元素的分子量”（我稍后会处理分子量的总和，因为我觉得这是容易的部分）。
Is this possible to do with Excel's built in functions, or do I have to use VBA (which I really don't have experience with). Any help here would be greatly appreciated.
这可能与 Excel 的内置函数有关，还是我必须使用 VBA（我真的没有经验）。这里的任何帮助将不胜感激。
回答by
While your request is marginally possible through some pretty complex (and CPU intensive) formulas using nothing but native Excel functions, a VBA User Defined Functionor UDFwould be vastly more appropriate. I'm not a chemist so please excuse the additions to your single sample I've provided as they were stolen shamelessly from an Internet page. TBH, I'm not even sure if I have half of the terminology correct.
虽然通过一些非常复杂（和 CPU 密集型）公式仅使用本机 Excel 函数，您的请求几乎是可能的，但 VBA用户定义函数或UDF会更合适。我不是化学家，所以请原谅我提供的单个样品中的添加物，因为它们是从互联网页面上无耻地偷来的。TBH，我什至不确定我是否有一半的术语是正确的。
?????
?????
Step 1 - Create a table of molecular weights and name it
步骤 1 - 创建一个分子量表并命名
You are going to require some form of cross-reference to retrieve the molecular weights from the element's periodic symbols. Here is what I scraped together. I'll supply a link to the full table of data in a sample workbook below.
您将需要某种形式的交叉引用来从元素的周期符号中检索分子量。这是我拼凑起来的。我将在下面的示例工作簿中提供指向完整数据表的链接。
?????
?????
With that on a worksheet named Element Data, go to Formulas ? Defined Names ? Name Mangerand give the cross-reference matrix a defined name.
在名为Element Data的工作表上，转到Formulas ? Defined Names ? Name Manger并为交叉引用矩阵指定一个定义的名称。
?????
?????
Here I've used a formula (=OFFSET('Element Data'!$A$1,0,0,COUNTA( 'Element Data'!$A:$A),6)) to define the range but the size of the data is fairly static so a cell range reference should be more than sufficient.
在这里，我使用了一个公式 ( =OFFSET('Element Data'!$A$1,0,0,COUNTA( 'Element Data'!$A:$A),6)) 来定义范围，但数据的大小是相当静态的，因此单元格范围引用应该绰绰有余。
Step 2 - Add the code for a User Defined Function
第 2 步 - 添加用户定义函数的代码
Tap Alt+F11and when the VBE opens, immediately use the pull-down menus to Insert ? Module(Alt+I+M). Paste the following into the new pane titled something like Book1 - Module1 (Code).
点击Alt+ F11，当 VBE 打开时，立即使用下拉菜单Insert ? Module( Alt+ I+ M)。将以下内容粘贴到名为Book1 - Module1 (Code)的新窗格中。
Public Function udf_Molecular_Weight(sCMPND As String) As Double
Dim sTMP As String, i As Long, sEL As String, sSB As String
Dim dAW As Double, dAWEIGHT As Double, dSUB As Long
sTMP = sCMPND: dAWEIGHT = 0: sSB = "0": sEL = vbNullString
Do While CBool(Len(sTMP))
sSB = "0": sEL = vbNullString
If Asc(Mid(sTMP, Application.Min(2, Len(sTMP)), 1)) > 96 Then
sEL = Left(sTMP, 2)
Else
sEL = Left(sTMP, 1)
End If
sTMP = Right(sTMP, Len(sTMP) - Len(sEL))
Do While IsNumeric(Left(sTMP, 1))
sSB = sSB & Int(Left(sTMP, 1))
sTMP = Right(sTMP, Len(sTMP) - 1)
Loop
'Debug.Print sEL & ":" & (Int(sSB) - (Not CBool(Int(sSB))))
dAWEIGHT = dAWEIGHT + Application.VLookup(sEL, ThisWorkbook.Names("tblPeriodic").RefersToRange, 6, False) * (Int(sSB) - (Not CBool(Int(sSB))))
Loop
udf_Molecular_Weight = dAWEIGHT
End Function
Public Function udf_Styled_Formula_Alt(sCMPND As String) As String
Dim sb As Long, sCOMPOUND As String
sCOMPOUND = sCMPND
For sb = 0 To 9
sCOMPOUND = Replace(sCOMPOUND, sb, ChrW(8320 + sb))
Next sb
udf_Styled_Formula_Alt = sCOMPOUND
End Function
Public Function udf_Unstyled_Formula_Alt(sCMPND As String) As String
Dim sb As Long, sCOMPOUND As String
sCOMPOUND = sCMPND
For sb = 0 To 9
sCOMPOUND = Replace(sCOMPOUND, ChrW(8320 + sb), sb)
Next sb
udf_Unstyled_Formula_Alt = sCOMPOUND
End Function
Only the first of those is pertinent to your posted question. The latter two stylizethe compound's chemical formula with Unicode subscript characters and reverse the process.
只有第一个与您发布的问题有关。后两者使用 Unicode 下标字符对化合物的化学式进行样式化，并反转该过程。
When you have completed the paste, tap Alt+Qto return to your worksheet. These UDF functions can now be used within your workbook just as any native Excel function can. The syntax is as simple as I could muster.
完成粘贴后，点击Alt+Q返回到您的工作表。这些 UDF 函数现在可以像任何本机 Excel 函数一样在您的工作簿中使用。语法尽可能简单。
=udf_Molecular_Weight(<single cell with compound formula in plain text>)
=udf_Molecular_Weight( <带有纯文本复合公式的单个单元格>)
For your sample compound (in the data image above) this would be,
对于您的样品化合物（在上面的数据图像中），这将是，
=udf_Molecular_Weight(B2)
=udf_Molecular_Weight(B2)
... or,
... 或者，
=udf_Molecular_Weight("C2H3N")
=udf_Molecular_Weight("C2H3N")
With 9000+ of these, I suspect you'll use the former method. Fill down as necessary. While this UDF is vastly more efficient than convoluted arrayformulas using INDIRECTand other native worksheet functions, they are not magic. Test the formula on a few hundred rows before committing to the 9000+ so you know what to expect. The other two UDFs work in much the same fashion should you choose to put them to use.
有了 9000 多个这些，我怀疑你会使用前一种方法。根据需要填写。虽然此 UDF 比使用和其他本机工作表函数的复杂数组公式高效得多INDIRECT，但它们并不神奇。在提交 9000+ 之前在几百行上测试公式，以便您知道会发生什么。如果您选择使用其他两个 UDF，它们的工作方式大致相同。
BRIEF EXPLANATION:
简要说明：
By 'variable declarations', I'm guessing you actually mean 'variable assignments'. I tend to write fairly tight code and I've taken what others would put into up to 4 code lines into a single line by stacking the zeroing of the variables with a colon. I turn this,
通过“变量声明”，我猜你实际上是指“变量赋值”。我倾向于编写相当紧凑的代码，并且通过将变量的清零与冒号堆叠在一起，我已经将其他人将最多 4 行代码放入一行。我转这个，
sTMP = sCMPND
dAWEIGHT = 0
sSB = "0"
sEL = vbNullString
... into this,
...进入这个，
sTMP = sCMPND: dAWEIGHT = 0: sSB = "0": sEL = vbNullString
The variables need to be reset before reentering the loops but it's a mundane task so I simply cram all four assignments into a single line.
在重新进入循环之前需要重置变量，但这是一项平凡的任务，所以我只是将所有四个分配塞进一行。
The two Do While ... Loopcrawl through the string that was passed into the function character by character. The inner loop deals exclusively with numbers. Each pass through the loop truncates the string from the left, shortening it by one or more characters and collecting those characters as either the symbol of a element or the number associated with its use in the organic compound. Eventually there is nothing left to truncate (length=0) and that is where CBool(Len(sTMP))becomes Falseand the loop ends. The inner loop performs much the same way but collects numeric digits until it reaches no length or an alphabetic character. After an element (and a possible numeric modifier) has been collected, the molecular weight for that element within the compound is calculated with a VLOOKUPagainst the molecular weight table and added to a growing number. When all elements and their associated number has been gathered and added into the grand total, the total is returned as the result of the function.
两者Do While ... Loop逐个字符地爬过传递给函数的字符串。内部循环专门处理数字。每次通过循环都会从左侧截断字符串，将其缩短一个或多个字符，并将这些字符收集为元素的符号或与其在有机化合物中的使用相关的数字。最终没有任何东西可以截断（长度= 0），这就是CBool(Len(sTMP))变为False并且循环结束的地方。内部循环的执行方式大致相同，但收集数字直到没有长度或字母字符为止。收集到一个元素（和可能的数字修饰符）后，化合物中该元素的分子量计算公式为VLOOKUP对照分子量表并添加到越来越多的数字中。当所有元素及其相关数字都已收集并添加到总计中时，总计将作为函数的结果返回。
回答by Rick Hitchcock
@Jeeped has a wonderful VBA solution to this. I posted a non-VBA solution to a related question at How to count up elements in excel. It's very easy to extend to this problem.
@Jeeped 对此有一个很棒的 VBA 解决方案。我在How to count up elements in excel 上发布了一个相关问题的非 VBA 解决方案。很容易扩展到这个问题。
Place each element in a separate column, with its atomic mass above it.
将每个元素放在一个单独的列中，其原子质量在其上方。
This formula will calculate the weight of each atom in the molecule:
这个公式将计算分子中每个原子的重量：
=B*
MAX(IFERROR(IF(FIND(B&ROW(:0),$A3),ROW(:0),0),0),
IFERROR(IF(FIND(B&CHAR(ROW(:)),$A3&"Z"),1,0),0)
)
Enter as an array formula: Ctrl+ Shift+ Enter.
输入作为数组公式： Ctrl+ Shift+ Enter。
The total molecular weight would be the sum of the weights.
总分子量将是重量的总和。
Example:
例子：
