在VBA中使用JAVASCRIPT和VBSCRIPT（1）
javascript有许多函数和功能可以弥补VBA不足，如正则，数组，类，等等
1)以数组为例，用JAVASCRIPT排序
Sub fig8()
Set x = CreateObject("msscriptcontrol.scriptcontrol")
x.Language = "javascript"
arr = Array("aa", "cc", "bb", "1a")
kk = Join(arr, ",")
x.addcode "function aa(bb){x=bb.split(',');x.sort();return x;}"
cc = x.eval("aa('" & kk & "')")
MsgBox cc
End Sub
2)1)以数组为例，用JAVASCRIPT倒序
Sub fig8()
Set x = CreateObject("msscriptcontrol.scriptcontrol")
x.Language = "javascript"
arr = Array("aa", "cc", "bb", "1a")
kk = Join(arr, ",")
x.addcode "function aa(bb){x=bb.split(',');x.reverse();return x;}"
cc = x.eval("aa('" & kk & "')")
MsgBox cc
End Sub
用VBSCRIPT的简单例子
Sub fig8()
Set x = CreateObject("msscriptcontrol.scriptcontrol")
x.Language = "vbscript"
x.addcode "sub aa(): msgbox ""hello.."":end sub "
x.Run "aa"
End Sub
以前需要分开好几个模块，函数，现在可以统统放在一起了。。。。。
Sub fig8()
Set x = CreateObject("msscriptcontrol.scriptcontrol")
x.Language = "vbscript"
x.addcode "sub aa(): msgbox ""hello.."":end sub : sub bb:msgbox 3:end sub :sub cc: msgbox ""cc"":end sub"
x.Run "aa"
x.Run "bb"
x.Run "cc"
End Sub
自定义函数的用法
Sub fig8()
Set x = CreateObject("msscriptcontrol.scriptcontrol")
x.Language = "vbscript"
x.addcode "function sum(x,y):sum=x+y:end function "
bb = x.Run("sum", 2, 3)
MsgBox bb
End Sub
动态改变窗口，文本框，单元格，range属性，
'本例改[A1:z888]单元格为红
Sub fig88()
javascript全局数组Set X = CreateObject("msscriptcontrol.scriptcontrol")
X.Language = "vbscript"
X.addcode "SUB AA:XX.INTERIOR.COLORINDEX=3:END SUB "
X.ADDOBJECT "XX", [A1:z888]
X.Run "AA"
End Sub
设置和调用全局变量
Sub figvb()
Set x = CreateObject("msscriptcontrol.scriptcontrol")
x.Language = "vbscript"
x.addcode "public x: sub aa(bb):x=bb*100:end sub"
x.Run "aa", 3
b = x.codeobject.x
MsgBox b
End Sub
代码放在单元格里不再是笑话：）
Sub figvbs()
    Set x = CreateObject("msscriptcontrol.scriptcontrol")
  x.Language = "vbscript"
  [a1] = "a1=3"
  [a2] = "b1=4"
  [a3] = "msgbox a1+b1"
    For i = 1 To 3
  x.executestatement Cells(i, 1)
    Next
  End Sub
新建类可以不再需要类模块
Sub figvbs()
    Set X = CreateObject("msscriptcontrol.scriptcontrol")
  X.Language = "vbscript"
  X.ADDCODE "CLASS AA:PUBLIC SUB TEST():MSGBOX ""类模块"":END SUB:END CLASS"
  X.ADDCODE "SET YY=NEW AA"
  Set RR = X.EVAL("YY")
  RR.TEST
End Sub
表达式可以直接拿来运算
Sub aa()
Set X = CreateObject("msscriptcontrol.scriptcontrol")
X.Language = "vbscript"
Dim ARR(2)
ARR(0) = "3"
ARR(1) = "4*6"
ARR(2) = "SIN(5)"
KK = Join(ARR, "+")
BB = X.EXECUTESTATEMENT("MSGBOX " & KK)
KK = Join(ARR, "*")
BB = X.EXECUTESTATEMENT("MSGBOX " & KK)
End Sub
msgbox ,inputbox 也可以作为变量
Sub figtest1()
Set x = CreateObject("msscriptcontrol.scriptcontrol")
x.Language = "vbscript"
aa = "msgbox "
bb = "cc=inputbox"
For i = 1 To 4
If i Mod 2 = 0 Then
kk = aa & "  " & i
Else
kk = bb & "(" & i & ")"
End If
x.executestatement (kk)
Next
End Sub
字符串加密(md5)
Sub figtest1()
Set X = CreateObject("msscriptcontrol.scriptcontrol")
X.Language = "vbscript"
X.ADDCODE "Function x(s):Set y= CreateObject(""CAPICOM.HashedData""):y.Algorithm =3:" & _
        "y.Hash s:z = y.Value:x = z:End Function"
BB = X.Run("x", "FIGFIG")
MsgBox "字符 FIGFIG 加密后是: " & BB
End Sub
数组也可以随意切割了
Sub JSArraySample()
    Set objJS = CreateObject("ScriptControl")
 
    With objJS
        .Language = "JScript"
        .AddCode "function JSSplit(s,d){return s.split(d);}"
     
    End With
 
    文字列 = "a,b,c,d,e"
 
    Set b = objJS.CodeObject.JSSplit(文字列, ",")
' '数组也可以随意切割了
  MsgBox b.slice(0, 1)
  MsgBox b.slice(1, 2)
  MsgBox b.slice(2, 5)
End Sub
功能更加强大的正则表达式
Sub figexp()
Set js = CreateObject("ScriptControl")
  js.Language = "JScript"
  script = "'abcdefg'.match(/a/)"
  result = js.eval(script)
  MsgBox result
End Sub
jscript返回的对象应该是一个数组，可以在VB直接调用相关函数，但又可以直接显示所有元素

Sub Mytest()

    Set sp1 = CreateObject("ScriptControl")
        sp1.Language = "JScript"
    s = "function sortarr(arr){Array();}"    '顺序
sp1.AddCode s
    aa = Array("张", "王", "李", "赵", "钱", "孙", "周", "吴", "郑", "王")
Set bb = deobject.sortarr(aa)
MsgBox bb
MsgBox bb.slice(1, 4)
at("888").concat("777")
bb.push ("999")
MsgBox bb


End Sub
数组非交集
Sub figjs()
arr1 = [a2:a11]
arr2 = [b2:b6]
Set x = CreateObject("scriptcontrol")
x.Language = "jscript"
x.eval ("function aa(aa) {Array();}")

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。