Excel VBA 質問スレ Part63

2019/09/16(月) 19:34:59.04

!extend:checked:vvvvv:1000:512

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

※前スレ
Excel VBA 質問スレ Part62
https://mevius.5ch.net/test/read.cgi/tech/1561303297/

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

2019/11/17(日) 11:52:40.84

いつの話だよ
Web系のAtomユーザーはほとんどVSCodeに乗り換えたよ

2019/11/17(日) 12:25:47.38

>>707
エクセル列の表示を数字とアルファベットで切り替える
https://www.tipsfound.com/excel/01204

2019/11/17(日) 12:31:12.53

>>707
言ってることがよくわからんけど↓みたいにVBEでコンソール出力するのは駄目なの？
?Selection.Item(1).Row & ", " & Selection.Item(1).Column

微糖 (ﾜｯﾁｮｲ 2eda-zGDE) · 2019/11/17(日) 12:46:43.50

いろいろ提案ありがとうございます。
普通に対応表を手元においときます。

2019/11/17(日) 13:14:49.52

>>707
A1とR1C1表示を切り替えられるショートカット作った

2019/11/17(日) 13:37:03.64

まれに禿もいるよ

2019/11/17(日) 14:08:51.89

>>707
1行目に連番を振っておく

2019/11/17(日) 15:12:07.26

>>707
機能はいらんかな。見たくなったらcolumn()入れてる

2019/11/17(日) 18:02:42.14

北海道のグラサンの禿はここにきてないの？

2019/11/17(日) 19:02:54.92

俺も=Column()入れてるわ。
ていうかね、何でどっちか片方だけなんだよ。
A(1)、B(2)とか両方表示するモード付けりゃいいのに。

2019/11/17(日) 19:28:55.74

>>717
おーそれいいね
ややこしかったらツールチップみたいに、ポイントした時に出てくるとかでも良い

2019/11/17(日) 19:33:09.97

現状自作するしかないわな

2019/11/17(日) 19:38:26.25

むしろ列番号が必要なことって少なくね？

2019/11/17(日) 19:43:53.48

indirectやVBAで古いデータを整備する時にたまに必要になる

**デフォルトの名無しさん** (JP 0H92-iGNt) · 2019/11/17(日) 22:37:01.66

1行目1列目にランダムに数字が入力されている表において、その数字を上から順番に足して2列目にその合計を表示させるにはどう書けばいいでしょうか？（下みたいな感じで）

1列目　2列目
--------------------
4　　　　　4
7 　　　　11
8 　　　　19
12　　　　31
15　　　　46
2　　　　48

2019/11/17(日) 22:40:14.20

上から順番に足して2列目にその合計を表示させればいいですよ

2019/11/17(日) 22:55:50.76

>>722
ワークシート関数じゃ駄目なの？

2019/11/17(日) 23:11:53.16

>>722
左のセル+上のセル

2019/11/17(日) 23:14:14.11

A2から対象行までを合計

2019/11/18(月) 00:38:18.28

for　i =　1to10
v=v＋cel（i,1）
cel （i,2）=v
next
みたいな感じ?

2019/11/18(月) 00:45:00.05

なんだcelて
ちゃんと書けボケナス

2019/11/18(月) 01:27:00.60

>>722
馬鹿は死ねよ

2019/11/18(月) 04:45:54.36

Ruby で作った

nums = <<"EOT".lines( chomp: true ).map( &:to_i ) # 数字の配列。改行は削除
4
7
8
12
15
2
EOT

totals = nums.each_with_object( [ 0 ] ) {
|num, ary| ary.push( ary.last + num ) }

totals.shift # 先頭要素の0 を削除する
p totals #=> [4, 11, 19, 31, 46, 48]

2019/11/18(月) 15:14:40.58

>>722
Sub sample1()
Dim i Range
Range("B1") = Range("A1")
For i = 2 To 6
Cells(i, 2) = Cells(i - 1, 2) + Cells(i, 1)
Next i
end sub

