カテゴリー別アーカイブ: Excel

Excel vba でセルの値が変更されたら実行する処理を書いてみる

例えば、顧客管理の中で配送業者名(あるいはその記号)を入力した時に、セルの色を変えたいなんてことがあった。

Worksheet_change_01

こんな感じで、運送業者や配送方法によってセルの色を変えたいという要望ね。

Excel2007以降は「条件付き書式」でいくつも指定できるから、単純にセルの色だけを変えたい場合にはそれを使っても良いでしょう。

しかし、今回は他の処理も同時に行いたいという要望もあったので「条件付き書式」では対応が出来なかった。

そこで使うのが「change」というイベントプロシージャ。

Worksheet_change_02

changeというイベントプロシージャは、そのワークシートのどこかのセルが変更されたら実行するもの。

そして、「Target As Range」ということから、Targetという変数にはRangeが渡される(正しくはRangeオブジェクト)。

実行される処理としては、変更されたセルがH列の場合にセルの色を変える内容が書かれている。

まぁ、この例は単純なもので、

case “s”
Cells(Target.Row, Target.Column).Interior.ColorIndex = 35

の後に

 Cells(Target>row, Target.Column + 1).value = “佐川急便元払”

とか、なにか処理を続けても良いと思うのね。

 
このchangeイベントはけっこう便利。

Excel vba でIEを操作してみる 【 IEがすでに起動しているかどうかの判別 】

Dim ie_obj As Object
Dim ObjShell As Object
Dim ObjWindow As Object
Dim WinExist As Boolean
Dim WinPage As String

WinExist = False
Set ObjShell = CreateObject(“Shell.Application”)
For Each ObjWindow In ObjShell.Windows
  If TypeName(ObjWindow.Document) = “HTMLDocument” Then
    WinExist = True
    Set ie_obj = ObjWindow
    If ie_obj.locationName = “Google” Then
      WinPage = “opend”
    End If
  End If
Next
‘ WinExist、IEが起動していたらTrueにするが、最初はFalse

‘ For Each でウィンドウがある限り繰り返し処理
‘ そのウィンドウのドキュメントタイプがHTMLかどうか
‘ HTMLならIEなのでWinExistにTrueを代入
‘ 見つけたIEをオブジェクトに代入
‘ さらに、そのロケーションネーム(タイトル)がGoogleかどうか
‘ GoogleならWinPageを”opened”にする

WinExist や WinPage なんてのはあってもなくても良いけど、

「IEが起動していなければ立ち上げる」とか
「IEは起動しているけど、目的のページ(タイトル)ではないから移動する」とか

そんな条件分岐に使ったりしても良いかと。

また、「locationName」を「locationURL」とすればURLを取得できるので、特定のURLを探し出したりすることもできる。

他にも

ie_obj.Name
ie_obj.Path
ie_obj.FullName
ie_obj.Width
ie_obj.Height
ie_obj.Left
ie_obj.Top
名前(Internet Explorer と入る)
パス(ディレクトリ名まで)
フルパス(ファイル名まで)
ウィンドウの横幅
ウィンドウの高さ
ウィンドウの左端からの位置
ウィンドウの上端からの位置

といった感じで情報を取得できる。

ランダムな数値を作る Excel、vba、そして算術型or銀行型丸め処理  ついでに、PHP、FileMaker

