excel vba 自定义函数更改单元格值

自定义函数更改单元格值

Public myc, v_b, p_i, p_y '定义公共变量及传递的参数
Function test_f(test_v)
    Dim v_a As String
    v_a = test_v
    p_i = test_v.Row
    p_y = test_v.Column
    If (InStr(v_a, " ") = 0) Then
        test_f = v_a '没有找到
    Else
        '分割文本
        v_b = Split(v_a, " ")
        Set myc = New 类1
        Set myc.sht = ActiveSheet
    End If
End Function
Public WithEvents sht As Worksheet '事件类
Private Sub sht_Change(ByVal Target As Range) '声明事件
  On Error GoTo ren '定义错误跳转
    Set myc.sht = Nothing '置空
    Set myc = Nothing '置空
    Set s = CreateObject("MSScriptControl.ScriptControl") '创建ScriptControl对象
    s.Language = "VBScript" '定义语言
    s.AddObject "ActiveWorkbook", ActiveWorkbook '增加对象
    s.AddObject "Application", Application '增加对象
    s.AddObject "Activesheet", ActiveSheet '增加对象
    s.AddObject "sheets", Sheets '增加对象
    s.AddObject "cells", Cells '增加对象

    '自定义代码区
        Dim i As Long
        For i = 0 To UBound(v_b)
            Debug.Print v_b(i)
            Cells(p_i, p_y + i + 1) = v_b(i)
        Next i
    '结束代码
    s.Reset '重置
ren: '错误跳转
End Sub

excel vba 自定义函数更改单元格值


本站如无特别说明即为原创,转而告知:(http://iwonmo.com/archives/1038.html)

标签: excel, vba, 自定义函数, 单元格值

添加新评论