A列が不特定多数で出力の速さを求めるのなら

Sub sample2()
Dim i Range,j Range
j = WorksheetFunction.Count(Range("A:A"))
Application.ScreenUpdating = False
Range("B1") = Range("A1")
For i = 2 To j
Cells(i, 2) = Cells(i - 1, 2) + Cells(i, 1)
Next i
Application.ScreenUpdating = True
end sub

2019/11/18(月) 15:19:20.54

間違えた

Sub sample1()
Dim i As Long
Range("B1") = Range("A1")
For i = 2 To 6
Cells(i, 2) = Cells(i - 1, 2) + Cells(i, 1)
Next i
end sub

A列が不特定多数で出力の速さを求めるのなら

Sub sample2()
Dim i As Long,j As Long
j = WorksheetFunction.Count(Range("A:A"))
Application.ScreenUpdating = False
Range("B1") = Range("A1")
For i = 2 To j
Cells(i, 2) = Cells(i - 1, 2) + Cells(i, 1)
Next i
Application.ScreenUpdating = True
end sub

2019/11/18(月) 15:42:28.46

>>707
VBEのメニュー上にテキストボックス作って数値とアルファベット相互変換する奴作れば。

2019/11/18(月) 15:43:19.48

>>720
寧ろ列番号の方が必要。

2019/11/18(月) 15:53:04.73

>>722
Range("B1:B10").Value="=SUM($A$1:$A1)"
'関数残したくなければ下を追加
Range("B1:B10").Value=Range("B1:B10").Value

2019/11/18(月) 17:07:56.73

>>734
どんなときに必要なの？

2019/11/18(月) 18:10:29.63

マクロ実行ブックと同じフォルダ内にある"CCT"というブックに
１つのシートがあって、そのシートをマクロ実行ブックの"CCT1"に貼り付けたいのですが
"CCT"内のシート名が毎回変わってしまうので、変わってもコピペができるマクロをおしえてください

Workbooks.Open ThisWorkbook.Path & "\CCT", ReadOnly:=True
Sheets("*").Cells.Copy ThisWorkbook.Sheets("CCT1").[A1]
ActiveWindow.Close savechanges:=False

2019/11/18(月) 18:25:23.23

>>736
普通はCells(i, j)を使うから

2019/11/18(月) 18:33:30.81

>>737
Worksheets(1)じゃ駄目なん?

2019/11/18(月) 19:06:26.91

>>738
まさかと思うけど、F1~X1のセルの処理を
For C = 6 To 24 ' F列からX列
~.Cells(1, C).~
Next
みたいにコーディングしてるの？

2019/11/18(月) 19:14:56.88

vbaを勉強してるのですが、specialcellsとusedrangeの違いが分からないのですが、どんな時に使い分けるのですか？

2019/11/18(月) 19:45:04.18

どっちも普段使わない

2019/11/18(月) 19:45:09.69

>>740
そんなものは内容による。
Range("C1")のような表現よりも数学的、座標的表現であるCellsの方を使うことの方が多いと言ってる。

2019/11/18(月) 19:46:05.14

>>737
シート名かシートの順番を固定しないと無理

2019/11/18(月) 19:49:12.48

>>741
specialcellsは選択範囲から取得される
usedrangeは選択範囲関係なくシート全体から取得される

2019/11/18(月) 19:50:38.34

>>744
シート1つしか無いと思う。
文が曖昧だから必ずそうとは言えないが。

2019/11/18(月) 19:55:46.17

>>743
お前がそうだというだけの話なのね

2019/11/18(月) 19:56:31.57

>>747
そうだよ。

2019/11/18(月) 19:58:05.76

今までの経験上、組める奴は同意見が多いけどね。

2019/11/18(月) 20:05:00.70

>>740
列方向だけに伸ばす事は少ないけど、

for k = 1 to 3
for i = 1 to 最終行
cells(i,k)
next
next

みたいに二重ループにすることはたまによくあるよ

2019/11/18(月) 20:05:54.63