Excel ワークシート関数
 =round(rand()*(y-x)+x,0)
 これでxからyの間で乱数を作れる。

 例えば、23~37までの乱数を作りたいのなら「 =round(rand()*14+23 」となる。

 rand()は0から1未満の数字になる。なのでrand()*14は0から14未満。そこに23を足す。
 つまり23から37未満。これをround関数で四捨五入すれば、23~37までの乱数が作れる。

 
Excel vbaの関数
 =int(rnd*(y-x+1)+x)
 これでxからyの間で乱数を作れる。

 上の例と同様に23~37までの乱数を作りたいのなら「 =int(rnd*15+23) 」となる。

 で、ここで使われるintは「小数点以下を切り捨てる」と言うとちょっとおかしいのかな。
 「整数部分を取り出す」とか「小数点部分を取り除く」の方が良いのか。

 なので、rnd*15で0から15未満の数を作り、23を足すと、23から38未満の乱数ができる。
 これをintで整数部分を取り出してやれば、23~37の乱数が出来上がり。

 
 さて、vbaではround関数を使わずにint関数を使っているところが多いけど、なんでなんだろ。

 vbaのround関数は確かに、ワークシート関数のroundとは挙動が異なる。

 いや、挙動をしっかり把握していれば、「 =round(rnd*(y-x)+x) 」でも良いと思うよ。
 というか、普通にこっちでも問題ないのでは?

 ただ、vba関数のroundをちゃんと使う場面はなかなかないと思うし、正確に使ってる人はあまりいない。

 vbaのround関数は「銀行型丸め処理」になってるから。
 だから、roundではなくintを使っているところが多いのかも。

 vbaのround関数は簡単にってしまえば、「0.5」とか「3.5」とか「4.5」のような「x.5」となっている時、
 四捨五入するかどうかは結果が偶数になるかどうかで判断される。

 四捨五入の結果が偶数なら切り上げるけど、奇数なら切り捨てて偶数になるようにする。

 「0.5」→「0」、「3.5」→「4」、「4.5」→「4」といったようにね。

 
 ここで、次の数の合計は?

 「0.5」「1.5」「2.5」「3.5」「4.5」「5.5」「6.5」「7.5」「8.5」「9.5」

 はい、50です。

 では、これを四捨五入してから合計してみましょう。

 「1」「2」「3」「4」「5」「6」「7」「8」「9」「10」

 はい、55になります。

 続いて、銀行型丸め処理による四捨五入をしてから合計してみましょう。

 「0」「2」「2」「4」「4」「6」「6」「8」「8」「10」

 はい、50になります。

 ね、なんか、単純に四捨五入するよりも、元の数を合計した値に近くなる。というかこの場合同じになる。

 いくつかx.5な値を選んでワークシート関数的な四捨五入=算術型丸め処理と銀行型のそれとを比べると、
 銀行型の方が、元の数の合計に近くなる傾向にあることがわかる。

 まぁ、そんな特性を必要とする人が、Excelのvbaユーザーに、割合としてそれほど多いとは思えないけど。
 それでも、そんな違いがあるから、みんなintで処理してるのかもしれないね。

 ついでに、vbaのround関数で

 round(1.05,1) とした場合、結果は「1.0」

 round(1.15,1) とした場合、結果は「1.2」

 となる。

 別に「x.5」である必要があるわけではなく、四捨五入をするための比較検討をする数値が「5」であるか
 そこがポイント。

 かりに「5」ではなく、さらに小数点以下が続いていて「5000001」だったら、

 つまり round(1.05000001,1) だったら、この結果は「1.1」になる。

 ややこしいですね。

 でも、四捨五入という処理はわりと日常的に行われているだけに、
 vbaでの round は銀行型だということを覚えておいてそんはないでしょう。

  
 また、どうしてもvbaでワークシート関数のroundを使いたければ、

 WorksheetFunction.Round(xxx.x,y)

 とでもしてください。

 詳しくは http://support.microsoft.com/kb/225330/ja といったMSのサイトで。

 
 

さて、ついでなのでPHPではランダムな値をどう求めるか。

rand( int $min , int $max )

 まんま、ですな。

 rand(23,37)

 これで23~37の乱数が出ます。

 

さらについでで、FileMakerでのランダム。

 Random * (y – x) + x でOK。

 全レコードからランダムにあるひとつのレコードを選ぶ、といったような場合にはこんな感じ。

 Random * Get ( 対象レコード数 ) + 1

 
 

ぶっちゃけ、Excelのワークシート関数も使うしvbaも使うし、PHPもFileMakerも使うのに、ちょっとした適当などうでも良い程度のランダムな値を作るのに、こんなにも作法があるとはねぇ・・・

Excel vba でIEを操作してみる 【 とにかくまずはIEを起動 】

下記3行で、IEは起動する。

Set ie_obj = CreateObject(“InternetExplorer.Application”)
ie_obj.Visible = True
ie_obj.Navigate2 “http://www.google.co.jp”

基本的にはこれだけなんで、簡単。

ここで Navigate2 というのをつかっているけど、Navigate というのももちろんある。
が、特に理由がなければ 2 を使った方が良いでしょう。
後々「新規タブで開きたい」なんて時には 2 でないとできないことがあるので。

ちなみに、新規タブで開くには

ie_obj.Navigate2 “http://www.google.co.jp”, 2048

と第二引数に「2048」を指定する。(16進数で「&H800」とかでもいい)
また、ここを「4096」(16進数では「&H1000」)にすると、新規タブで開くけど、非アクティブな状態になる。

 
 
そのほか、知っていると便利なオプションがいくつか

「objIE.Visible = True / False」
 IEの表示・非表示設定。Trueで表示。
 IEの操作開始時はFalseにしておいて、一通りそうさが終わったらTrueにすると良いかも。
 まぁ、自動でIEが操作されて画面が遷移していくのを眺めてるのも面白いけどね。

「objIE.FullScreen = False」
 フルスクリーン表示にするかウィンドウ表示にするか。
 デフォルトはFalseでウィンドウ表示。フルスクリーンにしたければTrueに。

「objIE.Top = 100」
「objIE.Left = 100」
「objIE.Width = 800」
「objIE.Height = 600」
 位置情報の指定。そのまま、ですな。

「objIE.Toolbar = True / False」
 ツールバーの表示・非表示を設定。デフォルトはTrueで表示する。

「objIE.MenuBar = True / False」
 メニューバーの表示・非表示を設定。デフォルトはTrueで表示する。
 ただ、IE7以上では見かけ上、メニューバーが表示されない。
 「Alt」を押すとでてくるので、あえてこれをFalseにするのは対象がIE6の時くらいかな?
 尚、ツールバーを非表示にすると、メニューバーも自動的に非表示設定になる。
 メニューバーをTrueにしてもツールバーがFalseだと表示されない。
 
「objIE.AddressBar = True / False」
 アドレスバーの表示・非表示を設定。デフォルトはTrueで表示する。
 これもツールバーを非表示にすると自動的に非表示設定になる。
 しかし、ツールバーをFalseにした後に、アドレスバーをTrueにすれば表示できる。
 (各順番が逆になると当然表示されない)

「objIE.StatusBar = True / False」
 ウィンドウ最下部のステータスバーの表示・非表示設定。デフォルトはTrueで表示する。