Excel VBA 質問スレ Part60

**デフォルトの名無しさん** · 2019/03/16(土) 20:39:09.64

ExcelのVBAに関する質問スレです
コード書き込みや作成依頼もOK
※前スレ
Excel VBA 質問スレ Part59
https://mevius.5ch.net/test/read.cgi/tech/1549692750/

**デフォルトの名無しさん** · 2019/04/03(水) 20:18:32.12

2013ってOSどこからサポートしてたっけ

**デフォルトの名無しさん** · 2019/04/03(水) 20:25:25.63

そんなのググったらすぐ出るだろう…

**デフォルトの名無しさん** · 2019/04/04(木) 00:26:33.22

新元号は「安平」であります。
https://dotup.org/uploda/dotup.org1812320.jpg

**デフォルトの名無しさん** · 2019/04/04(木) 02:37:49.53

あ
ほ

**デフォルトの名無しさん** · 2019/04/04(木) 09:42:49.96

>>416
これは、Office各バージョンで共通で使えるしExcel・Word等でも使えるね
他のアプリでも使えるんじゃない

**デフォルトの名無しさん** · 2019/04/04(木) 10:15:48.02

>>416
これは、Windowsのカレンダーに対して日本用の設定を追加してるだけだから
Windowsの標準コントロールのカレンダーを使用して正しくコーディングしていれば
この設定をするだけで対応できるだろ
これを「令和登録.reg」のファイルにして管理者モードで実行するだけだね
--下記--
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\Calendars\Japanese\Eras]
"2019 05 01"="令和_令_Reiwa_R"

**デフォルトの名無しさん** · 2019/04/04(木) 10:47:09.88

VBAでfor nextで回しているときにworksheetfunction.vlookupを使っているのですが
値が存在しないときにエラーが出て止まってしまいます。
こんなとき存在しない時は処理を回避するにはどうしたらいいですか？
On error resume nextにすると、エラーのときにも処理をしてしまいます。

**デフォルトの名無しさん** · 2019/04/04(木) 10:56:05.80

application.vlookup

**デフォルトの名無しさん** · 2019/04/04(木) 11:06:48.53

>>423
On error resume next　はエラーを無視して続ける
On error go to XXXX でえあらーが出たらそっち行け

**デフォルトの名無しさん** · 2019/04/04(木) 11:17:40.01

>>425
vlookupで見つかったらA処理、見つからなかったらB処理、をそれぞれさせる場合
どうするの？

**デフォルトの名無しさん** · 2019/04/04(木) 11:18:13.34

そっち行けのあと、ちゃんとfor nextループに復帰しないといけない。

**デフォルトの名無しさん** · 2019/04/04(木) 11:27:09.76

値が存在するか確認してからvlookupでもOK

**デフォルトの名無しさん** · 2019/04/04(木) 12:16:12.36

>>427
基本的にgo toの行先はループ内だな
で、正常の場合はそのルーチン内で飛ばす、また共通で使用できる部分はそのまま使う
それぞれ複雑になる場合は、外部呼出しで処理

**デフォルトの名無しさん** · 2019/04/04(木) 12:40:19.97

簡易だけどこんな風でよろしいか。

On Error Resume Next
For i=0 To lngRW
varValue=WorksheetFunction.Vlookup(～)
If Err.Number=(見つからない) Then
'B処理
Err.Clear
ElseIf Err.Number=0 Then
'A処理
Else
Debug.Print "想定外:" & vbCrLf & Err.Description
EndIf
Next i
On Error Goto 0

**デフォルトの名無しさん** · 2019/04/04(木) 13:14:42.92

こうじゃないの

On Error Goto err
For i=0 To lngRW
varValue=WorksheetFunction.Vlookup(～)
'処理
err:
Next i

**デフォルトの名無しさん** · 2019/04/04(木) 13:23:37.46

>>431
一般的に書くと以下だけど、処理は複雑なら外部ファンクションで

On Error Goto err
For i=0 To lngRW
varValue=WorksheetFunction.Vlookup(～)
[正常処理]
Goto 共通
err:
[err処理]
共通:
[共通の処理]
Next i