>>737
シートが1枚しか無いならブック開いてからアクティブシート取得すればいいのでは

**デフォルトの名無しさん** (ｽﾌｯ Sda2-LOTu) · 2019/11/18(月) 20:08:35.28

Set

2019/11/18(月) 20:22:40.48

>>748
ならなんの問題もないな

>>749
お前の周りの話だよね？

>>750
いや、その例で
for k = 1 to 3 ' A列~C列
って書いてるってことね

2019/11/18(月) 21:22:26.89

>>753
俺の周りって結構広いんで。

2019/11/18(月) 21:40:37.27

Rubyなら一行で書ける程度の事をVBAは面倒だね

2019/11/18(月) 22:02:21.62

>>754
はいはいw

2019/11/18(月) 22:39:37.88

俺はCells派
任意のセル範囲内での位置座標を相対指定できてめちゃくちゃ便利
Cells(i)もCells(i, j)もよくつかうしSelection.Cells～とかよくやる

2019/11/18(月) 23:25:47.41

>>728
携帯で打ってるのに無理ゆうなよ。

2019/11/19(火) 06:07:21.04

>>754
ウエストな

2019/11/19(火) 09:42:21.51

>>754
カエルかな？

2019/11/19(火) 12:42:00.23

>>741
SpeciallCellsのほうはユーザー定義関数で使うと不具合あったはず

2019/11/19(火) 22:43:20.17

>>740
RangeをVariantに代入して
For c = 1 To Ubound(Variant)
Variant(1,c)...
が一番楽
だけどRangeが1セルだけの場合上手く代入できない気がする

2019/11/20(水) 13:27:25.20

>>762
>>740
いや、だからさ、>>735みたいにしたりするし、その時によるんだよ。
行、列共に多少なりとも組める奴ならアルファベットでアクセスすることは少ないというだけ。

2019/11/21(木) 15:35:37.98

http://officetanaka.net/excel/vba/tips/tips38.htm

Sub Sample1()
Dim RE, strPattern As String, r As Range
Set RE = CreateObject("VBScript.RegExp")
strPattern = "SUM\("
With RE
.Pattern = strPattern ''検索パターンを設定
.IgnoreCase = True ''大文字と小文字を区別しない
.Global = True ''文字列全体を検索
For Each r In ActiveSheet.UsedRange
If .Test(r.Formula) Then r.Interior.ColorIndex = 3
Next r
End With
Set RE = Nothing
End Sub
このソースのDim REの部分ってこのサブルーチンだけを動かすようなマクロの場合いらなくね？

2019/11/21(木) 15:37:08.52

>>764
間違えたdimでRE変数宣言するのいらなくない？
それとは別の話でset RE nothingはこのサブルーチンしか動かさない場合は必要なくない？

2019/11/21(木) 16:45:06.69

>>765
変数そのものがなくても良いって話なのか、宣言がいらないって話なのか？
まあどっちもやりようによってイエスだが、それが推奨されるかは別の話

2019/11/21(木) 17:16:22.54

>>766
プログラム的に何か考慮してるのか聞きたい
プロジェクトでどうするべきか見たいなところは興味ない

2019/11/21(木) 17:22:58.39

vbaの設計的にはSet RE = CreateObject("VBScript.RegExp") これだけで初期化できてるからdimで宣言する必要があるのかコンピューターサイエンスサイドの意味で聞きたい

2019/11/21(木) 23:25:18.32

普通は変数宣言を強制するようになってるだろ。

2019/11/22(金) 00:36:07.32

スモリートインハゲラクター

2019/11/22(金) 01:53:36.59

>>768
モジュールレベルで変数宣言を強制している場合にはプロシージャ内のDimで宣言していないとコンパイルエラーになる
型指定なしのDim宣言ってのは実際にはVariant型変数の宣言なので、代入時に型評価をしているだけだけどね
RegExp型のメモリ領域をDim宣言時に確保するとなると参照設定が必要でそれはそれでまた別の問題が生じる

