LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@Q11Q

EXCEL 64bit版でJscriptの配列ソートは利用できない

Posted at

ScriptcontrolはVBAでいつ使うか

Excel VBAでWebサービス - ExcelでJScriptの機能を利用しよう
ではJscript(ScriptControl)を使うのは

  • URLのエンコード、デコード
  • 配列のソート

に限られており、この２つが代替できれば良いことになります。
このうち、URLのエンコード、デコードはvbscriptに値を受け渡せるのでそこでJscriptを使ってやれば可能です。エンコードは前回の記事の補足にコメントとして載せていますのでご覧ください。
　最もURLエンコードについてはexcel2013からワークシート関数が実装されたため、わざわざJscriptをエンコードのために呼び出す必要はなくなりました。
　ただしデコードはまだ必要で、旧バージョンとの互換性を考えるとVBSで組む方法は有効だと思います。

次に配列ですが、
VBScript の整数の入った配列をJavaScriptを使ってソートする
VBAからJScriptのArrayオブジェクトのsort()を利用する。
という方法があります。ところが前回とおなじ理由で64bitではこのJscriptの関数はエラーになってしまいます。VBSCRIPTの場合下のようにwsfファイルを作って32bitで動かせばいいのかもしれませんが、これはVBAからは配列が受け渡せそうにないので困難です。

バブルソート、クィックソート

すると考え方としてはバブルソート、クィックソート、ワークシートの利用が考えられます。
これはVBA、VBSとも大体記述も同じで、この方法がバージョンを考えなくてよいので、Jscriptで配列をソートしている場合、一番大体しやすいと思います。

[1次元配列の並べ替え（バブルソート,クイックソート）](http://excel-ubara.com/excelvba5/EXCELVBA228.html
VBAで配列をソートするためのユーザー定義関数
配列をソートする
配列のバブルソートを理解するためのExcelマクロ
これでもいいのですが

そして配列のソートに関しては、EXCELの場合、シートに転記して、そこからソートして再び配列にする方法も考えられます。

ワークシートのSortメソッドを使わずに並べ替えを行なう。

この方式ではクィックソートが一番早いようです。
またEXCEL　2007以降で数値の場合、
配列のデータを並べ替える

もし、配列内のデータが数値だった場合、さらに簡単な方法があります。
ワークシート関数の、SMALL関数やLARGE関数を使う方法です。
次のマクロは、配列orgArrayの数値データを昇順に並べ替えて、配列srtArrayに格納します。

この方法はよさそうです。しかし、配列の並べ替えはエクセルで使用するとは限りません。
なのでもうすこし一般的な方法を探してみます。

System.Collection.Arraylistを使う

VBS
VBSで動的配列（ArrayList）を使う
How do I sort arrays using vbscript?
VBA
.NET Frameworkを使う

■VBAから.NET Framework

で、静的に参照したいなと思って少し調べてみたら、VBAの参照設定からCommon Language Runtime Libraryを参照するようにすると、mscorlibライブラリの下にArrayListとかHashtableとかがいらっしゃるので、VBAからnewしたりできるようになる。

デフォルトでは正の整数しかソートできない

OfficeTanaka.VB
Sub Sample1()
    Dim DataList, myData, i As Long, buf As String
    Set DataList = CreateObject("System.Collections.ArrayList")     ''.NET Frameworkへの参照
    For i = 1 To 5
        DataList.Add Int(Rnd() * 10000)     ''5個の乱数を配列にセットする
    Next i
    DataList.Sort                   ''配列をソートする
    Set myData = DataList.Clone     ''配列の複製を作る
    For i = 0 To myData.Count - 1
        buf = buf & myData(i) & vbCrLf
    Next i
    buf = buf & "------" & vbCrLf
    DataList.Reverse                ''配列を逆順にする
    For i = 0 To DataList.Count - 1
        buf = buf & DataList(i) & vbCrLf
    Next i
    MsgBox buf
    Set myData = Nothing
    Set DataList = Nothing
End Sub

これを実験的に改造します
Dim aAr: aAr = Split("n,あ,3,雲,東,Ａ,ｚ,１,Yoko,洋子", ",")
Dim aBr: aBr = Split("105,302,3,42,0,-5,4.3,-4.1,-5.5,1000", ",")
Dim aCr: aCr = Split("105,302,3,42,0,-5,4,-4,-5,1000", ",")
Dim aDr: aDr = Split("105,302,3,42,0,5,4,4,5,1000", ",")
という配列を作り、このなかからインデックスを選んで配列に入れ込んでソートするのです。なおaArのＡ,ａ,ｚ,１は全角の１です。
この中でまともにソートできるのはどれでしょうか。答えはaDrだけです。しかも当方では例によって原因不明のエラーが出るため、ABSとINTが欠かせません。

OfficeTanaka2.VB
Sub Sample11()
    Dim DataList As New mscorlib.ArrayList, myData, i As Long, buf As String
    Dim msCore As mscorlib.CompareOptions
    Set DataList = CreateObject("System.Collections.ArrayList")     ''.NET Frameworkへの参照
    Dim aAr: aAr = Split("n,あ,3,雲,東,Ａ,ｚ,１,Yoko,洋子", ",")
    Dim aBr: aBr = Split("105,302,3,42,0,-5,4.3,-4.1,-5.5,1000", ",")
    Dim aCr: aCr = Split("105,302,3,42,0,-5,4,-4,-5,1000", ",")
    Dim aDr: aDr = Split("105,302,3,42,0,5,4,4,5,1000", ",")
    Dim i1 As Integer
 
    For i = 1 To 10
    i1 = Int(Rnd() * 10)
        DataList.Add Int(Abs(CLng(aDr(i1))))     ''10個の乱数を配列にセットする
      Next i
    DataList.Sort                   ''配列をソートする
    Set myData = DataList.Clone     ''配列の複製を作る
    For i = 0 To myData.Count - 1
        buf = buf & myData(i) & vbCrLf
    Next i
    buf = buf & "------" & vbCrLf
    DataList.Reverse                ''配列を逆順にする
    For i = 0 To DataList.Count - 1
        buf = buf & DataList(i) & vbCrLf
    Next i
    MsgBox buf
    Set myData = Nothing
    Set DataList = Nothing
End Sub

正負、小数点がある場合

正の整数と小数点が混在するaBr,aCrの場合、オプションを設定してCdblで型変換するとよいようです
もう一つ変数を足してソートオプションを指定します。

OfficeTanaka.vb
Sub Sample12()
    Dim DataList As New mscorlib.ArrayList, myData, i As Long, buf As String
    Dim msCore As mscorlib.CompareOptions
    Set DataList = CreateObject("System.Collections.ArrayList")     ''.NET Frameworkへの参照
    Dim aAr: aAr = Split("n,あ,3,雲,ｨ,Ａ,ｚ,１,Yoko,洋子", ",")
    Dim aBr: aBr = Split("105,302,3,42,0,-5,4.3,-4.1,-5.5,1000", ",")
    Dim aCr: aCr = Split("105,302,3,42,0,-5,4,-4,-5,1000", ",")
    Dim aDr: aDr = Split("105,302,3,42,0,5,4,4,5,1000", ",")
    Dim i1 As Integer
    msCore = CompareOptions_StringSort
    For i = 1 To 20
    i1 = Int(Rnd() * 10)
   '20個の乱数を配列にセットする
        DataList.Add CDbl(aCr(i1))
    Next i
    
    DataList.Sort                   ''配列をソートする
    Set myData = DataList.Clone     ''配列の複製を作る
    For i = 0 To myData.Count - 1
        buf = buf & myData(i) & vbCrLf
    Next i
    buf = buf & "------" & vbCrLf
    DataList.Reverse                ''配列を逆順にする
    For i = 0 To DataList.Count - 1
        buf = buf & DataList(i) & vbCrLf
    Next i
    MsgBox buf
    Set myData = Nothing
    Set DataList = Nothing
End Sub

文字列のソート

では文字列で比較できないか。文字列だとすこし変わったソートになるようです。
Cstrで文字列として配列を形成するのです。
ただしこの方法は全角の英数字ひらがなカタカナと半角の英数字ひらがなカタカナは等価なので１の次が半角の3になります。

Officetanaka3.vb
    Dim DataList As New mscorlib.ArrayList, myData, i As Long, buf As String
       Dim msCore As mscorlib.CompareOptions
    Set DataList = CreateObject("System.Collections.ArrayList")     ''.NET Frameworkへの参照
    Dim aAr: aAr = Split("n,あ,3,雲,東,Ａ,ｚ,１,Yoko,洋子", ",")
    Dim aBr: aBr = Split("105,302,3,42,0,-5,4.3,-4.1,-5.5,1000", ",")
    Dim aCr: aCr = Split("105,302,3,42,0,-5,4,-4,-5,1000", ",")
    Dim aDr: aDr = Split("105,302,3,42,0,5,4,4,5,1000", ",")
    Dim i1 As Integer
    'msCore = CompareOptions_StringSort
    For i = 1 To 10
    i1 = Int(Rnd() * 10)
       '5個の乱数を配列にセットする
        DataList.Add CStr(aAr(i1))
    Next i
    DataList.Sort                   ''配列をソートする
    Set myData = DataList.Clone     ''配列の複製を作る
    For i = 0 To myData.Count - 1
        buf = buf & myData(i) & vbCrLf
    Next i
    buf = buf & "------" & vbCrLf
    DataList.Reverse                ''配列を逆順にする
    For i = 0 To DataList.Count - 1
        buf = buf & DataList(i) & vbCrLf
    Next i
    MsgBox buf
    Set myData = Nothing
    Set DataList = Nothing
End Sub

まとめ

  • マイナビによるおt、Jscript すなわち　ScriptControl はVBAで２つの使い方がある。
  • いずれも64bitでは使えない。
  • このうちURLのデコードはVBSに関数を設定すると使える。エンコードはexcel 2013以降ならURLエンコード関数があるので利用する。
  • 配列のソートは今のところ難しい。VBAからVBSに配列を渡して、配列を返す方法がみつからない。これは書き換えが必要。
  • EXCELの場合ワークシートを活用したクィックソートが速い。とくにEXCELはワークシートを活用する。
  • このほか、正の整数ならmscolibを使える。マイナスや小数点がある場合はオプションを設定する。これはVBSでも使える。
  • 文字列ならmscorlibにオプションを設定する。ただし全角数字と半角数字が同じ数字扱いになるなど、全半角を問わないソートで、通常の文字列ソートと異なるため、使わない方がよい。
1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?