今天的技巧是群里2位高手帮忙解决一个实际需求。
网友”活塞“ 需求：
要求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 Object
Public 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 With
End 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 = True
End Sub
作者2：Excel小子-Office中国
函数实现的方法
