给vba插上翅膀，---在VBA中使用RUBY

figfig

'数组合并斌且取不重复值,相当于SQL的UNION,用+则相当于sql的UNION ALL
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
Y = x.eval("[ 'a', 'b', 'c' ] | [ 'c', 'd', 'a'] ")
MsgBox Join(Y, ",")
End Sub

'字符串删除指定内容
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("'hello'.delete 'lo' ")
MsgBox y
End Sub



'字符串自动提取数字计算,需要以数字开头
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("'1.222 hello'.to_f + '2.3333lo'.to_f ")
MsgBox y
End Sub




'字符串批量替换
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("' hello'.tr('aeiou', '*')  ")
MsgBox y
End Sub








'字符串批量替换并且不重复
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("  'hello'.tr_s('l', 'r') ")
MsgBox y
End Sub







'在1--100中提取38---44范围内数
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval(" (1..100).grep 38..44   ")
MsgBox Join(y, ",")
End Sub

[ 本帖最后由 figfig 于 2010-5-11 09:20 编辑 ]

aidixiao

太精彩了，继续学习。感谢figfig大师。

蓝桥玄霜

谢谢figfig老师！
有个问题：Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("aa=[[1,'a',41],[4,'b',19],[2,'a',58],[7,'c',6],[3,'b',2],[6,'a',1]].sort{|x,y| x[2]<=>y[2]}.sort{|x,y| x[1]<=>y[1]}")
[a1].Resize(6, 3) = y
For i = 0 To 5
    a = a & y(i, 0) & ","
Next
a = Left(a, Len(a) - 1)
MsgBox a
End Sub
得到的结果是"6,1,2,4,3,7"
而根据Excel内置的排序结果是"6,1,2,3,4,7"。好像是Ruby的有点问题，请问如何改进？
请见附图：

figfig

'把第一列也排一下就可以了

'把第一列也排一下就可以了
Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("aa=[[1,'a',41],[4,'b',19],[2,'a',58],[7,'c',6],[3,'b',2],[6,'a',1]].sort{|x,y| x[2]<=>y[2]}.sort{|x,y| x[1]<=>y[1]}.sort{|x,y| x[0]<=>y[0]}")
[a1].Resize(6, 3) = y
For i = 0 To 5
    a = a & y(i, 0) & ","
Next
a = Left(a, Len(a) - 1)
MsgBox a
End Sub

蓝桥玄霜

把第一列也排一下就可以了，与答案的差距更大了。
请见附图。

figfig

原帖由 蓝桥玄霜 于 2010-5-11 11:17 发表
把第一列也排一下就可以了，与答案的差距更大了。
请见附图。

Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("aa=[[1,'a',41],[4,'b',19],[2,'a',58],[7,'c',6],[3,'b',2],[6,'a',1]].sort_by{|x| [x[1],x[2]]}")
[a1].Resize(6, 3) = y
For i = 0 To 5
    a = a & y(i, 0) & ","
Next
a = Left(a, Len(a) - 1)
MsgBox a
End Sub

figfig

'用后引号执行系统命令

Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("`date`")
MsgBox y
End Sub

'当前目录
Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("`dir` ")
MsgBox y
End Sub





Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("`tree c:\windows` ")
MsgBox y
End Sub






Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("`notepad` ")
'MsgBox y
End Sub



'B=1 A=B+1 可以简写为   A=(B=1)+1



Sub ruby2()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("aa=(bb=1)+2;%x{echo #{aa}}  ")
MsgBox y
End Sub

[ 本帖最后由 figfig 于 2010-5-11 14:59 编辑 ]

3dcapture

请教大师，下面这段代码用ruby和perl怎样写：
Private Sub Command1_Click()
   ' 创建变量。
   Dim sc, m
   Set sc = CreateObject("ScriptControl")
   sc.Language = "VBScript"
   ' 添加模块。
   Set m = sc.Modules.Add("Module1")
   ' 向模块添加代码。
   m.AddCode "Sub Main : MsgBox ""嗨，你好！"" : End Sub"
   ' 运行 Script。
   m.Run "Main"
End Sub

3dcapture

感谢大师分享。

3dcapture

大师能不能简单讲讲eval、addcode等方法的意思及具体应用场合。谢谢了。

[ 本帖最后由 3dcapture 于 2010-5-11 21:57 编辑 ]