2019/11/22(金) 11:54:02.74

クラスモジュールを使ってオブジェクト指向(厳密には違うらしいですが…)を理解したいと思っています
Newでオブジェクト生成して、get set letを使いながらプログラムを書くという認識で合っているでしょうか？

2019/11/22(金) 12:01:14.10

>>771
モジュールレベルで変数宣言を強制している場合には必要なのね、ありがとう良くわかった。
set RE = nothingなんだけどこのサブルーチン実行完了したらメモリとか解放されるからnothingやらなくてもスタック領域占有しないと思ってるんだけど実際のところどうなの？そもそも"VBScript.RegExp"の占有ってスタック領域であってる？
そもそもVBAのこの

2019/11/22(金) 12:14:16.35

発達障害か何かか？

2019/11/22(金) 12:16:14.12

>>774
教えてよ知ってるなら質問してたからこれも

2019/11/22(金) 12:19:40.92

実際に正常終了する前提ならset RE = nothingしなくてもメモリリーク起きないのか知りたい

2019/11/22(金) 12:25:56.88

自分で検証して納得しなさいよ

2019/11/22(金) 12:44:22.42

>>777
やるけど"VBScript.RegExp"の占有領域はスタックかどうかだけ教えて欲しい
スタックメモリだと思ってるんだけど

2019/11/22(金) 12:55:31.14

>>774
相手するなよ…

2019/11/22(金) 13:00:58.08

>>779
いやここVBA質問スレだよね？
その姿勢は本分を果たしてなくないか

2019/11/22(金) 13:03:14.92

アインシュタイン「6歳の子供に説明できなければ、理解したとは言えない。」

2019/11/22(金) 15:54:34.05

6歳って小1とかだよな？
小1が5chか・・・

2019/11/22(金) 16:07:33.42

名探偵かよ

2019/11/22(金) 16:59:48.42

>>782
小1がやってもいいだろ
例えだよね？

2019/11/22(金) 17:00:31.99

お前ら答えられないからって逃げすぎw

2019/11/22(金) 18:58:21.55

CreateObject("VBScript.RegExp")

VBScript のオブジェクトを作っているのだろ。
別のプロセスか、DLL から作っているのだろ

とても、スタックとは思えない

2019/11/22(金) 19:08:12.45

そもそも占有領域とはなにを指しているのか
VBAのローカル変数はスタックにとられる
それ以上はそのオブジェクト次第

2019/11/23(土) 09:54:08.90

>>773
VBScriptの正規表現オブジェクトはIE付属のエンジンだからヒープにインスタンス作成してるだろうし、参照カウンタ0になったら自動破棄されてるんじゃないの
このサブルーチンからの参照がなくなっても別の参照が発生してたらインスタンスは残るでしょ

だから、このサブルーチンが正常終了するかどうかとメモリリークが起きるかどうかは別問題だと思う
サブルーチン外に制御が渡った段階でこの正規表現オブジェクトがどこかのプロセスによって参照されていて、そっちの制御フローの中に猛烈なメモリ負荷が発生する処理が存在すれば、メモリリークの原因になりうる
execループで空文字をマッチさせていてindexが進まずに無限ループしたりとかね

それと、あなたの認識通り、End Subでスタック内のローカル変数のアドレス空間自体が解放されるので、End Subの直前にこの変数SEにNothingを代入する理由はない
モジュールレベルの変数やグローバル変数を使う場合は逆に、制御フロー上の要求としてサブルーチン終了時に参照アドレスをクリアしなければならない場合もある

2019/11/23(土) 10:41:17.70

Windows10で、SetLayeredWindowAttributesのLWA_COLORKEYを正常動作させる方法ある?
Windows7でやる方法は知ってるので、7の場合の回答は不要。

2019/11/23(土) 13:40:54.04

with cells(i,j)
.cut .offset(1,0)
.value=k
end with

cells(i,j)にkが入らず、cells(i+1,j)にkが入るんだけど仕様？

