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/02(火) 09:24:24.65

>>403
本当だけど
それガッツリとしたVBAエンジニアだよ
多分gitかTortoiseSVN辺り使うはず

【フリーランス】Excel/Access-VBA | 開発知見がある方！システムサポート業務
仕事内容
基幹システム/部門個別システム支援（運用、問合せ、J-SOX対応等）
部門内インフラ管理支援、エンドユーザIT支援、業務用ツール開発
給与

**デフォルトの名無しさん** · 2019/04/02(火) 21:44:59.43

>>403
別にVBAでも普通ある。
けど、面接して採用される奴は他にもスキルがあるか、それなりに出来る奴だろうな。

gitや、SVNについては必ずしもってところだろう。
もっとも、VBAのコードを管理するためにgitやSVN用のツールをVBAで組む程度はやれる或いはやってるだろう。

**デフォルトの名無しさん** · 2019/04/02(火) 23:38:38.44

1989/1/7　→　昭和64年1月7日
1989/1/8　→　平成1年1月8日
2099/1/1　→　平成111年1月1日
3000/1/1　→　平成1012年1月1日

試してみたらこうなった。
令和がまだ入ってないのは分かるが、
1000年後も普通に平成だとは思わなかったわ。

**デフォルトの名無しさん** · 2019/04/02(火) 23:44:03.27

>>406
どうせならOSとEXCELのバージョンかけや

**デフォルトの名無しさん** · 2019/04/02(火) 23:45:56.37

>>406
https://dotup.org/uploda/dotup.org1812346.jpg

**デフォルトの名無しさん** · 2019/04/02(火) 23:47:51.13

>>407
どちらか片方ならわかるが、何で両方?
組み合わせで変わるもんじゃないだろう。

**デフォルトの名無しさん** · 2019/04/03(水) 01:37:48.37

>>409
MSはそんな思い込みが通用するほど甘い相手じゃない

**デフォルトの名無しさん** · 2019/04/03(水) 02:13:34.41

>>406
江戸もでないし

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

こういう思い込みをする奴が新年号対応に莫大な人工が掛かる糞システムを作るのである

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

>>411
シリアル値は1900年から
明治までしか対応できない

**デフォルトの名無しさん** · 2019/04/03(水) 07:43:03.53

Windows 10・WSL・Ubuntu 16.04 の設定ファイルでは、

+:2:1990/01/01:+*:平成:%EC%Ey年
+:1:1989/01/08:1989/12/31:平成:%EC元年

+:2:1927/01/01:1989/01/07:昭和:%EC%Ey年
+:1:1926/12/25:1926/12/31:昭和:%EC元年

+:2:1913/01/01:1926/12/24:大正:%EC%Ey年
+:2:1912/07/30:1912/12/31:大正:%EC元年

+:6:1873/01/01:1912/07/29:明治:%EC%Ey年

+:1:0001/01/01:1872/12/31:西暦:%EC%Ey年
+:1:-0001/12/31:-*:紀元前:%EC%Ey年

**414** · 2019/04/03(水) 07:55:00.58

明治6年(1873)から正しい

それ以前は太陰太陽暦(旧暦)だから、西暦とは異なるから、日本の暦は使えない

**デフォルトの名無しさん** · 2019/04/03(水) 12:14:49.07

Excel2013以降ならこれで行けると思う

Windows Registry Editor Version 5.00

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

**デフォルトの名無しさん** · 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プロパティ