**デフォルトの名無しさん** · 2019/04/04(木) 13:50:38.20

>>432
あぁそうか、その形だスマン

エラー番号を見たり外したりで分岐させる方法が合ったと思うけど、
あれってどうなのかな

**デフォルトの名無しさん** · 2019/04/04(木) 14:26:57.06

Dim Rc As Variant

For I = 1 To 10
Rc = Application.VLookup(～)
If IsError(Rc) Then
　　'B処理
　　Debug.Print "Err"
Else
　　'A処理
　　Debug.Print Rc
End If
Next

**デフォルトの名無しさん** · 2019/04/04(木) 16:38:45.71

If Err = *** Then

**デフォルトの名無しさん** · 2019/04/04(木) 16:56:03.91

このエラーって何が返ってきてるの？
Rc = Application.VLookup(1, Range("a1:b2"), 2, 0)
Rcには「エラー2042」
variant/error
となっている
エラーオブジェクト？

**デフォルトの名無しさん** · 2019/04/04(木) 17:24:54.95

Debug.Print WorksheetFunction.IsNA(Rc)

**デフォルトの名無しさん** · 2019/04/04(木) 17:32:46.09

>>436
VBA CVErr 関数
https://www.tipsfound.com/vba/05cverr

2042 #N/A

**デフォルトの名無しさん** · 2019/04/04(木) 18:04:33.36

>>437
？

>>438
errorってオブジェクト？
なんでsetせずに代入できるんだ？

**デフォルトの名無しさん** · 2019/04/04(木) 19:42:12.58

on error使うのあまり良くない
vlookupの前にcountやfindで存在チェックするのをおすすめするよ
裏技的な方法だとAppllication.Vlooiup

**デフォルトの名無しさん** · 2019/04/04(木) 19:42:34.71

Application.Vlookup

**デフォルトの名無しさん** · 2019/04/04(木) 19:50:28.21

それが彼の最後の言葉だった

**デフォルトの名無しさん** · 2019/04/05(金) 03:50:15.90

>>439
宣言済みのグローバルオブジェクトだから

**デフォルトの名無しさん** · 2019/04/05(金) 08:49:34.62

>>443
ありがとう！そんなんあったのか

**デフォルトの名無しさん** · 2019/04/05(金) 09:38:24.06

なんでマクロを表から実行する機能がボタンしかないんですか！
ボタンだけ画像で気持ち悪い

**デフォルトの名無しさん** · 2019/04/05(金) 09:43:10.12

>>445
alt+f8

**デフォルトの名無しさん** · 2019/04/05(金) 10:33:41.98

>>445
セルのクリックで選択が変わったイベントを取るとか
他にもできるけど、単に知らないだけだろ

**デフォルトの名無しさん** · 2019/04/05(金) 10:44:09.79

>>442
悪い奴じゃなさそうだが、残念だったな
所詮、何でも道具なんだから目的に合わせて
臨機応変に対応しないとな

**デフォルトの名無しさん** · 2019/04/05(金) 10:53:42.85

>>445
そういえば、聞き漏らしたけど
>ボタンだけ画像で気持ち悪い
これ意味が分からない
例えば表に更新ボタンがあるのが気持ち悪いか？

**デフォルトの名無しさん** · 2019/04/05(金) 11:15:00.27

>>445
セル内に任意の Sub を走らせるコマンドを書きたいということかな？

**デフォルトの名無しさん** · 2019/04/05(金) 11:16:44.20

ボタンを透明にして、セルをクリックしたように見せかければいい

**デフォルトの名無しさん** · 2019/04/05(金) 11:28:07.60

>>451
それは見方によっては罠なのでは？

**デフォルトの名無しさん** · 2019/04/05(金) 11:31:39.64

>>451
例えば以下でいい、透明なボタンなんてメンテナンスしにくい
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
「セルA1を選択された時の処理」
End If
End Sub

**デフォルトの名無しさん** · 2019/04/05(金) 12:16:59.99