2019/11/23(土) 13:47:27.73

>>790
普通に

Cells(i, j).Value = k

では駄目なのか？

2019/11/23(土) 14:19:57.78

>>790
cut貼り付けでセルそのものが移動してるから

2019/11/23(土) 14:44:54.38

ストリートインハゲラクターはここに来ないの？

**デフォルトの名無しさん** (ｽﾌｯ Sd33-cCMV) · 2019/11/23(土) 17:04:16.03

>>791
>>792
仕様なんですね
ありがとうございます

2019/11/23(土) 17:11:07.86

仕様というか、代入先を自分で変えてることは伝わってるんだろうか

2019/11/23(土) 19:56:27.67

>>790
仕様ではなくて君のコードの動作の問題
同じ綴りのオブジェクト式の記述をまとめる文法ルールとしてWithを理解するのは大きな間違い

Withで任意のセルを取得して和のセルに貼り付けたらEnd Withまでずっと貼り付けられた先のセルを参照する
その状態で.Valueに値を代入したら、当然貼り付けられたセルの値が変わる

With Cells(i, j)
.Cut .Offset(1,0)
End With
Cells(i, j).Value = k

とすれば何の問題もない

2019/11/23(土) 20:39:38.41

>>795
わかってて聞いてるんでしょ
わかってなきゃわざわざ「仕様？」なんて聞き方しないだろうし

With で参照してるセルを変更した時の挙動を書いてるドキュメントは見たことないから仕様かどうかはよくわからん
誰か見たことある人いる？

2019/11/23(土) 22:14:08.35

アンカー間違えてた
>>796は>>794宛ね

2019/11/23(土) 22:28:15.56

>>790
仕様
cut後もcells()で返ってくるオブジェクトは同じ
そういや上書きされるcells()はどうなるのか、と試してみたらnothingになっていた
この辺はExcelのカットアンドペーストなどでおなじみだけど、いざやってみると不思議な気分だ

Sub foo()
i = 2
j = 3
k = "k-"
Set before = Cells(3, 3)
With Cells(i, j)
.Cut .Offset(1, 0)
.Value = k
End With
Debug.Print before.Value
End Sub

2019/11/23(土) 22:52:50.40

>>799
不思議でも何でもない
変数によるオブジェクト参照の仕組みをもっと正確に理解すべきだろ
カットは貼り付け先のセルの削除と貼り付け元のセルの埋め込みを伴う処理だぞ
貼り付けを行った段階で、変数のスタックに格納されていた貼り付け先のセルのポインタが無効になるので、貼り付け後に変数の中身を評価するとNothingになるだけ
セルの仕様ではなくオブジェクト参照の仕組みからして当然

2019/11/24(日) 00:00:31.22

しかし、VBAをやり始めたら仕事がはかどるな
面倒くさい勤務表の処理もマクロで一発やからな

2019/11/24(日) 02:13:33.02

まるで働いているみたいな言い方だな

2019/11/24(日) 02:19:56.28

VBAで劇的に工数削減できるような単価の低そうな仕事をしているのなら、
今の仕事の効率化なんかよりプログラミングに習熟してITエンジニアに転職したほうが金を稼げるのではないだろうか

2019/11/24(日) 02:30:35.85

>>803
そのやりかたは？
フリーランサーになればたくさん稼げる？
在宅ワークも可？

2019/11/24(日) 11:11:52.55

>>804
いかがでしたでしょうか？

2019/11/24(日) 11:24:51.53

>>803>>804
ここで回答するのもアレだが
VBAは基本的に常駐で保守のゴツイ仕事のみ。仕事自体が少なく稼げない。稼ぐなら別のプログラム言語の方が良い。
在宅の案件は更に無い。ゼロと思ったほうが良い（web業者がついでにAccessのVBAをメンテする、といった事例は聞いたことがある）

2019/11/24(日) 11:58:45.04

VBAはマクロ記録も出来るし、やりたいことをちょいググればいくらでもサンプルコード載ってるからね