Excel VBA 質問スレ Part62

2019/06/24(月) 00:21:37.48

!extend:checked:vvvvv:1000:512

ExcelのVBAに関する質問スレです
コード書き込みや作成依頼もOK

※前スレ
Excel VBA 質問スレ Part61
http://mevius.5ch.net/test/read.cgi/tech/1556203263/

VIPQ2_EXTDAT: checked:vvvvv:1000:512:----: EXT was configured

2019/08/28(水) 00:59:49.87

>>784
その通り、ワークシートから呼び出された関数からは別のセルは加工できない仕様になってる

2019/08/28(水) 03:05:45.46

>>784
状態変える処理やるとそこで強制終了するね
エラーでキャッチもできないし

2019/08/28(水) 10:32:02.44

Excel以外は会社の縛りで入れちゃダメって設定だったろ？

2019/08/28(水) 11:04:52.35

>>794
( ﾟдﾟ)ﾎﾟｶｰﾝ

2019/08/28(水) 12:15:47.11

>>792-793

Cells(1, 1).Interior.Color = vbRed　を

Cells(1, 1).Font.Color = vbRed

にすればちゃんと動くんだがなあ

残念

2019/08/28(水) 13:03:44.86

>>794
普通はAccessも入ってるもんだが。

2019/08/28(水) 13:06:36.95

>>796
実施後にセルの色を変える処理を呼び出せば少し高度だけど可能。
SetTimerを使う。

2019/08/28(水) 18:08:37.69

>>797
Access外す所多いよ

2019/08/28(水) 20:25:09.97

DBと言われてaccessだと思う人って少ないよね

2019/08/28(水) 21:11:17.18

>>800
でしょうね
でも、DBのフロントエンドなら私としては断然ACCESS

office365に移行した会社増えてるみたいだから、実は使えるようになってますって人もいるんじゃないかな

2019/08/28(水) 21:14:21.69

DBと言えばaccessと思う人って多いよね

2019/08/28(水) 21:17:52.13

そしてデフォルトで使ってうっかり誤操作してデータ壊すまでが洗礼
トランザクションとは

2019/08/28(水) 22:59:00.75

DBZ

2019/08/28(水) 23:22:20.08

>>802
エンジンはWindowsに標準で入っているから
メモ帳があれば使えるDBだし

2019/08/28(水) 23:31:35.60

DBがOS標準で入ってるのは知ってるんだけど
じゃあaccessって何のために存在しているの

2019/08/28(水) 23:37:37.32

フォーム、レポート、クエリーエディタ
データシートビューでも連結テーブル表示できるし
Excelと違って型チェックする必要ないから楽だぞ

2019/08/28(水) 23:42:20.06

>>806
DBだけじゃないから

2019/08/29(木) 02:07:43.21

PowerQuery使えばいいのに

**796** (ﾜｯﾁｮｲ 619b-4hip) · 2019/08/29(木) 09:59:11.11

> Cells(1, 1).Interior.Color = vbRed　を
>
> Cells(1, 1).Font.Color = vbRed
>
> にすればちゃんと動くんだがなあ

と書いたが、Excel2007だとダメだな
やっぱFunctionプロシージャ内でセルの状態を変化させるのはご法度ということか

>>798
SetTimer調べてみたけど想像も付かん
良ければ教えて下さい

2019/08/29(木) 11:59:18.65

>>796
>>798
どういうこと？
具体的な例をにfunctionから貼ってくれ

2019/08/29(木) 14:14:31.26

>>784 >>810
Excel のユーザー定義関数の制限について
http://support.microsoft.com/kb/170787/ja

2019/08/30(金) 09:41:25.60

相互参照をアクティブにする方法はどうしますか？

book1でbook2のとあるセルの値を取得、
book1でその値を元に計算した結果のセルをbook2で参照して取得したいです。
両方のブックを開いてるとリアルタイムで連動しますがbook1を閉じるとbook2が連動しなくなってしまいます。

何とかしてbook2だけ開いてても動くにはどうすればよいですか。

2019/08/30(金) 09:56:38.22