>>445
何を言ってるんだ?
標準モジュールに以下を書いて、
セル内に=FuncNibai(2)と入れてみろ。

Public Function FuncNibai(lngNum As Long) As Long
FuncNibai=2*lngNum
End Function

**デフォルトの名無しさん** · 2019/04/05(金) 12:20:44.27

>>454
それでは基本的にシートに対する副作用のない動作しかできないよ

**デフォルトの名無しさん** · 2019/04/05(金) 12:31:55.05

>>455
ボタンから実行していないだろ。
表を変えたいというなら、入力したセルと無関係のセルの値を変更する処理も書ける。
そんなことする奴はおらんだろうが。

そもそも>>455がどうしたいのかが無いから、どうにもならん。
個人的には、リボンから実行するように作ることが多いけど。

**デフォルトの名無しさん** · 2019/04/05(金) 12:42:46.47

>>456
落ち着けよ、445の釣りかもしれんし

**デフォルトの名無しさん** · 2019/04/05(金) 12:51:21.11

>>456
基本的に、新規関数の定義だから
それ以外のことをするのは、混乱の元だよね

**デフォルトの名無しさん** · 2019/04/05(金) 13:41:00.30

イベントでマクロ起動できるようになりました。ありがとうございます！
ハイパーリンクのイベント使いました。

でも
イベントはシート単位でマクロ側で呼び出し元特定する仕組みで
間違えるの怖いしシート全体でマクロ走るし
ハイパーリンクは自分のセル位置変わっちゃうし
余計気持ち悪くなりました

ハイパーリンクに直接マクロ指定したりとか
もっとスパッと間違えない仕組みがないものでしょうか

**デフォルトの名無しさん** · 2019/04/05(金) 13:44:20.23

>>459
何がしたいのかもう少し具体的に言ってくれれば誰かがマクロ書くと思う
＞シート全体でマクロ走るし
これも以下のような形で、特定のセルだけマクロが走るように制御することも可能

Private Sub Worksheet_Change(ByVal Target As Range)
行 = Target.Row
列 = Target.Column
If 行 < 4 And 行 > 2 And 列 = 2 Then
'処理
End If
End Sub

**デフォルトの名無しさん** · 2019/04/05(金) 13:58:27.50

>>459
スレタイ読めない馬鹿は死ね

**デフォルトの名無しさん** · 2019/04/05(金) 14:03:08.75

>>459
そんなの選択したセルがハイパーリンクか判断すればいいだけ
要求を書かないから特定のセルで動くマクロを提示しただけ
その前に
>ボタンだけ画像で気持ち悪い
これ意味が分からない

**デフォルトの名無しさん** · 2019/04/05(金) 14:40:23.82

ボタンはクリックしただけで手が滑って意図せずずれちゃったり
配置した後ろにカーソルが隠れちゃったりするから
なんか異物感があります

**デフォルトの名無しさん** · 2019/04/05(金) 15:16:00.65

>>463
そうか、理由はよくわからんがハイパーリンク化は
selection.hyperlinks.count
または
Range.hyperlinks.count
で取れるんでないの試してない

**デフォルトの名無しさん** · 2019/04/05(金) 16:03:40.10

>>459
＞ハイパーリンクに直接マクロ指定したりとか

ハイパーリンクのクリックでマクロを実行する
http://www.excel-wing.com/study/jitumu/941

**デフォルトの名無しさん** · 2019/04/05(金) 16:21:20.23

>>465
俺もこのほうが良いと思う確認してないけど

**デフォルトの名無しさん** · 2019/04/05(金) 16:25:16.09

まあ、どうやっても出来ないと書き込むんじゃなくて
何をどうしたいけど方法を聞けよ、できるだけ自分の求めることを
詳しく

**デフォルトの名無しさん** · 2019/04/05(金) 16:38:12.77

スライサーのボタンクリックをイベントとしてマクロを実行させたいんだけど、
ググっても探し方が悪いのか、「スライサーを作成するVBA」ばかりになってしまう。

どこか事例を解説しているWebページを紹介してもらえませんか？

**デフォルトの名無しさん** · 2019/04/05(金) 16:41:10.23

>>468
スレタイ読めない馬鹿は死ね

**デフォルトの名無しさん** · 2019/04/05(金) 16:50:23.23

ピボットテーブルのスライサーの事じゃないの？
別にスレチでもなんでもないと思うけど

**デフォルトの名無しさん** · 2019/04/05(金) 17:16:27.60

>>470
はい、そうです。

**デフォルトの名無しさん** · 2019/04/05(金) 17:26:04.18

>>471
スライサーにイベントハンドルかます事はできないっぽい
以下２つのどっちかになると思う

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Debug.Print Cells(4, 1)
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Debug.Print Cells(4, 1)
End Sub

https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheet.pivottablechangesync
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheet.pivottableupdate

**デフォルトの名無しさん** · 2019/04/05(金) 17:41:45.84

>>472
ありがとうございます。
ピボットテーブル本体まで戻れば
変更イベントハンドルがあるのですね。
この方向でやってみます。

**デフォルトの名無しさん** · 2019/04/05(金) 18:17:05.73

>>456
えっ？
副作用のある操作もできるのか？？

**デフォルトの名無しさん** · 2019/04/05(金) 20:04:21.22

>>459
特定のセルクリックしたらマクロ実行とか

**459** · 2019/04/05(金) 20:52:19.13

いろいろやってみましたが
表をいじって位置が変わっちゃうと動かなくなったり、
変なリンクできたりするので
ボタンでやります…

ボタンをセルに合わせて自動調整されるようにプロパティ設定したら
ちょっと気持ちがおちつきました

ありがとうございます

**デフォルトの名無しさん** · 2019/04/05(金) 21:55:09.13

♪かけ違えたボタンがある～

**デフォルトの名無しさん** · 2019/04/05(金) 22:38:48.78

>>474

**デフォルトの名無しさん** · 2019/04/06(土) 02:33:50.43

主作用でなければ副作用だが

**デフォルトの名無しさん** · 2019/04/06(土) 05:07:20.11

ヒポリットテーブルってややこしい。
理解するこつあはありますか？

**デフォルトの名無しさん** · 2019/04/06(土) 09:18:19.10

>>480
ピボットテーブルな
繰り返し使うしか無い
後は元データもちゃんとしたものじゃないとピボットがうまく機能しない

**デフォルトの名無しさん** · 2019/04/06(土) 09:43:33.17

>>480
色々できて楽しいと思いながら
新しい使い方を検索しながら使いな

**デフォルトの名無しさん** · 2019/04/06(土) 11:28:05.41

>>454の関数で値を計算して返すついでにどこか別のセルの値を設定しようと
Range("A1") = 1
としたって通らないだろ
そんな制約があったんじゃ>>445に対する答えにはならんね

**デフォルトの名無しさん** · 2019/04/06(土) 12:56:33.17

マイクに話しかけたら実行されるようにすりゃいいんじゃないの?

**デフォルトの名無しさん** · 2019/04/06(土) 13:12:45.38

>>480
いろいろやってみると面白いし、
元データをテーブルにして組み合わせると、
計算速度も速いような？

融通が利かない部分もあるけど。
4～3月の月次推移グラフで、
ピボットテーブルには年度合計を出せるのに、
この合計もグラフに反映させる方法あるのか、ないのか・・・
日時時間フィルターのタイムラインは、使い勝手がイマイチ、
とか。

あと、縦横クロス集計のピボットテーブルを
一発でリスト表(3Dマップに使える)に変換する隠れ機能があったり。

**デフォルトの名無しさん** · 2019/04/06(土) 23:19:03.44

まずスレタイぐらい理解できるようになろう

**デフォルトの名無しさん** · 2019/04/07(日) 11:38:35.77

シート内にマクロを設定した
ボタンを１つと、複数の画像があります。

ボタンをクリックしたら以下コードを実行し画像の一括削除を行っています。
しかし、問題があってボタンまで消失してしまいます。

そこでボタンの場合には削除はしない
という風に制御を入れたいのですが、ボタンの名称はどうやって調べればよいでしょうか？

'シート無いの画像をすべて削除する。
For Each img In ActiveSheet.Shapes
img.Delete
Next

**デフォルトの名無しさん** · 2019/04/07(日) 11:59:22.40

>>487
ボタンに特定の名前を付けてそれをハードコーディングすりゃいいと思うがそれじゃ不満なのかな？

**デフォルトの名無しさん** · 2019/04/07(日) 12:06:51.57

>>487
img.Nameで名前を調べることができます

If img.Name <> "ボタンの名称" Then
　　img.Delete
End If

みたいに書けば、特定の画像の消去をスキップできます

**デフォルトの名無しさん** · 2019/04/07(日) 12:40:05.22

>>489
名前なんて変化するのでコーディングするのはない
プロパティで有るだろ、調べてないが常識的に

**デフォルトの名無しさん** · 2019/04/07(日) 13:13:42.16

削除対象の画像を予めShapeRangeにまとめるなり、削除対象の画像の名前をシートに書き出すなりしてボタンマクロで一括削除させる設計にする方が検索効率や保守効率が良いと思う
ボタン名やコントロール名でハードコーディングするのは下策
ボタンの数やコントロールの種類が増えたときにいちいち手直しする必要が出てくる

**デフォルトの名無しさん** · 2019/04/07(日) 13:22:52.48

動的にコントロールを追加するプログラムを作ってないの？
毎回追加で書いてるの？
それは面倒で下衆だわ

**デフォルトの名無しさん** · 2019/04/07(日) 13:30:07.71

＞動的にコントロールを追加するプログラム
昔作ったわ
EXCELでこの設計は止めたほうが良い。エラー頻発する

**デフォルトの名無しさん** · 2019/04/07(日) 14:02:19.38

>>487
For Each work in Shapes
　If work.type = 消したいオブジェクトタイプ Then 消す
Next

**デフォルトの名無しさん** · 2019/04/07(日) 15:13:26.49

>>474
>>478
>>483

スマンな。
>>456の時点では勘違いしてた。

でも、たぶん出来るだろうなと思ってやってみたら出来るな。
それどころか入力セルに対しても出来る。

**デフォルトの名無しさん** · 2019/04/07(日) 15:19:02.23

>>487は差のマクロが登録されたあるひとつのボタンだけを対象に「消したくない」と言っているように思えるのだが

**デフォルトの名無しさん** · 2019/04/07(日) 15:26:28.77

>>491
別にボタン名とかコントロール名でやっても良いと思うよ。
増えようが、名前付けル－ルがデタラメなんてこと無いだろう。
画像ならimgが先頭に付くとか、ボタンならbtnが付くとか、普通そうしてるはずだろ。

>>492
それは、この質問だけ見ただけじゃ何とも言えない。
消えなければ追加する等という作業は最初から必要無い。
動的に追加するような状況とも思えないな。

>>493
それはその組み方が良くないだけだろ。

**デフォルトの名無しさん** · 2019/04/07(日) 18:49:28.94

まあ、その時目の前の問題だけクリアーするのは筋が悪い
目的を汎用化し使い易くメンテナンスし易いように組むな普通

**デフォルトの名無しさん** · 2019/04/07(日) 19:51:51.79

VBAスレで何言ってんの
そういうことを言い出すとVBAなんか使うなという結論にしかならんよ

**デフォルトの名無しさん** · 2019/04/07(日) 19:54:51.59

EXCEL前提ならそりゃVBAになる

**デフォルトの名無しさん** · 2019/04/07(日) 20:30:11.80

>>488
ボタンに名前をつけるにはどうすればいいんですか？

**デフォルトの名無しさん** · 2019/04/07(日) 21:10:44.99

>>497
名前比較で実現出来るかどうかという実現可能性の話をしてるわけではない
あくまで効率性の話をしてるんだが
特定のボタンを消さないようにするという目的だけのためにShapesコレクションをなめてアイテムのNameプロパティを逐一調べるという処理は筋が悪い
ワークシート上に存在する画像や図形、テキストボックス、コントロール等の数だけ名前比較することになるからな
削除対象になる画像をワークシートに追加するタイミングでShapeRangeコレクションに入れ、ボタン押下でDeleteメソッドで一括削除というやり方の方が絶対に速い

**デフォルトの名無しさん** · 2019/04/07(日) 21:18:21.02

>>501
Nameプロパティ

**デフォルトの名無しさん** · 2019/04/07(日) 21:22:19.79

一つも目的だけの為に作り込むのが良いのか汎用性を持たせて使い回せるようにした方が良いのかって事で俺は汎用性がある方が長持ちして良いと思う派

**デフォルトの名無しさん** · 2019/04/07(日) 21:27:32.10

>>502
あのさあ、わざわざそんなことするほどの処理かね?
俺だったら名前ではやらんと思うけど、こんなのは何でも良い話だろ。

普通に考えて、ボタン1つに後は画像だけなんじゃね―の?
正直、こんなので効率も糞も無いと思うね。

**デフォルトの名無しさん** · 2019/04/07(日) 21:45:02.84

マクロに「Sub なんちゃら」って名前を付けるんだから、それを呼び出すボタンに固有の名前を付けるのは全く自然だ

**デフォルトの名無しさん** · 2019/04/07(日) 22:33:14.85

>>505
コレクションの総なめや文字列比較での処理分岐は処理速度の問題に直結するだろ
それにマクロで処理しなければならないくらい画像を張り付けてあるシートに対する操作なら尚更処理が重くなる可能性が高い
数百個くらい画像が貼ってあるかもしれないし

割と現実的な設計の話だと思うんだが、まぁ君が効率も糞もないと思うなら仕方ない

**デフォルトの名無しさん** · 2019/04/07(日) 22:37:25.36

質問者そっちのけ。

**デフォルトの名無しさん** · 2019/04/07(日) 22:38:12.36

>>502
> 削除対象になる画像をワークシートに追加するタイミングでShapeRangeコレクションに入れ
とか言うけど、その画像がプログラムで追加されるとは限らんよ
人が適当に描画した図形を単にクリアするボタンかも知れんし

**デフォルトの名無しさん** · 2019/04/07(日) 22:43:02.40

>>509
それならShapes.SelectallでShapeRangeコレクションに突っ込んで、マクロ呼び出し用のボタンだけShapeRangeから除外して一括削除だね
とにかくShapesの中でループを回す必要がない

**デフォルトの名無しさん** · 2019/04/07(日) 22:50:29.75

>>507
それは数百個貼るような状況の場合の話。
そういう場合なら、その時考える話だ。

例えば、それなりに組める奴ならScreenUpdatingなんかを使っても速度が速くなることは無いだろう。
俺の場合も当然そうだ。
だからと言って、普段そんなに速度を重視して組んでいる訳じゃ無い。
しかしだ、状況により速さが必要になったら当然それ用に変わるだろう。

いつも速度を重視して組んでいるならご苦労なこった。

**デフォルトの名無しさん** · 2019/04/07(日) 22:54:24.63

>>510
> マクロ呼び出し用のボタンだけShapeRangeから除外して
どうやってやるつもり？

**デフォルトの名無しさん** · 2019/04/08(月) 02:30:49.49

オブジェクトを直接比較すれば済む話じゃね
なんでわざわざ.Name呼び出して文字列比較するのかわからん
オブジェクトの比較なら何千あっても大して遅くならんし
馬鹿ばっかかよ

**デフォルトの名無しさん** · 2019/04/08(月) 02:33:33.22

そもそもshaperangeコレクションにアイテムをremoveするメソッドがあれば済む話なのにな
やっぱVBAって糞だわ

**デフォルトの名無しさん** · 2019/04/08(月) 06:39:30.33

やっと気付いた？

**デフォルトの名無しさん** · 2019/04/08(月) 08:39:44.42

20年進歩なければな