[Chia sẻ] Tự động cập nhật bảng dữ liệu từ hàm cho sẵn func(x,y)

[ego.bizzin]

' TAGS : 2014-04-27, EB.GPE, robot, func(x,y), update, auto, predefined
' Các kỹ thuật sử dụng :
' # Worksheet_Change
' # Evaluate
' Cảm ơn anh vu_tuan_manh_linh về Evaluate

[GPECODE=vb]Sub Worksheet_Change(ByVal Target As Range)
'
Dim rngRobot, rngFormula, cell As Range
Set rngRobot = Sheet1.Range("_robot")
Set rngFormula = Sheet1.Range("_text_formula")
'
If Target.Address <> rngFormula.Address Then Exit Sub
'
For Each cell In rngRobot
cell.Value = robotFunc(rngFormula.Value, cell)
Next cell
End Sub
[/GPECODE]
[GPECODE=vb]Function robotFunc(formula_text As String, rng As Range)
Dim varX, varY As String
varX = "x"
varY = "y"
'
Dim headerX_row, headerY_col As String
headerX_row = "1"
headerY_col = "A"
'
Dim tmp As String
tmp = Replace(formula_text, varX, Split(rng.Address, "$")(1) & headerX_row)
tmp = Replace(tmp, varY, headerY_col & Split(rng.Address, "$")(2))
robotFunc = Evaluate(tmp)
End Function
[/GPECODE]