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

figfig

Sub ruby2()

'正则patter可以用变量替换
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
Y = x.eval("regEx = '\d+' ;str='ab123c'.scan(/#{regEx}/)")

MsgBox Y(0)

End Sub

lb_bn

figfig老师太强大了，什么语言都会。

蓝桥玄霜

原帖由 figfig 于 2010-5-10 13:04 发表
Sub ruby2()

'根据长度排序
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("['aa','nnnnn','jk'].sort_by {|x| x.length}")
MsgBox Join(y, ",")
End Sub

请教：降序的代码怎么写？

figfig

原帖由 蓝桥玄霜 于 2010-5-10 14:38 发表

请教：降序的代码怎么写？

Sub ruby2()

'根据长度排序
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("['aa','nnnnn','jk'].sort_by {|x| x.length}.reverse")
MsgBox Join(y, ",")
End Sub

figfig

Sub ruby2()

'根据长度排序
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("['aa','nnnnn','jk'].sort_by {|x| -x.length}")
MsgBox Join(y, ",")
End Sub

figfig

Sub ruby2()

'数组取最大最小值,也可以指定条件范围取最大最小值
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("ary = [32 , 89 , 3 , 45 , 104].min")
MsgBox y

y = x.eval("ary = [32 , 89 , 3 , 45 , 104].minmax")
MsgBox Join(y, ",")


'根据余数取最大最小值
y = x.eval("ary = [32 , 89 , 3 , 45 , 104].minmax_by {|v| v % 10}")
MsgBox Join(y, ",")


End Sub

figfig

Sub ruby2()

'把不符合的拒之门外
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("ary = [32 , 89 , 3 , 45 , 104].reject {|x| x>80} ")
MsgBox Join(y, ",")



End Sub

蓝桥玄霜

谢谢figfig老师。还有个问题：
如果有个二维数组：
arr(1,1)=1        arr(1,2)="a"
arr(2,1)=4        arr(2,2)="b"
arr(3,1)=2        arr(3,2)="a"
arr(4,1)=7        arr(4,2)="c"
arr(5,1)=3        arr(5,2)="b"
arr(6,1)=6        arr(6,2)="a"
要求按第2维升序排序以后得到第一维的排列次序：（1，2，6，4，3，7）
用ruby怎么写？

figfig

原帖由 蓝桥玄霜 于 2010-5-10 17:06 发表
谢谢figfig老师。还有个问题：
如果有个二维数组：
arr(1,1)=1        arr(1,2)="a"
arr(2,1)=4        arr(2,2)="b"
arr(3,1)=2        arr(3,2)="a"
arr(4,1)=7        arr(4,2)="c"
arr(5,1)=3        arr(5,2)="b"
arr(6,1)=6        arr(6,2)="a"
...

Sub ruby()

'须安装ACTIVERUBY.才能运行
'下载地址  http://www.artonx.org/data/asr/ActiveRuby.msi  

Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("aa=[[1,'a'],[4,'b'],[2,'a'],[7,'c'],[3,'b'],[6,'a']].sort_by{|x| x[1]}")



Stop

End Sub

figfig

Sub ruby()

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]}")



Stop

End Sub



也可以根据条件排序,入根据逗号里面数字排序
Sub ruby()

Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("a = ['Hitoshi,045', 'Sizuo,046', 'Yoshi,0138'].sort{|a, b| a.split(',')[1] <=> b.split(',')[1]} ")



Stop

End Sub

'字符串象数组一样处理
Sub ruby()

Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("a = 'Hitoshi,045'[2,3] ")



MsgBox y

End Sub


'数组重新整理入把里面数字相乘2x500构成新数组
Sub ruby()

Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("a = [['vine', 2, 500], ['orange', 3, 100], ['apple', 10, 50]] .map {|x| [x[0], x[1]*x[2]]} ")


Stop

End Sub
'根据重复次数排序
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("a=[1,1,2,2,2,3].sort_by{|v| a.count(v)}.uniq")
MsgBox Join(y, ",")
End Sub

'判断范围的写法相当于 IF A>1 AND A<20
Sub ruby()
Set x = CreateObject("scriptcontrol")
x.Language = "rubyscript"
y = x.eval("(1..20) === 15 ")
MsgBox y
End Sub

[ 本帖最后由 figfig 于 2010-5-10 22:25 编辑 ]