Excel单位转换解答实例

今天的技巧是群里2位高手帮忙解决一个实际需求。网友”活塞“ 需求：

1480687617808050.png

要求VBA实现

说明:
1,A2为需要初步输入值,当不输入时,为空,
2,B2为下拉菜单选择相关单位,当改变单位时,A2自动乘以单位转换系数,详见E2:H5
举例1	A2输入100,B2单位为mpa,当B2改为bar时,A2=1000(即100*10)
举例2	A2输入5,B2初始单位为bar,当B2改为psi时,A2=72.5(即5*14.5)

作者1：开心-Office中国解决办法：
Public FactorDic As ObjectPublic oldValue As String
'初始化系数字典Sub InitFactor()  Set FactorDic = CreateObject("Scripting.Dictionary")   With FactorDic    .Add "mpa", 1    .Add "bar", 10    .Add "psi", 145    .Add "kpa", 1000 End WithEnd Sub
Private Sub Workbook_Open()  Globals.oldValue = Range("B2")End Sub
Private Sub Worksheet_Change(ByVal Target As Range)  If Target.Address  "$B$2" Then Exit Sub   Dim val As Double  val = Range("A2")   '如果不是数字退出  If Not VBA.IsNumeric(val) Then Exit Sub   '如果系数字典为空就初始化系数  If Globals.FactorDic Is Nothing Then Globals.InitFactor   '如果旧值等于新值说明没有变更，不进行运算  If Globals.oldValue = Target.Value Then Exit Sub    Dim oldFactor, newFactor   '获取新旧值的系数值  oldFactor = Globals.FactorDic(Globals.oldValue)  newFactor = Globals.FactorDic(Target.Value)   Dim resultValue  resultValue = val / (VBA.CDbl(oldFactor) / VBA.CDbl(newFactor))    '关闭事件触发  Application.EnableEvents = False   Range("A2") = resultValue  Globals.oldValue = Target.Value   Application.EnableEvents = TrueEnd Sub
作者2：Excel小子-Office中国函数实现的方法

1480687924885766.gif