[vba] 写入公式, xlookup, 及evaluate直接获取公式结果的探索

结论:

1. 通过range.formula = "= 公式" 的方式来写入公式, 其中需要注意公式中的部分因为处于双引号中, 原本的双引号需要加倍;

2. Evaluate可以很方便的获取公式的结果, 使用时 Evaluate(公式), 其中公式中不需要等号, 公式整体需要双引号括起来, 因此双引号也要加倍. 公式中的参数可以使用单元格地址, 也可以使用字符串.Evaluate可以简写用方括号代替, 方括号中不需要加双引号.

3. Evaluate不可以引用关闭的工作簿, 因此想要不打开工作簿而引用数据, 最好还是通过vba书写公式;

4. WorksheetFunction.Xlookup(字符串/?, 字符串/单元格/数组, 字符串/单元格/数组, "Not Found")

Sub test_xlookup()

'arr1 = Array("a", "b", "c", "d")
'arr2 = Array("1", "2", "3", "4")
'rst = WorksheetFunction.XLookup("b", arr1, arr2, "X") 'rst = "2"

'arr1 = Range("A1:A10")
'arr2 = Range("B1:B10")
'rst = WorksheetFunction.XLookup("a", arr1, arr2, "X") ' rst : "$B$3"

'Set arr1 = Range("A1:A10")
'Set arr2 = Range("B1:B10")
'rst = WorksheetFunction.XLookup("a", arr1, arr2, "X") ' rst : "$B$3"
Set Rng = Application.InputBox("ok", Type:=8)
adr = Rng.Address(False, False, xlA1)
Selection.Formula = "=XLOOKUP(" & adr & _
",'C:\Users\roy.luo\Desktop\[SNN Tracker.xlsx]Sheet1'!$A:$A,'C:\Users\roy.luo\Desktop\[SNN Tracker.xlsx]Sheet1'!$B:$B,""X"")"

End Sub

Sub test_evaluate()


'rst = Evaluate("VLOOKUP(""a"",B1:C10,2,false)")'works
'rst = Evaluate("VLOOKUP(A3,B1:C10,2,false)") 'works
'rst = [VLOOKUP(A3,B1:C10,2,false)] 'works
'rst = [VLOOKUP("c",B1:C10,2,false)] 'works
'rst = [xlookup(A8,B1:B10,C1:C10,"X")]'works

'rst = ['C:\Users\roy.luo\Desktop\[SNN Tracker.xlsx]Sheet1!A1] 'not work, evaluate may not use on closed workbook
'rst = [XLOOKUP(B13,'C:\Users\roy.luo\Desktop\[SNN Tracker.xlsx]Sheet1'!$A:$A,'C:\Users\roy.luo\Desktop\[SNN Tracker.xlsx]Sheet1'!$B:$B,"X")]

fd = "C:\Users\roy.luo\Desktop\"
bn = "SNN Tracker.xlsx"
sn = "Sheet1"
ad = "'" & fd & "[" & bn & "]" & sn & "'!"
rg = Application.InputBox("org", Type:=8).Address(False, False, xlA1)
fm = "=xlookup(" & rg & "," & ad & "A:A," & ad & "B:B,""X"")"

Selection.Formula = fm 'works
'rst = Evaluate(fm) 'not use on closed workbooks

'MsgBox rst
End Sub