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

figfig · 发表于 2010-5-10 14:16

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 · 发表于 2010-5-10 14:18

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

蓝桥玄霜 · 发表于 2010-5-10 14:38

原帖由 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:45

原帖由 蓝桥玄霜 于 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 · 发表于 2010-5-10 14:48

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 15:04

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 · 发表于 2010-5-10 15:10

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

蓝桥玄霜 · 发表于 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"
要求按第2维升序排序以后得到第一维的排列次序：（1，2，6，4，3，7）
用ruby怎么写？

figfig · 发表于 2010-5-10 17:33

原帖由 蓝桥玄霜 于 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 · 发表于 2010-5-10 21:00

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 编辑 ]

		自动登录	找回密码
密码			免费注册

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

也可以这样,简单点

多条件排序,先排第3列,再排第2列,可一直排下去,