Created
May 29, 2012 00:43
-
-
Save venj/2821913 to your computer and use it in GitHub Desktop.
old code for the lab
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Attribute VB_Name = "包合常数自动计算脚本" | |
| ' | |
| ' 作者: venj | |
| ' 日期: 2007年01月06日 | |
| ' 版本: 0.2.0 Update 1 | |
| ' | |
| ' 用途: 环糊精包合常数自动计算脚本 | |
| ' | |
| ' ChangeLog: | |
| ' 0.0.1 完成计算脚本的大体框架 | |
| ' 0.1.0 重新格式化输出结果表格格式，完全重写数值计算部分代码，增加多个变量 | |
| ' 0.1.1 增加另外两种作图方法 | |
| ' 0.1.2 增加选择作图方法的变量 | |
| ' 0.1.3 增加自动计算回归曲线斜率、截距的方法，将包合常数计算自动化 | |
| ' 0.2.0 修正了第三种曲线的错误的算法（事实上，错误仅仅是弄反了X轴和Y轴！修正的行都进行了简单的注释，并注上了日期。） | |
| ' 0.2.0 Update 1 再次修正第三种曲线的错误算法，错误不仅仅是弄错了坐标轴，X轴所引用的变量就是错误的。目前已经修正。 | |
| ' 修正了第三种作图方法的包合常数计算方法。（第一种作图方法的包合常数计算方法也可能是错误的。） | |
| ' 修正了在删除一个坐标点后，包合常数无法自动进行重新计算的错误。 | |
| ' | |
| ' | |
| ' TODO: | |
| ' 1. 增加另外两种作图方法（即求CD的包合常数的方法） (Done! ) | |
| ' 2. 将初始化表格的那个宏添加到表格加载的事件中 (Failed! ) | |
| ' 3. 对脚本进行优化 | |
| ' 4. 将半自动计算IC的Sub自动化 (Done!) | |
| ' 5. 在表格输出结果的格式上作一些小改动 | |
| ' 6. 开始着手用其他语言改写这个脚本 | |
| ' 7. 增加异常处理模块 | |
| ' 8. 增加选择作图方法的模块，以便于在3种图形输出之间选择 (Done! 用了3个if结构，避免了大量修改代码。) | |
| ' 9. 为了实现8，必须将作图模块作为单独的函数独立出来 (Done! 利用其他方法解决了8，这就不需要了。) | |
| ' 10. 原始数据的排序。由于实验数据从控制软件上读取时，有时会无故打乱，但是因为大多数情况下，原始数据是从小到大排列的，所以有增加排序模块的必要。加入存在可能不需要排序的情况，则可以增加一个判断变量，并给予默认值，排序。 | |
| ' | |
| ' | |
| ' | |
| ' | |
| ' 生成格式固定的表格 | |
| ' | |
| Sub Init_Tbl() | |
| ' | |
| ' 这个过程将被添加到空白表格的加载事件中 | |
| ' Init_Input Macro | |
| ' 初始化表格，等待用户输入吸光值（A0-An）、环糊精质量（mCD）和环糊精平均分子量（MCD） | |
| ' 以及其他的额外数据 | |
| ' | |
| ' | |
| ' 用户输入区。包括部分数据输出区。 | |
| ' | |
| Range("B2").Select | |
| ActiveCell.FormulaR1C1 = "波长(nm)" | |
| Range("D2").Select | |
| ActiveCell.FormulaR1C1 = "CD类型" | |
| Range("F2").Select | |
| ActiveCell.FormulaR1C1 = "pH" | |
| Range("H2").Select | |
| ActiveCell.FormulaR1C1 = "定容体积(mL)" | |
| Range("I2").Select | |
| ActiveCell.FormulaR1C1 = "10" ' 如果用户不修改这个数，将不作修改，大多数情况下，这个数值是正确的 | |
| Range("B3").Select | |
| ActiveCell.FormulaR1C1 = "注射针数" | |
| Range("D3").Select | |
| ActiveCell.FormulaR1C1 = "每针剂量(μL)" | |
| Range("F3").Select | |
| ActiveCell.FormulaR1C1 = "原始剂量(mL)" | |
| Range("G3").Select | |
| ActiveCell.FormulaR1C1 = "2.5" ' 如果用户不修改这个数，将不作修改，大多数情况下，这个数值是正确的 | |
| Range("B4").Select | |
| ActiveCell.FormulaR1C1 = "A0 -> An" | |
| Range("B5").Select | |
| ActiveCell.FormulaR1C1 = "CD质量(g)" | |
| Range("B6").Select | |
| ActiveCell.FormulaR1C1 = "CD摩尔质量(g/mol)" | |
| Range("F6").Select | |
| ActiveCell.Value = "图表选择" | |
| Range("G6").Select | |
| ActiveCell.Value = "2" '不需要对这个数据进行修改，默认值取2，一般来说，够用了。 | |
| Range("B7").Select | |
| ActiveCell.FormulaR1C1 = "CD物质的量(mol)" | |
| ' Range("B11").Select | |
| ' ActiveCell.Value = "a" ' 回归曲线的斜率 (这三个改在下面自动计算包合常熟时添加。) | |
| ' Range("D11").Select | |
| ' ActiveCell.Value = "b" ' 回归曲线的截距 | |
| ' Range("F11").Select | |
| ' ActiveCell.FormulaR1C1 = "包合常数(1/mol)" | |
| Range("F7").Select | |
| ActiveCell.FormulaR1C1 = "溶剂" | |
| Range("G7").Select | |
| ActiveCell.FormulaR1C1 = "水" | |
| Range("H7").Select | |
| ActiveCell.FormulaR1C1 = "药物名" | |
| Range("I7").Select | |
| ActiveCell.FormulaR1C1 = "某药" | |
| '增加部分默认字段，以减少平时非正式计算时输入的文字量（2006-10-13） | |
| Range("C2").Select | |
| ActiveCell.Value = " λ " ' 生成这个默认值，使用时选中单元格直接输入新值即可 | |
| Range("E2").Select | |
| ActiveCell.Value = "Beta-CD" ' 默认为Beta-CD | |
| Range("G2").Select | |
| ActiveCell.Value = "7.0" ' 默认为中性7.0 | |
| ' Range("C3").Select | |
| ' ActiveCell.Value = "8" ' 此处为注射针数，因为每次实验的组数不同，注释之 | |
| Range("E3").Select | |
| ActiveCell.Value = "50" ' 每针剂量也不同，可能为50或100。因为50比较常用，重新启用。 | |
| ' | |
| ' 生成表格主体格式 （不包括组号） | |
| ' | |
| Range("C13").Select | |
| ActiveCell.FormulaR1C1 = "A" | |
| Range("D13").Select | |
| ActiveCell.FormulaR1C1 = "[CD](mol/L)" | |
| Range("E13").Select | |
| ActiveCell.FormulaR1C1 = "1/[CD]" | |
| Range("F13").Select | |
| ActiveCell.FormulaR1C1 = "1/(A-A0)" | |
| Range("H13").Select ' 修正算法；将G列数据和H列数据相交换。2007-01-04 | |
| ActiveCell.FormulaR1C1 = "A-A0" | |
| Range("G13").Select ' 同上。 | |
| ActiveCell.FormulaR1C1 = "(A-A0)/[CD]" ' 修正错误的变量 [CD]/(A-A0) -> (A-A0)/[CD] 2007-01-06 | |
| End Sub | |
| Sub Data_Proc_All() | |
| ' | |
| ' 变量定义部分 | |
| ' | |
| Dim m_cd As String '定义CD物质的质量，即称量质量 | |
| Dim mw_cd As String '定义CD的分子量，即摩尔质量 | |
| Dim n_cd As Double '定义CD物质的量，即质量/分子量 | |
| Dim wl As String '定义波长，即选取数据的波长，一般为波峰处 | |
| Dim t_cd As String '定义CD名称。以后使用固定CD名，添加根据CD名称确定CD分子量的代码块 | |
| Dim ph As String '定义pH，用途暂时未明确 | |
| Dim n_inj As String '定义注射次数 | |
| Dim a_inj As String '定义每针剂量 | |
| Dim a_ori As String '定义原始剂量，即，打针前注入比色皿的液体体积，一般为2.5mL | |
| Dim v_cst As String '定义定容体积，即，实验使用的容量瓶体积（药品溶液定容CD的定容体积） | |
| Dim tmp_msg As Integer '定义消息框临时变量 | |
| Dim chrt_sel As String ' 定义图表选择变量 | |
| ' | |
| ' 变量赋值部分 | |
| ' | |
| ' 用户输入部分变量赋值，除吸光值外。吸光值输入将在表格填充，即数据处理部分完成。 | |
| ' | |
| Range("C5").Select | |
| m_cd = ActiveCell.Value | |
| Range("C6").Select | |
| mw_cd = ActiveCell.Value | |
| n_cd = (m_cd / mw_cd) | |
| Range("C2").Select | |
| wl = ActiveCell.Value | |
| Range("E2").Select | |
| t_cd = ActiveCell.Value | |
| Range("G2").Select | |
| ph = ActiveCell.Value | |
| Range("I2").Select | |
| v_cst = ActiveCell.Value | |
| Range("C3").Select | |
| n_inj = ActiveCell.Value | |
| Range("E3").Select | |
| a_inj = ActiveCell.Value | |
| Range("G3").Select | |
| a_ori = ActiveCell.Value | |
| Range("G6").Select | |
| chrt_sel = ActiveCell.Value | |
| ' | |
| ' 数据处理部分 | |
| ' | |
| ' 填充数据：组号和吸光值 | |
| ' | |
| ' | |
| ' 定义变量 | |
| ' | |
| Dim rng_no As String '定义组号单元格位置 | |
| Dim rng_cntr As Integer '计数器临时变量 | |
| Dim rng_a_ori As String '复制源吸光值的单元格位置 | |
| Dim rng_a_dst As String '复制到吸光值的单元格位置 | |
| Dim tmp_a As String '吸光值值传递中介 | |
| Dim rng_cd As String '体系的环糊精浓度 | |
| Dim tmp_cd As String '在[CD]到1/[CD]之间传递数值 | |
| Dim rng_cd_re As String '定义1/[CD]的单元格位置 | |
| Dim a_blnk As String '定义A0的值,也算是临时变量吧。 | |
| Dim rng_dlt_a As String '定义A-A0的单元格位置 | |
| Dim rng_dlt_a_re As String '定义1/(A-A0)的单元格位置 | |
| Dim rng_dlt_a_cd As String '定义(A-A0)/[CD]的单元格位置 | |
| rng_cntr = 0 '计数器初始化 | |
| ' | |
| ' 填充CD的物质的量的值 | |
| ' | |
| Range("C7").Select | |
| ActiveCell.Value = n_cd | |
| ' | |
| ' 为a_blnk赋A0的值 | |
| ' | |
| Range("C4").Select | |
| a_blnk = ActiveCell.Value | |
| ' | |
| ' 计算表格中各种数据的循环 | |
| ' 已经完全替换自动填充代码 | |
| ' | |
| For rng_cntr = 0 To n_inj | |
| ' | |
| '各组运算值的单元格位置赋值 | |
| ' | |
| rng_no = "B" & (14 + rng_cntr) | |
| rng_a_ori = Chr(Asc("C") + rng_cntr) & "4" | |
| rng_a_dst = "C" & (14 + rng_cntr) | |
| rng_cd = "D" & (14 + rng_cntr) | |
| rng_cd_re = "E" & (14 + rng_cntr) | |
| rng_dlt_a = "H" & (14 + rng_cntr) ' swap G and H 2007-01-4 | |
| rng_dlt_a_re = "F" & (14 + rng_cntr) | |
| rng_dlt_a_cd = "G" & (14 + rng_cntr) ' swap G and H 2007-01-4 | |
| ' | |
| ' 填充组号 | |
| ' | |
| Range(rng_no).Select | |
| ActiveCell.Value = rng_cntr | |
| ' | |
| ' 复制吸光值到目标单元格 | |
| ' | |
| Range(rng_a_ori).Select | |
| tmp_a = ActiveCell.Value | |
| Range(rng_a_dst).Select | |
| ActiveCell.Value = tmp_a | |
| ' | |
| ' 主算法部分[CD],1/[CD] | |
| ' | |
| ' | |
| ' [CD]的值 | |
| ' | |
| Range(rng_cd).Select | |
| ActiveCell.Value = ((n_cd / (v_cst * 0.001)) * a_inj * 0.000001 * rng_cntr) / ((a_ori + (a_inj * 0.001 * rng_cntr)) * 0.001) | |
| tmp_cd = ActiveCell.Value | |
| If ActiveCell.Value = "0" Then | |
| ' | |
| ' 表格结果区处A0外的第一行留空 | |
| ' | |
| ActiveCell.Value = "" | |
| Range(rng_cd_re).Select | |
| ActiveCell.Value = "" | |
| Range(rng_dlt_a).Select | |
| ActiveCell.Value = "" | |
| Range(rng_dlt_a_re).Select | |
| ActiveCell.Value = "" | |
| Range(rng_dlt_a_cd).Select | |
| ActiveCell.Value = "" | |
| Else | |
| ' | |
| ' 1/[CD]的值 | |
| ' | |
| Range(rng_cd_re).Select | |
| ActiveCell.Value = 1 / tmp_cd | |
| ' | |
| ' A-A0,1/(A-A0),(A-A0)/[CD]的值 | |
| ' | |
| Range(rng_dlt_a).Select | |
| ActiveCell.Value = tmp_a - a_blnk ' A-A0的值 | |
| Range(rng_dlt_a_re).Select | |
| ActiveCell.Value = 1 / (tmp_a - a_blnk) ' 1/(A-A0)的值 | |
| Range(rng_dlt_a_cd).Select | |
| ActiveCell.Value = (tmp_a - a_blnk) / tmp_cd ' (A-A0)/[CD]的值 (2007-01-06,修正错误的量) | |
| End If | |
| Next | |
| ' | |
| ' | |
| ' 生成结果表格的名称 | |
| ' | |
| ' | |
| Dim slv_nm As String '定义溶剂名，默认是"水" | |
| Dim drg_nm As String '定义药物名，默认是"某药" | |
| Range("G7").Select | |
| slv_nm = ActiveCell.Value | |
| Range("I7").Select | |
| drg_nm = ActiveCell.Value | |
| Range("C1:J1").Select | |
| With Selection | |
| .HorizontalAlignment = xlCenter | |
| .VerticalAlignment = xlCenter | |
| .WrapText = False | |
| .Orientation = 0 | |
| .AddIndent = False | |
| .IndentLevel = 0 | |
| .ShrinkToFit = False | |
| .ReadingOrder = xlContext | |
| .MergeCells = True | |
| End With | |
| Range("C1:J1").Select | |
| ActiveCell.Value = t_cd & "在pH" & ph & "的" & slv_nm & "溶液体系中对" & drg_nm & "的包合常数 (波长：" & wl & "nm)" | |
| ' | |
| ' | |
| ' 数据拟合部分 | |
| ' 即：作图 | |
| ' | |
| '(2006-10-13)添加自动计算包合常数模块。 | |
| ' | |
| ' 为了使作图功能在不同工作表中都可以使用，现在将常量：工作表名替换为变量 | |
| ' | |
| ' | |
| Dim sht_nm As String | |
| sht_nm = ActiveSheet.Name | |
| ' | |
| ' 用三个 If 结构选择图表的种类。 | |
| ' 都是VB的Select Cace结构在进行了一次Select之后就跳出Select结构了， | |
| ' 要是C语言的话，这3个 If 结构就可以用一个简单的Select结构做到。 | |
| ' | |
| '(2006-10-13)添加自动计算包合常数模块。（下面的部分代码段） | |
| ' 用一个选择结构计算包合常数的值 | |
| ' 尽管这样做使得代码十分冗长，但是这是最方便的办法了 | |
| ' 回归方程的形式：y = a * x + b | |
| ' | |
| ' | |
| ' 定义作图所需变量 | |
| ' 暂时不打算添加相关系数的表格单元 | |
| ' 因为不仅要计算r，还要计算r^2，增加的单元格太多 | |
| ' | |
| '这些变量已经被公式取代，不再需要，注释掉。 | |
| 'Dim slop_a_1 As String | |
| 'Dim inter_b_1 As String | |
| 'Dim slop_a_2 As String | |
| 'Dim inter_b_2 As String | |
| 'Dim slop_a_3 As String | |
| 'Dim inter_b_3 As String | |
| ' | |
| If chrt_sel = "1" Or chrt_sel = "12" Or chrt_sel = "13" Or chrt_sel = "123" Then | |
| ' 计算包合常数的值：选择作图方法 1； 计算图 1 的包合常数 | |
| Select Case n_inj | |
| Case "5" | |
| Range("B8").Select | |
| ActiveCell.Value = "A1" | |
| Range("C8").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[7]C[1]:R[11]C[1],R[7]C:R[11]C)" | |
| 'Range("C8").Select ' 这些变量已经被注释，不再需要。下同。见上方注释部分。 | |
| 'slope_a_1 = ActiveCell.Value | |
| Range("D8").Select | |
| ActiveCell.Value = "B1" | |
| Range("E8").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[7]C[-1]:R[11]C[-1],R[7]C[-2]:R[11]C[-2])" | |
| 'Range("E8").Select | |
| 'slope_b_1 = ActiveCell.Value | |
| Range("F8").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G8").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "6" | |
| Range("B8").Select | |
| ActiveCell.Value = "A1" | |
| Range("C8").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[7]C[1]:R[12]C[1],R[7]C:R[12]C)" | |
| 'Range("C8").Select | |
| 'slope_a_1 = ActiveCell.Value | |
| Range("D8").Select | |
| ActiveCell.Value = "B1" | |
| Range("E8").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[7]C[-1]:R[12]C[-1],R[7]C[-2]:R[12]C[-2])" | |
| 'Range("E8").Select | |
| 'slope_b_1 = ActiveCell.Value | |
| Range("F8").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G8").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "7" | |
| Range("B8").Select | |
| ActiveCell.Value = "A1" | |
| Range("C8").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[7]C[1]:R[13]C[1],R[7]C:R[13]C)" | |
| 'Range("C8").Select | |
| 'slope_a_1 = ActiveCell.Value | |
| Range("D8").Select | |
| ActiveCell.Value = "B1" | |
| Range("E8").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[7]C[-1]:R[13]C[-1],R[7]C[-2]:R[13]C[-2])" | |
| 'Range("E8").Select | |
| 'slope_b_1 = ActiveCell.Value | |
| Range("F8").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G8").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "8" | |
| Range("B8").Select | |
| ActiveCell.Value = "A1" | |
| Range("C8").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[7]C[1]:R[14]C[1],R[7]C:R[14]C)" | |
| 'Range("C8").Select | |
| 'slope_a_1 = ActiveCell.Value | |
| Range("D8").Select | |
| ActiveCell.Value = "B1" | |
| Range("E8").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[7]C[-1]:R[14]C[-1],R[7]C[-2]:R[14]C[-2])" | |
| 'Range("E8").Select | |
| 'slope_b_1 = ActiveCell.Value | |
| Range("F8").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G8").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "9" | |
| Range("B8").Select | |
| ActiveCell.Value = "A1" | |
| Range("C8").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[7]C[1]:R[15]C[1],R[7]C:R[15]C)" | |
| 'Range("C8").Select | |
| 'slope_a_1 = ActiveCell.Value | |
| Range("D8").Select | |
| ActiveCell.Value = "B1" | |
| Range("E8").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[7]C[-1]:R[15]C[-1],R[7]C[-2]:R[15]C[-2])" | |
| 'Range("E8").Select | |
| 'slope_b_1 = ActiveCell.Value | |
| Range("F8").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G8").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "10" | |
| Range("B8").Select | |
| ActiveCell.Value = "A1" | |
| Range("C8").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[7]C[1]:R[16]C[1],R[7]C:R[16]C)" | |
| 'Range("C8").Select | |
| 'slope_a_1 = ActiveCell.Value | |
| Range("D8").Select | |
| ActiveCell.Value = "B1" | |
| Range("E8").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[7]C[-1]:R[16]C[-1],R[7]C[-2]:R[16]C[-2])" | |
| 'Range("E8").Select | |
| 'slope_b_1 = ActiveCell.Value | |
| Range("F8").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G8").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case Else | |
| tmp_msg = MsgBox("你注射的针数不在5-10之间，请联系作者！", vbOKOnly, "警告！") | |
| Exit Sub | |
| End Select | |
| ' | |
| ' 对不同的注射次数选择不同的数据区域。 | |
| ' | |
| ' 我汗，居然一定要3行一起放在选择结构里面 | |
| ' 而且好像作图还是有时成功，有时失败的，晕死！ | |
| ' 不过，经过测试，暂未发现bug。 | |
| ' | |
| ' 线性：所得的点的线性；精确度：所得的包合常数的精确度。 | |
| ' | |
| ' 作图 1 | |
| ' 线性最好，精确度最差的一种方法 | |
| ' | |
| ' x <-> y: A <-> [CD] | |
| ' | |
| Select Case n_inj | |
| Case "5" | |
| Range("C15:D19").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("C15:D19") | |
| Case "6" | |
| Range("C15:D20").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("C15:D20") | |
| Case "7" | |
| Range("C15:D21").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("C15:D21") | |
| Case "8" | |
| Range("C15:D22").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("C15:D22") | |
| Case "9" | |
| Range("C15:D23").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("C15:D23") | |
| Case "10" | |
| Range("C15:D24").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("C15:D24") | |
| Case Else | |
| tmp_msg = MsgBox("你注射的针数不在5-10之间，请联系作者！", vbOKOnly, "警告！") | |
| Exit Sub | |
| End Select | |
| ActiveChart.Location Where:=xlLocationAsObject, Name:=sht_nm | |
| ActiveChart.PlotArea.Select | |
| Selection.ClearFormats | |
| ActiveChart.Axes(xlValue).MajorGridlines.Select | |
| Selection.Delete | |
| ActiveChart.SeriesCollection(1).Select | |
| ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _ | |
| Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select | |
| ActiveChart.ChartArea.Select | |
| End If | |
| If chrt_sel = "2" Or chrt_sel = "23" Or chrt_sel = "12" Or chrt_sel = "123" Then | |
| ' | |
| ' 计算包合常数的值：选择作图方法 2； 计算图 2 的包合常数 | |
| Select Case n_inj | |
| Case "5" | |
| Range("B9").Select | |
| ActiveCell.Value = "A2" | |
| Range("C9").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[6]C[3]:R[10]C[3],R[6]C[2]:R[10]C[2])" | |
| 'Range("C9").Select | |
| 'slope_a_2 = ActiveCell.Value | |
| Range("D9").Select | |
| ActiveCell.Value = "B2" | |
| Range("E9").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[6]C[1]:R[10]C[1],R[6]C:R[10]C)" | |
| 'Range("E9").Select | |
| 'slope_b_2 = ActiveCell.Value | |
| Range("F9").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G9").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "6" | |
| Range("B9").Select | |
| ActiveCell.Value = "A2" | |
| Range("C9").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[6]C[3]:R[11]C[3],R[6]C[2]:R[11]C[2])" | |
| 'Range("C9").Select | |
| 'slope_a_2 = ActiveCell.Value | |
| Range("D9").Select | |
| ActiveCell.Value = "B2" | |
| Range("E9").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[6]C[1]:R[11]C[1],R[6]C:R[11]C)" | |
| 'Range("E9").Select | |
| 'slope_b_2 = ActiveCell.Value | |
| Range("F9").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G9").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "7" | |
| Range("B9").Select | |
| ActiveCell.Value = "A2" | |
| Range("C9").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[6]C[3]:R[12]C[3],R[6]C[2]:R[12]C[2])" | |
| 'Range("C9").Select | |
| 'slope_a_2 = ActiveCell.Value | |
| Range("D9").Select | |
| ActiveCell.Value = "B2" | |
| Range("E9").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[6]C[1]:R[12]C[1],R[6]C:R[12]C)" | |
| 'Range("E9").Select | |
| 'slope_b_2 = ActiveCell.Value | |
| Range("F9").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G9").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "8" | |
| Range("B9").Select | |
| ActiveCell.Value = "A2" | |
| Range("C9").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[6]C[3]:R[13]C[3],R[6]C[2]:R[13]C[2])" | |
| 'Range("C9").Select | |
| 'slope_a_2 = ActiveCell.Value | |
| Range("D9").Select | |
| ActiveCell.Value = "B2" | |
| Range("E9").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[6]C[1]:R[13]C[1],R[6]C:R[13]C)" | |
| 'Range("E9").Select | |
| 'slope_b_2 = ActiveCell.Value | |
| Range("F9").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G9").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "9" | |
| Range("B9").Select | |
| ActiveCell.Value = "A2" | |
| Range("C9").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[6]C[3]:R[14]C[3],R[6]C[2]:R[14]C[2])" | |
| 'Range("C9").Select | |
| 'slope_a_2 = ActiveCell.Value | |
| Range("D9").Select | |
| ActiveCell.Value = "B2" | |
| Range("E9").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[6]C[1]:R[14]C[1],R[6]C:R[14]C)" | |
| 'Range("E9").Select | |
| 'slope_b_2 = ActiveCell.Value | |
| Range("F9").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G9").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case "10" | |
| Range("B9").Select | |
| ActiveCell.Value = "A2" | |
| Range("C9").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[6]C[3]:R[15]C[3],R[6]C[2]:R[15]C[2])" | |
| 'Range("C9").Select | |
| 'slope_a_2 = ActiveCell.Value | |
| Range("D9").Select | |
| ActiveCell.Value = "B2" | |
| Range("E9").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[6]C[1]:R[15]C[1],R[6]C:R[15]C)" | |
| 'Range("E9").Select | |
| 'slope_b_2 = ActiveCell.Value | |
| Range("F9").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G9").Select | |
| ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]" | |
| Case Else | |
| tmp_msg = MsgBox("你注射的针数不在5-10之间，请联系作者！", vbOKOnly, "警告！") | |
| Exit Sub | |
| End Select | |
| ' | |
| ' | |
| ' 作图 2 | |
| ' 线性、精确性最平衡的一种 | |
| ' | |
| ' x <-> y: 1/[CD] <-> 1/(A-A0) | |
| ' | |
| Select Case n_inj | |
| Case "5" | |
| Range("E15:F19").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("E15:F19") | |
| Case "6" | |
| Range("E15:F20").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("E15:F20") | |
| Case "7" | |
| Range("E15:F21").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("E15:F21") | |
| Case "8" | |
| Range("E15:F22").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("E15:F22") | |
| Case "9" | |
| Range("E15:F23").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("E15:F23") | |
| Case "10" | |
| Range("E15:F24").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("E15:F24") | |
| Case Else | |
| tmp_msg = MsgBox("你注射的针数不在5-10之间，请联系作者！", vbOKOnly, "警告！") | |
| Exit Sub | |
| End Select | |
| ActiveChart.Location Where:=xlLocationAsObject, Name:=sht_nm | |
| ActiveChart.PlotArea.Select | |
| Selection.ClearFormats | |
| ActiveChart.Axes(xlValue).MajorGridlines.Select | |
| Selection.Delete | |
| ActiveChart.SeriesCollection(1).Select | |
| ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _ | |
| Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select | |
| ActiveChart.ChartArea.Select | |
| End If | |
| If chrt_sel = "3" Or chrt_sel = "13" Or chrt_sel = "23" Or chrt_sel = "123" Then | |
| ' | |
| ' 计算包合常数的值：选择作图方法 3； 计算图 3 的包合常数 | |
| Select Case n_inj | |
| Case "5" | |
| Range("B10").Select | |
| ActiveCell.Value = "A3" | |
| Range("C10").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[5]C[5]:R[9]C[5],R[5]C[4]:R[9]C[4])" | |
| 'Range("C10").Select | |
| 'slope_a_3 = ActiveCell.Value | |
| Range("D10").Select | |
| ActiveCell.Value = "B3" | |
| Range("E10").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[5]C[3]:R[9]C[3],R[5]C[2]:R[9]C[2])" | |
| 'Range("E10").Select | |
| 'slope_b_3 = ActiveCell.Value | |
| Range("F10").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G10").Select | |
| ActiveCell.FormulaR1C1 = "=ABS(1/RC[-4])" ' 修正第三种作图方法的包合常数值得计算方法，下同。 2007-01-06 | |
| Case "6" | |
| Range("B10").Select | |
| ActiveCell.Value = "A3" | |
| Range("C10").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[5]C[5]:R[10]C[5],R[5]C[4]:R[10]C[4])" | |
| 'Range("C10").Select | |
| 'slope_a_3 = ActiveCell.Value | |
| Range("D10").Select | |
| ActiveCell.Value = "B3" | |
| Range("E10").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[5]C[3]:R[10]C[3],R[5]C[2]:R[10]C[2])" | |
| 'Range("E10").Select | |
| 'slope_b_3 = ActiveCell.Value | |
| Range("F10").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G10").Select | |
| ActiveCell.FormulaR1C1 = "=ABS(1/RC[-4])" | |
| Case "7" | |
| Range("B10").Select | |
| ActiveCell.Value = "A3" | |
| Range("C10").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[5]C[5]:R[11]C[5],R[5]C[4]:R[11]C[4])" | |
| 'Range("C10").Select | |
| 'slope_a_3 = ActiveCell.Value | |
| Range("D10").Select | |
| ActiveCell.Value = "B3" | |
| Range("E10").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[5]C[3]:R[11]C[3],R[5]C[2]:R[11]C[2])" | |
| 'Range("E10").Select | |
| 'slope_b_3 = ActiveCell.Value | |
| Range("F10").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G10").Select | |
| ActiveCell.FormulaR1C1 = "=ABS(1/RC[-4])" | |
| Case "8" | |
| Range("B10").Select | |
| ActiveCell.Value = "A3" | |
| Range("C10").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[5]C[5]:R[12]C[5],R[5]C[4]:R[12]C[4])" | |
| 'Range("C10").Select | |
| 'slope_a_3 = ActiveCell.Value | |
| Range("D10").Select | |
| ActiveCell.Value = "B3" | |
| Range("E10").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[5]C[3]:R[12]C[3],R[5]C[2]:R[12]C[2])" | |
| 'Range("E10").Select | |
| 'slope_b_3 = ActiveCell.Value | |
| Range("F10").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G10").Select | |
| ActiveCell.FormulaR1C1 = "=ABS(1/RC[-4])" | |
| Case "9" | |
| Range("B10").Select | |
| ActiveCell.Value = "A3" | |
| Range("C10").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[5]C[5]:R[13]C[5],R[5]C[4]:R[13]C[4])" | |
| 'Range("C10").Select | |
| 'slope_a_3 = ActiveCell.Value | |
| Range("D10").Select | |
| ActiveCell.Value = "B3" | |
| Range("E10").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[5]C[3]:R[13]C[3],R[5]C[2]:R[13]C[2])" | |
| 'Range("E10").Select | |
| 'slope_b_3 = ActiveCell.Value | |
| Range("F10").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G10").Select | |
| ActiveCell.FormulaR1C1 = "=ABS(1/RC[-4])" | |
| Case "10" | |
| Range("B10").Select | |
| ActiveCell.Value = "A3" | |
| Range("C10").Select | |
| ActiveCell.FormulaR1C1 = "=SLOPE(R[5]C[5]:R[14]C[5],R[5]C[4]:R[14]C[4])" | |
| 'Range("C10").Select | |
| 'slope_a_3 = ActiveCell.Value | |
| Range("D10").Select | |
| ActiveCell.Value = "B3" | |
| Range("E10").Select | |
| ActiveCell.FormulaR1C1 = "=INTERCEPT(R[5]C[3]:R[14]C[3],R[5]C[2]:R[14]C[2])" | |
| 'Range("E10").Select | |
| 'slope_b_3 = ActiveCell.Value | |
| Range("F10").Select | |
| ActiveCell.Value = " 包合常数(1/mol)" | |
| Range("G10").Select | |
| ActiveCell.FormulaR1C1 = "=ABS(1/RC[-4])" | |
| Case Else | |
| tmp_msg = MsgBox("你注射的针数不在5-10之间，请联系作者！", vbOKOnly, "警告！") | |
| Exit Sub | |
| End Select | |
| ' | |
| ' | |
| ' 作图 3 | |
| ' 线性最差，精确度最高 | |
| ' | |
| ' 比“作图 2" 条件苛刻 | |
| ' | |
| ' x <-> y: (A - A0) <-> [CD]/(A - A0) | |
| ' | |
| Select Case n_inj | |
| Case "5" | |
| Range("G15:H19").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("G15:H19") | |
| Case "6" | |
| Range("G15:H20").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("G15:H20") | |
| Case "7" | |
| Range("G15:H21").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("G15:H21") | |
| Case "8" | |
| Range("G15:H22").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("G15:H22") | |
| Case "9" | |
| Range("G15:H23").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("G15:H23") | |
| Case "10" | |
| Range("G15:H24").Select | |
| Charts.Add | |
| ActiveChart.ChartType = xlXYScatter | |
| ActiveChart.SetSourceData Source:=Sheets(sht_nm).Range("G15:H24") | |
| Case Else | |
| tmp_msg = MsgBox("你注射的针数不在5-10之间，请联系作者！", vbOKOnly, "警告！") | |
| Exit Sub | |
| End Select | |
| ActiveChart.Location Where:=xlLocationAsObject, Name:=sht_nm | |
| ActiveChart.PlotArea.Select | |
| Selection.ClearFormats | |
| ActiveChart.Axes(xlValue).MajorGridlines.Select | |
| Selection.Delete | |
| ActiveChart.SeriesCollection(1).Select | |
| ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _ | |
| Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select | |
| ActiveChart.ChartArea.Select | |
| End If | |
| ' | |
| ' | |
| ' （不再需要一下几行，注释之） | |
| ' 假设线性回归方程的形式是：y = a*x + b | |
| ' 在输入a, b的值的时候，请务必带上"+/-"号 | |
| ' | |
| ' 生成方程a, b值的输入区域 | |
| ' | |
| 'Range("B9").Select | |
| 'ActiveCell.Value = "a" | |
| 'Range("D9").Select | |
| 'ActiveCell.Value = "b" | |
| End Sub | |
| ' | |
| ' 半自动化计算Inclusion Constant ' 不再需要这个模块，注释之 | |
| ' | |
| ' | |
| 'Sub Calc_IC() | |
| ' Dim a, b As String | |
| ' Range("C9").Select | |
| ' a = ActiveCell.Value | |
| ' Range("E9").Select | |
| ' b = ActiveCell.Value | |
| ' Range("C11").Select | |
| ' ActiveCell.Value = b / a | |
| ' | |
| 'End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment