Excel VBA Archive

VBは行番号に沿って１行ずつコンパイルして実行処理されるプログラムです。Gotoステートメントで行を指定すれば行を飛ばしたり、戻ったり、ループさせることも出来ます。

ＶＢに行番号をつけて簡単なコードを書いてみました。

1 times a=1 b=0 c=0
2 times a=3 b=2 c=0
3 times a=3 b=6 c=9

下のコードを実行するとイミディエイトウィンドウに上のように書き込まれます。最初に、a=0, b=0, c=0, i=1 と変数に数値を格納しておきます。インタプリタですので、１行ずつコードをコンパイルしながら処理されます。

1:a=0 の場合、行番号3に移動します
3:a=b+1 ---> a=0+1 ですので、a=1 となります
4:ここで、1 times a=1 b=0 c=0 と書き込まれます
5:a=1 の場合、行番号2 に移動します
2:b=2 となります
3:a=b+1 ---> a=2+1 ですので、a=3 となります
4:ここで、2 times a=3 b=2 c=0 と書き込まれます
5:a=3 となりましたので、行番号2には移動しません
6:b = a * 2 ですので、b=6 となります
7:c = a + b ですので、c=9 となります
8:ここで、3 times a=3 b=6 c=9 と書き込まれて終了です

Sub GoTo_Test() Dim a%, b%, c%, i% a = 0: b = 0: c = 0: i = 1 1: If a = 0 Then GoTo 3 'a=0 b=0 c=0 2: b = 2 'a=1 b=2 c=0 3: a = b + 1 'a=1 b=0 c=0 --> a=3 b=2 c=0 4: Debug.Print i & " times " & "a=" & a & " b=" & b & " c=" & c: i = i + 1 5: If a = 1 Then GoTo 2 'a=1 b=0 c=0 --> a=3 b=2 c=0 6: b = a * 2 'a=3 b=6 c=0 7: c = a + b 'a=3 b=6 c=9 8: Debug.Print i & " times " & "a=" & a & " b=" & b & " c=" & c End Sub

型宣言文字 型宣言文字はあまり推奨されないかもしれませんが、ここで紹介しているコードによく使ってますので紹介します。

文字列関数のString型変換

Mid(Expression,Start,Length)-->Mid$(Expression as String,Start,Length)
Replace(Expression,Find, Replace,,)-->Replace$(Expression as String,Find, Replace,,)

上のテーブルのようにファンクションの"Mid"などで文字列を使うと決まっている場合は、Mid() -- > Mid$()と書くことでバリアント型からストリング型にバイト数を小さくした「型」を使うことで処理が速く⁽¹⁾なります。Excel VBAでValueのついたデータ型(例：Textbox1.Value, Range("A1").Value など）はバリアント型です。VBAのコードを書く場合は、これらの計算をするときに必ず数値に変換させないとエラーの原因となります(Example:CSng(Textbox1.Value))。これは当然のことで、Ｖａｌｕｅの値に数値が入ったとしていてもバリアント型としてコンパイルされますので注意が必要です。これは数値でも他のデータ型に変換される可能性があるからです。

Spinbutton.Value ---> Long型 などの例外もあります。
⁽¹⁾実行速度が速くなる理由はバイナリーデータ（機械語）にコンパイルした時の実行処理するときのサイズが小さくなるためです。Double型やLong型の計算よりも、Integer型の計算の方が速いのも同じ理由です。
コンピュータが認識できるのは2進数（0と1）のバイナリーデータです。電圧の高い=1, 低い=0のデータの組み合わせを認識して処理します。（ハードディスクドライブなどの記憶装置は、（普通に考えても）地球上にはS極とN極の磁気しかないのでそのバイナリーデータの組み合わせです。）

2分検索ファンクションのVBAソースコードを紹介します。配列の最初から順番に目的の要素を探す場合は、最大でN回の比較検索が必要です。しかしこの２分検索アルゴリズムは最大でlog*N回の比較検索で探します。何度も言いますがソートなどの並び替えと同じく、アルゴリズムを構築する場合はとても長い配列を想定しなければいけません。

アルゴリズムとシステムについて：少し内容が逸れます。 アルゴリズムは「システム」とは違い失敗や漏れがあってはアルゴリズムとして成り立ちません。そういった理に適っている洗練されたアルゴリズムは人類のとても貴重な財産です。宝石やお金などの光り物は常に価値が変動したり時に失ったりすることがありますが、理となって顕現しているものは決して動いたり失うことがありません。不動の理（支点）がないと何も動かすことが出来ません。一方、ビジネスのツールや社会のルールとなるシステムは時間の経過と共にに想定外の事態が起こった場合、切捨てなければならない部分が生じます。株価の例を挙げると、上げ下げの材料は時間にほぼ比例するように増えていきます。

もしすべての条件を最初から想定できる社会システムを創ることが出来れば、公共の福祉（弱者救済)のための司法機関は必要なくなるでしょう。残念ながらシステムである以上それはありえません。通常、システムは常に多数を選択します。それでコツコツと（多数と少数の）鞘の部分に貯まりだす利益を生み出します。

話が逸れましたが、アルゴリズムはとても価値あるもので人生というジグソーパズルの中の定まった貴重なパーツにもなりうるということを理解してください。

The VBA source code of "Binary search" function is shown below.To find for the target element in an order from the beginning of array, N comparison search is required at the maximum. However, this "Binary search" algorithm will find target element at log*N time at the maximum. Although repeatedly said, when building an algorithm, you have to assume very long array.

About an algorithm and a system: The contents swerve for a while. Algorithm is different from "System", and bugs never occur in the algorithm. Such refined rational algorithms are human beings' very precious property. Although value jewelry or money may always fluctuate,and also may sometimes be lost, algorithm is truth and it will never change, or is not lost. Anything can be moved if there is no immovable truth (fulcrum). On the other hand, when the situation besides assumption happens to progress of time, the portion which must be cut off when produces the system used as the tool of business, or a social rule. If the example of a stock price is given, the material of taking up and down will increase mostly proportional to time.

The machinery of law for public welfare will become unnecessary if the social system which can assume all the conditions from the beginning can be predicted. Though regrettable, a system cannot be created perfectly like algorithm. Usually, a system always chooses a large number. After then, the profits (a large number and small number) which begin to accumulate in the portion of a difference are produced.

What I meant was, I want you to understand that an algorithm is very valuable and it can also become the fixed precious part of a jigsaw puzzle in your life.

ソースコードを紹介します。ヒープソートなどと比べるととても簡単なものです。

Continue reading

訂正：２０％効率化されたヒープソート・アルゴリズムと書いていましたが、どうにも腑に落ちなかったので、C言語で書かれた専門書を買って調べたところ、ここに書いているコード（効率化されたヒープソートと書いたコードとほぼ同じもの）が本に書かれていました。

この記事の前に書いたヒープソートのコードは、Wikipediaや海外の複数のサイトで紹介されていたC言語のアルゴリズムを参考にしたものでした。それらが間違っているとは言い切れないし、難しいところです。ただ、効率化された新しいヒープソートと紹介した部分は間違っていますので、ここで訂正します。

簡単な時間測定で、通常のヒープソートよりも時間が短縮されます。コムソート１１よりも少しだけ早くなりました。100万個の配列での相対的比較です。処理数は最低で13,815,511回となりますので単純計算で１万個の配列のソートテストを少なくとも150回して平均値を取ったのとほぼ同じになります。10,000,000*log(10,000,000)/10,000*log(10,000)=150（テストは乱数で作った配列の並び替えです）

アルゴリズム的には当然、O(N*log N) の範囲内での改善になります。コードは以下に紹介しています。

According to the HeapSort algorithm, the portion of the comparison & swap was all done in loop. I have removed the portion of compare & swap from the loop. As a result, large amounts of processings of sorting have been improved. New HeapSort is equivalent to CombSort11 or became more than it.

Continue reading

エクセルのシート上のグラフをGIF画像として保存するソースコードを紹介します。

This procedure will create GIF image of chart to selected folder.

Continue reading

Introducing English version of a little Excel software 'GraphControl'. This software will help your Excel work, and save your time.

Download GraphControl_en Ver.203

• Ajust height of width of charts(if "ALL" is checked, then all chart will be ajusted)
• Change color of chartarea, plotarea, wall(3D), floor(3D) by using Excel est. Textures, gradation, and 56 color-index
• Change to rounded or square corner, or shadow graph.
• Change Rotation, Elevation etc. of 3D graph.
• Create graph image(GIF) to selected folder
• Reverse graph both X and Y axis
• Change color and style of marker in line graph
• And so on...

I would like to refer and speak to freedom on this little Excel software "GraphControl". I offer you this license which gives you legal permission to copy, distribute and/or modify the software. It is complete open source, and I am very welcome you to modify and develop "GraphControl" to better software to distribute. You can change the link label on UserForm to your website only if you can support end users. Also, for each author's protection and ours, I want to make certain that everyone understands that there is no warranty for this software. Arg. We do not need "Tower of Babel" in the world of computer, don't we?

GraphConrol Ver. 201 GraphConrol Ver. 202を配布開始します。新機能を追加しています。

• グラフを指定フォルダに GIF画像として保存できるようにしました。
• ツールバーのメニューに追加しました。
• 複数のブックを開いていても、ブック、シート、グラフを指定することが出来ます。

使い方は、グラフコントロール画面のコンボボックスで開いているブックを選択してボタンを押すと指定されたブックがアクティブになります（後から開いたブックは読み込みません）。オプションとして、カスケードスタイルやタイルスタイルなどを選択して表示出来るようにしました。

グラフ画像作成ボタンでは、選択したグラフをコンボボックスで指定したフォルダに保存します。フォルダがないと保存出来ませんので、前もってフォルダを作成しておいてください。「全部」のグラフが指定されている場合は、シート上の全グラフ画像が作成されます(画像はGIF形式です)。保存されるグラフ画像の名前は、グラフタイトルです。もしタイトルがなかったら、ブック名（上のコンボボックスが空欄の場合はなし） + _グラフ番号で保存されます

ダウンロードは↓です。
DownLoad GraphControl Ver.203

今回のソート・アルゴリズムはCombSort と呼ばれるものです。これも、N * Log N の計算量です。エクセルVBAで、1,000,000 の数値の配列を２０数秒で並び替えます。ヒープソート(Heapsort)も、アルゴリズムの理論値が同じですから、ほぼ同じような結果になります。N²のアルゴリズムのソート方法では、10,000の並び替えに同じくらいの時間がかかりました。その方法で、1,000,000 の数値配列の並び替えは無理かもしれません（実験しようとも思いません）。アルゴリズムを構築する者があまりハードウェアの分野に入り込まない方がいいかもしれません。

This sorting algorithm is called CombSort. The quantity of calculation is also N*LogN,and the array of the numerical value of 1,000,000 is sorted in about twenty seconds by Excel VBA. Heapsort also brings the almost same result, because of same theoretical value of an algorithm. By the sorting algorithm of N², sorting of 10,000 took about the same time. So that, by that method, sorting of the numerical array of 1,000,000 may be unreasonable. I think those who build an algorithm had better not seldom enter into the field of hardware.

Continue reading

ヒープソート(Heap Sort)のVBAコードを紹介します。クイックソート(Quick Sort) をVBAで使うとエラーが発生するので、長い配列の並び替えをする場合にこのコードがあるととても重宝すると思います。N² の原始的なソート方法とは比較にならないほど早いです。

Quick Sort Algorithm (クイックソート)

ソートのアルゴリズムについて： ソートのアルゴリズムの計算量は、分かりやすいように大きく２つに分類すると、N²とN*Log N に分けられます。アルゴリズムを構築する場合、この "N" はとても大きな数値だと仮定しなければいけません。下のテーブルのように、計算量がN² の 1/100 となったとしてもアルゴリズム上の理論値は N²です。

The VBA code of heap sort is end of this article. I think that it is found useful very much if there is this code when sorting long array, because an error will occur if quick sort is used by VBA. It is so fast that it cannot be compared with the primitive method.

下のコードはに、３つの N² のアルゴリズムのソートです。１つ目のソートは、順番に小さい数値を見つけて挿入していくソートです。このソートは、N² * 1/2 の計算量です。２つ目は、最初と最後を比較していくソートで、３つ目は一番小さい数値と大きな数値を見つけて挿入するソートです。このソートは、N² * 1/4 の計算量です。4つ目は、一番小さい数値と大きな数値を２つ見つけて挿入するソートです。計算量は、N² * 1/6 になります。膨大な量のデータをこういった原始的なアルゴリズムで並び替えようとすると大変なことになります。

These are 4 samples of primitive method which takes quantity of calculation N².

下のリンクに続きます。

Continue reading

先日書いたクイックソートの記事で少し勘違いしてた部分がありましたので書き換えました。今回は、Procedure と Function の両方のコードを紹介します。ExcelVBAのレベルでは、今のところこういったソートを個人レベルで利用することはあまり必要ではありませんが、多くの人が使うアドインなどに組み込む場合は必要です。クイックソートの他にヒープソート(Heap Sort)と呼ばれる（これも計算量：N*Log N）で安定したソート方法がありますので次の記事で紹介します。

Heap Sort（ヒープソート）記事

QuickSort のアルゴリズムを用いたVBA 用のコードを紹介します。このアルゴリズムは配列に適しています。クイックソートはある条件下において、効率が悪くなることがあります。このアルゴリズムは、比較するためのPivotを配列の中心の値にしてますが、本来のPivot の位置は配列の先頭の値です。この場合だと、降順を昇順に、または昇順を降順にソートする場合に処理量がN²となってしまいます。Pivotの位置を変えても、一定の条件下では効率が悪くなることがあります。イントロソートと呼ばれるソート方法がありますが、これはクイックソートの効率が悪くなる条件の場合、ヒープソートに切り替えるものです。

しかし、平均的に、N*Log N の処理量を実現してますので、とても優秀なアルゴリズムです。しかしながら、エクセルVBA のレベルでは、配列が大きくなったとき、「スタック領域・・・」とエラーが出て、あまり使えそうにありません。長い配列を使う場合はヒープソートを使うといいと思います。本来は、本当に処理速度を追求するのであれば、配列なんて便利なものは使えません。便利なものは必ず何かを犠牲にします。

クイックソートが作られた時代に配列などはなく、データ構造はレコードをポインターで繋いだ構造でした。ポインターを使うことでとても自由なプログラミングが可能になりますが、一度ポインターが切れるとデータは２度と捕まえることが出来ません。その環境で書かれたプログラムは、アルゴリズムが完成してないと、厄介なプログラムになってしまいます。 ２０年前に、ポインターをネットワーク状に張り巡らせたソートプログラムを作りました。少しだけメモリを使いますが、安定したプログラムでした。機会があったら紹介したいと思います。

I rewrote this article, since there was a portion which misunderstood with the article of quick sort written the other day. Although it is not so much required on the level of ExcelVBA to use such sorting on an individual level for the moment. It is called the "Heap Sort" other than quick sort which is the stable sorting method, it introduces with the following article.

Heap Sort

This is code of QuickSort Function & Procedure which is written in VBA. This algorithm is suitable for sorting array. Since quick sort has realized the amount of processings of N*Log N, it is a very excellent algorithm on the average, also sometimes its efficiency may worsen under a certain condition. Although this algorithm makes Pivot for comparing the value of the center of arrangement, the position of original Pivot is a value of the head of arrangement. The amount of processings will be N² if quicksort sort an ascending order to descending order. Even if it changes the position of Pivot, efficiency may worsen under certain conditions. Although there is the sorting method called "intro sort". this method will change to a heap sort, when the efficiency of quick sort is the worsening conditions. if processing speed is pursued truly, a convenient thing cannot be used. convenience sometimes sacrifice something.

I was writing Pascal. It's very free programming is attained by using a pointer. The program written in the environment will turn into a troublesome program, if the algorithm is not completed. 20 years ago, I have made the sorting program which spread the pointers around in the shape of a network. it was the more stabilized program than quick sort. I want to introduce, when there is an opportunity.

Continue reading

生まれてからの、日数と週数、そして干支を求めるマクロです。

This sample code find out number of days&week from your birth.

Continue reading

２０年前（１９８０年代）の初期のPascal言語では、今のようなStringやVariantなどの変数がなかったので、文字列を読み込む際は、１文字ずつループで読み込みをしていました。変数CharでスペースやIntegerを読み込もうとするとエラーとなり、条件を指定してから読み込むコードを書いていました。Turbo Pascalがパソコン用のソフトで出てきたころからStringが認識できるようになりました。

Excel では、セル値などの(.Value)の付いているものはほとんどがＶａｒｉａｎｔ型です（Spinbutton.Value=Long などの例外もあります）。エクセルの数式で、
Range("A1").Value+Range("B1").Value
などと、セル値同士を計算させることも出来ます。しかしＶａｒｉａｎｔ型同士の計算式はエクセルが自動で処理してくれているだけで、これはエラーの原因にもなります。簡単な処理であれば問題はないでしょうが、大量の計算処理をした場合は何の保証もありません。PascalであれExcelであれ、扱っているデータタイプをしっかりと区別してから処理させることは基本であり、とても重要なことです。

20 years ago (the second half of the 1980s), I have leaned Pascal language. In Pascal at that time, since there were no variables, such as present String and Variant, when reading a character string, it was reading one character at a time by the loop. The code read after specifying conditions to prevent error. When Turbo Pascal came out with the software for personal computers, Pacal could recognize Var String.

In Excel, most of variables, such as a cell value are Variant type (there is also an exception of Spinbutton.Value=Long etc.). Cell values can also be made to calculate with Range("A1").Value+Range("B1").Value etc. with the expression of Excel. However, this formula is calculated automatically by Excel. While it is in easy processing, it will not cause error. But when a lot of calculation processing is carried out, there is no guarantee. Whether if Pascal, Excel or other language, it is foundations to make it to process, after distinguishing the data type, and it is very important.

前の記事で、スピンボタンを使ってスクロールする方法の記事を書きましたが、スピンボタンのイベントでSpinbutton_Change() 以外のイベント(SpinDown & SpinUp イベント)を使用する方法を紹介します。プロシージャは２つに増えてコマンドボタンを使うのと同じような感じになります。

Spinbutton_Change() で操作する方法

using SpinDown event & SpinUp event instead of Spinbutton_change().

Continue reading

エクセルの複数開いているブックの表示をアレンジして変えるコードです。

How to arrange windows of Excel books.

xlArrangeStyleVertical

xlArrangeStyleCascade

xlArrangeStyleHorizontal

Continue reading

前回にシートの並び替えに引き続き、今回はグラフに番号の名前をつけるマクロを紹介します

How to change name of chart to specific number.

Continue reading

エクセルシートを指定した順番に並びかえるマクロを紹介します。

Rearranging Excel sheets to prevent error.

Continue reading

エクセルグラフの目盛り線を消すマクロです。

delete gridlines of xlValue & xlCategory of Excel chart.

Continue reading

スピンボタンを使って、グラフの高さ（幅）などを設定する簡単なアルゴリズムを紹介します。

Easy algorithm to change Excel Chart Setting by spinbutton.

下記のページは、Spindown & SpinUp イベントを使用した例です。プロシージャが２つになりますので、コマンドボタンを使った時とほぼ同じようになります。

Spinbutton_spindown & spinup event Continue reading

エクセルグラフの高さと幅を設定うするマクロを紹介します。

Continue reading

エクセルグラフをパターン色にする。

Continue reading

MarkerStyle （マーカーの種類)

エクセル既定のマーカーの種類です（９種類）。系列１を選択する場合は、ActiveChart.SeriesCollection(1).Select となります。サイズは２～７２までです。

Sample Code

エクセルの目盛り線を設定するマクロを紹介します。

Source code below is to set up gridlines of Excel chart.

Continue reading

エクセル3Dグラフの回転の値を設定する。

setting up ActiveChart.Rotation of Excel chart.

訂正のお知らせ：３Ｄグラフの回転を設定するコードに誤りがありました。 Rotation = ActiveChart.Rotationの部分が Rotation = ActiveChart.Elevationとなっていました。訂正と改善した部分がありましたのでお知らせします。

Continue reading

このプログラムは、株などのシステムトレードのように、ルーレットシステムが成立するかどうかを簡単に検証するものです。答えは、このページで説明しているようにほぼ１００％不可能です。

画像は、仮想ルーレットデータを10,000回の乱数を発生させて以下を検証したものです。

　　
1. ルーレットがRed,Red, Evenと続いたとき、Redに５％
　　
2. ルーレットがRed,Red, Even, Even, Blackと続いたとき、Odd（奇数)に1５％
　　
3. ルーレットがRed,Red, Even, Even, Black, Black,Oddと続いたとき、Blackに25％

結果は、図の例のように時々プラスに転じる場合があります。

This program verifies whether you can build a Roulette System like trading system of Stock market.　The answer is, almost 100% impossible because distortion of odds has no trend. For example, if you repeat trading in stock market when stock price does not change, you'll never make anything.

Image is verification of 10,000 times virtual roulette data according to probability below.

　　
1. Bet 5% of funds to "Red" when roulette become as Red,Red,Even.
　　
2. Bet 15% of funds to "Odd" when roulette become as Red,Red,Even,Even,Black.
　　
3. Bet 25% of funds to "Black" when roulette become as Red,Red,Even,Even,Black,Black,Odd.

As a result in this case, become 650000 profit. Sometime it's happened. We call it "luck". Chart of profit and loss curve is below.

Download is under preparation

Download Roulette System Simulation

損益曲線は以下のとおりです。

Continue reading

エクセルグラフを既定のテキスチャに設定するマクロです。

Set up Excel chart by texture of ground colors which is fixed in Excel.

Continue reading

グラフを透明にするマクロを紹介します。

Setting up Excel chart transparently.

Continue reading

エクセルグラフのプロットエリア、チャートエリア、壁面、床面を既定のグラデーションに設定するマクロを紹介します。以下は、エクセル既定のグラデーションの種類とタイプです。

The following table indicates the est. color in Excel.

Continue reading

エクセルチャートの色を単一色に変えるソースコードです。

At this article, introducing some source codes to change graph into a single color.

Continue reading

エクセルのシート上のグラフをユーザーフォームなどで動かすためのコードを紹介します。

At this article, introducing source codes to move charts on Excel sheet by commandbutton.

Continue reading

株価チャートの陽線と陰線(ローソク足)の色を設定するサンプルコードを紹介します。

I have added to an option to GraphControl that is for stock chart. It's able to change color of candle bars that is shown in stock chart.

Continue reading

エクセルグラフの系列のインテリア、ボーダー、線の色、マーカータイプと大きさなどを設定するソースコードを紹介します。

At this time, introducing some codes to set up seriesCollection. it will be the LineStyle, Maker style, color etc.

Continue reading

エクセル３Ｄグラフの奥行きを設定するマクロを紹介します。

Excel VBA source code which set the value perspective of 3D Chart.

Continue reading

UserForm からエクセルシートをスクロールさせるマクロです。

Scrolling Excel's spead sheet using spinbtton.

SpinUp & SpinDown event（別のイベントを使った例）

Continue reading

エクセル３ＤグラフのＸ軸高さの比率を設定するマクロを紹介します。

introducing the excel's source code of setting the 3D chart (.HeightPercent).

Continue reading

エクセルの３Ｄグラフの仰角を調整するマクロを紹介します。

Continue reading

エクセルのグラフに影をつけるソースコードを紹介します。

This source code is to create shadow background of the selected chart.

Continue reading

エクセルグラフのコーナーを丸くしたり、角にしたりするソースコードです。

Round or square corners of excel charts.

Continue reading

前回は、数値軸（Ｙ軸）の反転させるコードを紹介しました。今回は、項目軸（Ｘ軸）の反転をするコードです。

At this time, introducing a code to reverse xlCategory of selected chartobject.

Continue reading

エクセルグラフのＸ軸、Ｙ軸（第１軸、第２軸）を反転するエクセルマクロのソースコードを紹介します。

実行されると、チャートの数値軸（Ｙ軸）を反転します。もし上下反転していれば、元に戻ります。

The code reverses selected graph upside down. Procedure carry out both Axes(xlValue, xlPrimary) and Axes(xlValue,xlSecondary). If the selected chart is already reverse order then will put it back.

Continue reading