csvから特定のカラムを取得してレポート用資料作成をしたいです
それぞれ値を取る際にFormatしたり、キーを取って別シートを参照して値取ったりしてますここらへんをfunctionで纏めて呼び出し元に返したいのですが、複数の意味ある値を返す際どういった形式にするのが良いのでしょうか
リストだと煩雑すぎますし利用する際に中のデータ構造を正しく理解する必要があるので辛いです

2019/08/30(金) 10:17:51.52

>>810-811
ちょっと余計なことを言ったかなと反省している。
SetTimerは使い方を間違えるととても危険なので、
仕組みが分からない人は使わないほうが良い。

Private Declare Function SetTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
Private rngTarget As Range
Public Function main()
Dim hWnd As Long
hWnd = Application.hWnd
Set rngTarget = ActiveCell
Call SetTimer(hWnd, 1, 100, AddressOf TimerProc)
End Function
Private Function TimerProc _
(ByVal hWnd As Long, _
ByVal msg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
Select Case wParam
Case 1
rngTarget.Value = ""
rngTarget.Interior.Color = vbRed
Call KillTimer(hWnd, 1)
End Select
End Function

2019/08/30(金) 13:25:34.95

>>815
おお素晴らしい

質問ですがTimerProcの引数はどこから渡されてるのでしょう？
ネットのサンプル見ると引数なしにしてるのが多いようですが

2019/08/30(金) 19:47:25.71

>>813
リンク更新じゃだめなん

2019/08/31(土) 09:16:24.02

>>817
相互参照だから開いてない方のbookの値が更新されないんです

【book1】
A1=book2.A1
A2=A1*2
【book2】
A1=1
A2=book1.A2

こんな感じになってて、book2だけ開いてる状態でA1を2にしたらA2が4になるようにしたいです。

2019/08/31(土) 09:24:05.84

片方のbook開いたら他方のbookも裏で開くようにするしか

2019/08/31(土) 09:39:57.73

VBAは奥が深くて面白いね
でもすでに同じ構文のVB6が終了して19年
いつまでExcel VBAは前世紀の言語仕様で戦い続けるんだろうね

2019/08/31(土) 09:56:21.35

西暦2100年そこにはいまだにVBA を書く社畜の姿が

2019/08/31(土) 10:01:54.51

>>819
こんな感じにデータベースファイルみたいなのが100個くらいのbookに分かれてるんですよね
それを1個のbookで参照・集計してアナライズしたいんですけど
自分で書いててアレなんですけどやっぱ明らかにexcelでやることじゃないんですよねーｗｗもうどうしようかしらん

2019/08/31(土) 12:03:18.46

>>820
デカくて潰せない会社と同じ
批判はあっても存続
他言語導入でも併存

他言語になっても、新たに覚え直す程の魅力がなければワザワザ移行しない
そんな言語があるとも思えない

2019/08/31(土) 13:10:01.15

もう既に基本的なことはpythonのopenpyxlで叩く方が楽だけどなｗ

2019/08/31(土) 13:27:16.71

pythonは入れだけでつらいから

2019/08/31(土) 13:54:41.11

>>818
まず、kore互いに参照し合う構成にすること自体が間違ってる
book2のA2はbook2のA1*2でいいんじゃないのか

2019/08/31(土) 13:56:14.97

Excelは互いに参照し合う構成にすること自体が間違ってる

2019/08/31(土) 14:33:22.19

>>827
book1に1日目のりんごとみかんの売り上げ個数が書いてある
book2に2日目のりんごとみかんの売り上げ個数が書いてある

book3で1日目と2日目のりんごとみかんの売り上げを表示したい、またbook3で書く売り上げに用いる消費税というパラメータを定義してある

こんな構成の時ってどうすればいいの？
今はbook1とbook2でbook3の消費税パラメータを参照して計算して、book3で計算結果を吸い上げてる

2019/08/31(土) 14:39:04.14

横からだが、その構成途中で値上がりしたときにやばくないか？

2019/08/31(土) 14:41:09.90

>>828
book3が最終形態ならbook1とbook2から個数を参照してbook3で料金計算

2019/08/31(土) 14:58:39.86

>>829
例えなので、実際は単価が上がることはなく、種類は100種類程度のデータです。
book3で全日付のデータ吸い上げて1日の売り上げ個数をbook3の別シートに仮保存して計算というのもしましたが、
日数分だけシートが増えてくので、。
欲しいのは売り上げ金額だけなので、無駄なデータは増やしたくないのですが・・

2019/08/31(土) 15:29:32.08

>>831
なら尚更構成見直した方がいいと思う
なんであっち見たりこっち見たりするんだ…

2019/08/31(土) 20:18:57.86

教えてください
ctrl+cでセルを値でコピーするのと同時に、セルか文字に色をつけたいのですがなかなか作れません
ヒントあれば教えて下さい

2019/08/31(土) 20:25:39.22

>>833
色だけコピーしたいと言ってんの？

2019/08/31(土) 20:38:21.36

>>834
いえ、コピーしたセルがわかるように色付けしたいです

2019/08/31(土) 20:44:10.45

>>833
マクロを
・色付
・コピー
にしてショートカットに登録

ctrl+cではなくそのショートカットでコピーする

2019/08/31(土) 20:51:57.67

>>832
どう見直しても解決出来そうになくて・・
そんなに特殊ケースでもないと思うのですが、世間の人々はどんな構造にしてるのでしょうか。

2019/08/31(土) 20:52:51.76

>>836
ありがとうございます。
ctrl+cでやるのは無理なんですか？

2019/08/31(土) 21:41:30.00

>>838
作ったマクロを ctrl+cに割り当ててみ

2019/08/31(土) 21:44:06.08

>>837
日報と同じファイルに単価とか共通の固定データ用のシートを1個作る
日報がどんなレイアウトか知らないが↓みたいなのと仮定する
　　　　　　8/1　8/2…
　りんご　
　みかん
sumifで1日分の個数を取得×Vlookupで単価取得
個数のファイル名はyyyymmddとかにして数式コピペできるようにしとく

2019/08/31(土) 21:46:45.55

日報て集計用のファイルな

2019/08/31(土) 23:13:48.46

>>833
死ね、クズ

2019/09/01(日) 02:37:04.86

>>842
すみません

2019/09/01(日) 04:01:41.09

コピーはCtrl+Cだけとは限らないからクリップボードを監視して
対象のシートがアクティブな間に状態が変わったら選択セルに色を付けるだけ
個人で使うだけなら適当でいいんじゃないの

2019/09/01(日) 13:44:49.90

Ctrl+Cでやりたいと言ってるんだから面倒なことしなくていいと思うが

2019/09/03(火) 17:37:57.38

A1を始点に行方向に5,列方向に4の間隔で格子状に存在するセルの先頭の文字だけを赤くしたいのですがどうやれば可能ですか？

2019/09/03(火) 17:50:49.52

>>846
Sub Macro1()
　　For r = 1 To 100 Step 5
　　　　For c = 1 To 100 Step 4
　　　　　　Cells(r, c).Characters(Start:=1, Length:=1).Font.Color = vbRed
　　Next c, r
End Sub

2019/09/03(火) 18:00:40.88

今日、Excelの表で手動でソートしてたら変な挙動が現れ、原因が分からず困ってしまい…

まず、「状況」列に、「完走」と「未達成」のデータがあるのですが、
この列を昇順ソートすると、「未達成」の上と下に「完走」が分かれてしまいました
もちろん降順ソートも同様です

で、この上下の「完走」を別のシートにコピペで貼り、挙動をチェックしてみましたが…
　https://i.imgur.com/FXwnn0h.jpg

上のリンクでも書いていますが、文字自体はif文でもフィルターでも同一扱いです
（ソートの時だけ、なぜか違いが出ています）

思い当たる違いは入力の仕方で、手打ちしたものとマクロ（VBA）で入力した違いがあります
マクロを作って以降はマクロで入力してますが、それ以前のデータは手打ち＆コピペで入力しました

とりあえずの対処法として、マクロで入力した文字を全てにコピペすればこの問題は解決しますが
今後のこともあり、なにか見落としている点や抜本的な対策がないか、
何かアドバイスを頂ければと思い質問しました。

なお、これは仕事とかじゃなくて趣味でやってるものです
あと、使ってるのは Excel2007 で、ちょっと古いバージョンです

以上、よろしくお願いします

2019/09/03(火) 18:37:10.54

>>848
ふりがな？

2019/09/03(火) 19:03:22.22

>>849
それでした！

「Excel、ソート、ふりがな」でググると出てきました
https://whiteleia.com/%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB-%E4%B8%A6%E3%81%B9%E6%9B%BF%E3%81%88-%E3%82%BD%E3%83%BC%E3%83%88-%E3%81%A7%E3%81%8D%E3%81%AA%E3%81%84-%E6%BC%A2%E5%AD%97/

Sortオブジェクトで並べ替えを実行する
http://officetanaka.net/excel/vba/tips/tips148c.htm

いろいろ見てみると、Excelでソートをする際の（隠れた？）重要事項のようですね
（見えない「ふりがな」で漢字も正しくソート！、親切な機能ですが…やめてくれ～！！）

2019/09/03(火) 19:08:44.32

>>850
大を「おおきい」で変換するか「だい」で変換するかで位置が変わるのと同じやね

う
え
大 (おお)
か
き
大 (だい)

2019/09/03(火) 19:09:53.69

シュワルツ変換じゃないんだな
やばいね

2019/09/03(火) 19:30:59.43

>>850
デフォで ONなのがあかんよね

2019/09/04(水) 03:28:35.08

ふりがなは集計とかで計算が合わない原因の1つ
非常にヤバイ

2019/09/04(水) 04:06:51.76

俺ならorder byでソートするわ。

2019/09/04(水) 04:06:51.76

俺ならorder byでソートするわ。

2019/09/04(水) 04:10:04.07

あ、勝手に2回書き込まれた。
2回押したわけじゃない。(時間が同じ)

2019/09/04(水) 06:58:53.39

同時にマルチポストするスクリプトのテストなんでしょ？わたししってます

2019/09/04(水) 09:41:13.02

それ何の意味があるの?

2019/09/04(水) 21:10:47.96

utf8でテキストファイルを読み書きするにはADODB.Stream使うしか無いんですか？

2019/09/04(水) 22:19:41.54

>>860
お前にはできないだけ

2019/09/04(水) 22:43:43.48

>>860
テキスト取り込み機能の、文字コードの選択肢に、
普通にUTF-8入ってるけど。

2019/09/05(木) 05:41:49.26

>>862
詳しく

2019/09/05(木) 19:27:53.95

>>863
データ → テキストまたはCSVから → テキストファイル選択 → インポート → 元のファイル「65001:Unicode (UTF-8)」

バージョンによって、多少表記は違うと思うけど。

2019/09/05(木) 20:11:50.19

>>864
ありがと、試してみる。

utf8でファイルへ書き出す方法はある？
ADODB.Stream以外で。

2019/09/05(木) 21:14:36.97

>>865
なぜDODB.Streamを避ける？参照設定ならVBAで追加できるぞ

2019/09/05(木) 21:28:31.64

>>866
ゴメン、それをここで議論するつもりは無いんだ。
utf8でテキストファイルを読み書きするにはADODB.Stream使うしか無いのか知りたいだけ。

2019/09/05(木) 21:35:39.87

言い直す。
utf8でテキストファイルを読み書きするのにADODB.Stream以外の方法があれば知りたい。

2019/09/05(木) 21:37:45.62

言い直す。
utf8でテキストファイルを読み書きするのにADODB.Stream以外の方法があれば知りたい。

2019/09/05(木) 21:39:34.16

大事なことなので

2019/09/05(木) 21:47:14.25

こだわりって奴か

2019/09/05(木) 22:05:29.23

どんな方法でもいいのなら、文字コード変換プロシージャを自作すればいい
変換テーブルはネットに落ちてる

**デフォルトの名無しさん** (ｽﾌｯ Sd62-fUZA) · 2019/09/05(木) 22:10:54.25

>>868
APIで変換できる
ADODB.streamも内部では同じAPIを呼んでるはず
http://www.t-net.ne.jp/~cyfis/win_api/sdk/MultiByteToWideChar.html

2019/09/05(木) 22:29:23.75

.netのライブラリ呼び出すとか？
ADODBを避けたい理由がわからんけど

2019/09/05(木) 22:37:00.77

違う視点で調べてみるとか、
UTF-8じゃなくて、中国語をどうにかしたい、とかさ。

2019/09/06(金) 01:25:28.43

>>868
方法はいくつもある
無料のWebAPIもあるし、コマンドラインでnkfを呼び出したり、テキストボックスのプロパティをいじったり
あとは理由、目的、条件などをはっきりさせてくれないと話の続けようがない

2019/09/06(金) 09:13:01.42

参照とかAPIとか事務のおばちゃんには難しいのでは？
VBAを使う理由って何もさせてくれない設定だったよね？

2019/09/06(金) 09:20:07.41

何設定って
ぼく働いたことないから実態わかりませんてこと？

2019/09/06(金) 12:46:48.43

・2列の一致する組み合わせを数える方法(ex.A列に野菜B列に果物が書かれている時AがトマトかつBがりんごの行をカウントしたい)

・数式の結果の文字の先頭を色付けする方法(ex.A1セルに【=IF(条件式,"☆優先","▽後回し")】が入力されていてその結果の文字の先頭の記号だけを色付けしたい)

この2つのやり方教えてください。

2019/09/06(金) 13:17:28.93

>>879
対象範囲をForで回して評価します。
式で表現されたセルの部分文字列の書式を違えることは出来ないんじゃ？

2019/09/06(金) 13:22:25.08

>>879
最初の方は何をしたいのか意味が分からない。
トマトが野菜かどうかはどうやって判定するの?

2番目の方は数式の結果はそのセルのValueで取れるから数式関係無くね?
VBAでやりたいのか数式でやりたいのかも不明。

2019/09/06(金) 13:24:03.74

馬鹿の書いた仕様にマジレスか

2019/09/06(金) 17:23:07.64

>>881
トマトが野菜かは関係無いだろ

2019/09/06(金) 17:29:43.22

for each c in [a2:a20]
if c.value & c.offset(,1).value ="トマトりんご" then cnt =cnt + 1
next
msgbox "うんこ"

2019/09/06(金) 17:45:39.03

トウモロコシは野菜？

2019/09/06(金) 18:20:35.01

トマトは野菜だがフルーツトマトはフルーツである。○か×か？

2019/09/06(金) 18:51:20.65

2019/09/06(金) 21:07:32.81

オーノー

**デフォルトの名無しさん** (ｽﾌｯ Sd62-TE+O) · 2019/09/06(金) 21:47:07.49

>>881
このスレにわざわざ書き込んでるんだからさすがにVBAでやりたいんだろうと思うけど。

2019/09/06(金) 22:15:50.48

>>879
一つ目は数式でできるがVBAでないと駄目なのか？

2019/09/06(金) 22:33:33.84

質問ですが、ExcelのワークシートとVBAとでは、日付の扱い（起点？）が異なるのでしょうか？

例えば、数字の「５」をDate型で表示すると、
　・ワークシート：　1900/01/05
　・VBA　　　　　：　1900/01/04
　https://i.imgur.com/8Iq99R4.jpg

このように１日ズレますが、これはどういう事なのか…、これで合ってるのでしょうか？
（Excelのバージョンは2007です）

何かアドバイスを頂けると幸いです

2019/09/06(金) 22:47:22.58

https://www.tipsfound.com/vba/05cdate
CDate
値に数値を指定したときは、日付型の初期値 1899/12/30 0:0:0 にその数値の日を足した日付に変換します

https://www.officepro.jp/excelfunc/date/index0.html
日付のシリアル値は、1900年1月1日が基準日となりシリアル値は「1」となります。

らしいぞ