Excel VBA 質問スレ Part73

2021/09/13(月) 07:29:59.47

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑同じ内容を2行貼り付けるナリ

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

※前スレ
Excel VBA 質問スレ Part70
https://mevius.5ch.net/test/read.cgi/tech/1616072923/
Excel VBA 質問スレ Part71
https://mevius.5ch.net/test/read.cgi/tech/1621914481/

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

2021/09/13(月) 08:13:09.52

>>1 乙

2021/09/13(月) 19:11:08.29

いつものあいつの釣りとあまりに馬鹿過ぎる質問はお断りです
また、Excelの使い方すらわからない人はお帰りください

2021/09/13(月) 21:24:22.31

それでも来たらスルーでいいんじゃね

2021/09/14(火) 16:24:06.98

A列の全行のセルの値を配列に突っ込む時、オートフィルターが複数列でかかっていると、
表示されてるやつだけ配列に入ってしまうの、なんとかならない？

配列をA列に縦方向に貼り付ける時も、オートフィルターが既にかかってると、適切に元の行に貼り付け戻せない。
仕様なのかな

5 (ﾜｯﾁｮｲ e6bb-gYTk) · 2021/09/14(火) 16:26:32.34

2万行あるから、なるべく最初にa列の内容を全部配列に入れてから、それぞれを操作して、
それからまた修正済みの配列の中身をa列に上書き貼り付けしたい。
でも、オートフィルターがかかっているとまずい。
複数列でオートフィルターがかかっているととても難しい

2021/09/14(火) 16:40:04.06

フィルタ全解除する処理を前に置けばいいじゃない

5 (ﾜｯﾁｮｲ e6bb-gYTk) · 2021/09/14(火) 17:37:49.57

全解除して処理を行う、というのはできたけど、ユーザーのためにフィルタした状態ってのはそのままに戻したい。
フィルタしてる列が単体ならなんとかそれはできた。
だけど、複数列でフィルタした状態を、いったん全解除して、そんであとでまた元のフィルタ選択状態に戻すのが出来なさそうなのよね。

2021/09/14(火) 19:45:59.21

テーブルの見出しはvalueじゃ取り出せないんだな
勉強になったわ

2021/09/14(火) 20:24:42.51

フィルタは全解除するのが一番手っ取り早い
横に伸ばすのは異常に難易度高い

2021/09/14(火) 20:25:17.71

列の増加が多いなら最初から大量にフィルタ列を用意シておいたほうが良い

2021/09/14(火) 21:12:31.57

>>8
フィルタの条件って色々指定できる(色とか日付とか特定の文字列を含むとか)から、全部に対応しようとするとすごくめんどくさい
https://excel-ubara.com/vba_class/VBA_CLASS_08.html

割り切りとして「このマクロを使ったらフィルタは解除されますが情報は最新化されます」くらいにしとくのが無難かと

**デフォルトの名無しさん** (JP 0Hc9-dGLa) · 2021/09/14(火) 21:57:16.30

作業シートにコピペしてから処理すれば？

2021/09/14(火) 23:44:27.27

コマンドボタンを押してユーザーフォームを開くときに、
TopとLeftの位置をセルC5のTopとLeftの位置に合わせるべく、
以下のようにコードを記述したのですがうまく合ってくれません。
※セルC5のLeftやTop位置より大分左や上に表示されてしまう。

何か記述的におかしいのでしょうか？

Sub UserFormOpenSet()
Dim varTop, varLeft
With Sheets("説明シート").Range("C5")
varTop = .Top
varLeft = .Left
End With
With UserForm
.Show
.Top = varTop
.Left = varLeft
.Label1.Caption = "コメント内容"
　　　　・
・
・
End With
End Sub

2021/09/14(火) 23:56:12.55

>>14
UserForm top left でググッたらこんなページがhttps://www.officepro.jp/excelvbaform/form_form/index4.html
検証してないけど試してみたらどうですかね

2021/09/15(水) 00:23:15.17

>>15
UserForm top left　セルの位置　でググったら
完璧なTipsがありました！
検索のヒントをありがとうございました！

2021/09/15(水) 00:48:53.61

どーでも良い質問なんだが、VBAからエクセルのセルに値を入れるときの表現について
意見が割れてるので、みんながどー表現してるか知りたい

例えばVBAで A1セルに 123 という値を入れるとき、
A1セルに 123 を出力するという表現を使うか、A1セルに 123 を入力するという表現を使うか、どっち？

仕様書で人によって同じことしてるのに出力となってたり入力となってたりで色々バラバラすぎる

**デフォルトの名無しさん** (ｽﾌｯ Sd0a-dGLa) · 2021/09/15(水) 01:03:00.63

>>17
VBAで計算や処理した結果をワークシートに保存するだけ、セルに値を入れて終わりの場合は出力
シートに値をセットしたあと、さらにその値を使って数式やグラフなどで別の処理や加工をする場合は入力

2021/09/15(水) 07:18:17.38

>>17
それは一律で入力じゃないのか
代入って言うぐらいだし

「出力」はエクセルがrangeのvalueを出力してるだけでは？

2021/09/15(水) 07:51:35.00

VBAの関数、メソッドとしてそのデータが入力値なのか出力値なのかは明白なのでは？

2021/09/15(水) 10:54:35.14

>>17
VBAがセルに出力
ユーザーがセルに入力

2021/09/15(水) 12:02:19.64

お知恵をお貸し下さい

String型の変数に文章を代入しています
その変数が「は」を助詞として使用しているかを検出し、その「は」が何文字目に記載されているかを抽出したいです
判定式はどの様に組むのが良いでしょうか

2021/09/15(水) 12:16:58.05

>>22
これが使えるんじゃないかな。
https://qiita.com/mima_ita/items/bc2aeb060ee12d280d7b

2021/09/15(水) 12:26:39.15

こちらの下の方に具体的に品詞の判定方法がある。
http://www.excel.studio-kazu.jp/kw/20201108202429.html

2021/09/15(水) 14:02:58.42

是非とも
「はははははは、ははのはははははははと笑う」で
挙動確認を行なって欲しい案件だな

2021/09/15(水) 16:06:38.48

>>17
> A1セルに 123 を出力するという表現を使うか、A1セルに 123 を入力するという表現を使うか、どっち？
俺なら「A1セルに 123 を書き込む」かな
シートはストレージみたいなものなのと思ってるので出力とか入力って言うのはなんか違和感ある

2021/09/15(水) 16:59:13.14

VBA側を主体に書いているなら(シートに)出力
シート側を主体にかいてるなら(VBAから)入力
だな

VBAの仕様書でVBA側を主体にしないのはどうかと思うが
（マクロ付き）エクセルの仕様書だと考えればまあ入力でもいいのかも

2021/09/15(水) 23:30:54.86

985なのですが、
ttps://whois.nic.ad.jp/cgi-bin/whois_gw?key=182.22.16.251
から
b. [ネットワーク名] YAHOO-NET
をVBAで抽出するいい方法はあるでしょうか？

**デフォルトの名無しさん** (JP 0Hc9-dGLa) · 2021/09/16(木) 00:13:25.85

>>28
Sub ネットワーク名取得()
　　With CreateObject("MSXML2.XMLHTTP")
　　　　.Open "GET", "https://whois.nic.ad.jp/cgi-bin/whois_gw?key=182.22.16.251";, False
　　　　.Send
　　　　For Each s In Split(.responseText, vbLf)
　　　　　　If InStr(s, "ネットワーク名") Then Debug.Print s
　　　　Next
　　End With
End Sub

2021/09/16(木) 00:59:42.68

一生使うことのなさそうなCreateObjectだな

2021/09/16(木) 01:49:26.07

>>28
shift-jis の古いサーバーっぽい。
Ruby で作った

require 'open-uri'
require 'nokogiri'

url = "ここへURL"
doc = Nokogiri::HTML( open( url ) )

elements = doc.css( "pre" )
text = elements.first.content # 1つ目のpreの内容

result = "エラー！該当なし"
text.each_line( chomp: true ) do | line | # 改行を除去して、各行を処理する
if line.start_with? "b. [ネットワーク名]" # 行頭が何々で始まっていれば
result = line
break
end
end

puts result

出力
b. [ネットワーク名] YAHOO-NET

2021/09/16(木) 01:51:47.59

なぜRuby

2021/09/16(木) 02:01:13.02

スレタイも質問も読めないRubyバカ

2021/09/16(木) 06:38:12.58

利用者数激減の言語
それがruby

2021/09/16(木) 08:25:21.24

>>32-34
相手すんなよ…

2021/09/16(木) 08:25:49.50

>>17
なるほど仕様書の問題か
ならば現場内で紛らわしくないような記載のルールを作ればいいかな。
例えばユーザーが手打ちで設定する場合は「入力」
VBAで書き込む場合は「出力」または「設定」

コード上では「取得」と「設定」とかに分かれるからあまり問題ないだろうけど、仕様書の記載はまた別のセンスが必要になるからね。
一見して分かりやすく曲解のない仕様書を作るときって日本語の難しさを痛感することあるよね。

そういう時は大まかな流れ図みたいなもの書いて添付して紛らわしさを回避するのも一つの手だよ。

2021/09/16(木) 11:33:48.95

質問への回答ありがとう
>>17の質問者です

個人的には「人が入力」、「VBAから出力」というとらえ方がしっくりきました

紛らわしくない書き方のルールを作るというのは本当にその通りで、
これは実践したい

日本語は改めて考えると主体が曖昧で難しい…

**デフォルトの名無しさん** (JP 0Hc9-dGLa) · 2021/09/16(木) 12:13:09.55

>>37
日本語の文章ってのは、主語が存在しなくても成立する性質があるんだよ
おかげでどっち視点かわからなくなる現象が起きやすい

逆に英文法では主語がほぼ省略できない
だから単語を置き換えただけの翻訳は不自然な文章になる

2021/09/16(木) 13:11:41.46

グッジョブ！俺

2021/09/16(木) 14:54:30.21

>>38
馬鹿の典型例

2021/09/16(木) 17:31:50.83

質問させてください。

ウェブサイトから特定の場所の情報を抽出するのはこちらのVBAを使用するということで間違いないでしょうか？

またこれはExcelを購入することによって使用できるもので、WindowsでもMacでも使用できるのでしょうか？

現在Macを使用しておりまして、具体的に申し上げるとyahooファイナンスなどの個別銘柄ページから配当利回りなどの情報をまとめて抽出したいと考えております。

スレ違いでしたら申し訳ございませんが、検索してもよく分からなかったので、書き込み数が多いこちらで質問させていただきました。

よろしくお願い申し上げます。

2021/09/16(木) 17:42:26.31

俺が馬鹿なだけかもしれねぇけど何をしたいのかがさっぱり分からない

2021/09/16(木) 18:02:43.18

スクレイピングしたいんだろ
そのデータをどう扱うかによるわな

2021/09/16(木) 18:31:48.90

というかMAC版のEXCELってVBA使えるの？
あとスクレイピング用のコンポーネントも

2021/09/16(木) 18:41:42.01

他のツールでデータを集めて
結果をExcelにするのが良いよ
csvでもいいんじゃね？

2021/09/16(木) 19:07:04.50

>>41
htmlから簡単なスクレイピングならPower Queryがとっつきやすいかと。
URLのリンク先がcsvやExcelファイル、pdfファイル、
GitHubにあるJSONなんかもこれで結構いける。

CSS構造や、
画面上で検索指定やボタンを押させるような対話型を必要とする
複雑なhtmlなら、Pythonとか。

その先の処理でVBAを組み合わせることはある。
クエリーエラーを避けるため、一つずつシーケンシャルに更新させたいとか、
同時にファイルをDLして保存したいとか。

2021/09/16(木) 19:33:58.44

>>41
chrome 拡張機能スクレイピング
で検索すると良い
ちなみにyahooなんかはスクレイピング対策として、短時間で大量にアクセスすると一発で垢一時停止になるぞ

一番いいのはyahoo　APIなどのAPIを使う事

2021/09/16(木) 20:21:31.16

MacのOfficeソフトとRubyには期待するなって
死んだばあちゃんが言ってた

2021/09/16(木) 20:24:24.60

Macには無料で今流行りのPythonがついているそうなので
スクレイピンクはそっちの方が遥かに簡単じゃないかな
ExcelVBA経験がないのなら

2021/09/16(木) 20:45:30.18

>>48
ばあちゃんハイカラやな

2021/09/17(金) 03:52:03.83

Rubyist は、たいていMac

スクレイピングは、Ruby, Selenium Webdriver, Nokogiri だけど、
無断で、大量のスクレイピングすると、営業妨害で逮捕される

スクレイピングする際、そのサイトのrobots.txt を遵守しないといけない。
取得したデータは、CSV, JSON、データベースなどに保存する

今は、どの本でも、無断でスクレイピングする事は推奨しないと書いてある。
相手のサイトと契約して、API を使わしてもらうべき！

YouTube API とか、Google Maps API とか、
株式相場などもそう

2021/09/17(金) 11:36:45.56

>>41
なんでできもしないことやろうとすんのかね
検索してもわからないんじゃ無理だろ

2021/09/17(金) 12:01:52.56

できないことをやらないと
いつまでたっても何もできないのだが、、、

2021/09/17(金) 18:44:11.62

>>29, 31
ありがとうございました。

2021/09/17(金) 20:27:29.50

見えないものを見ようとして望遠鏡を覗き込んだら捕まった事例

2021/09/17(金) 20:40:10.73

>>53
おまえが出来ないのは自業自得

2021/09/17(金) 22:49:29.45

前の定義で移動できるセルを登録したいのですが、アプリケーションの定義エラーがでます。
範囲は101カ所登録したいのですが、どうやらstr*を３個くらい抜くと動くのですが、名前の定義に制限があるのでしょうか？
str*はどこを抜いても同じです。

Dim stra As String, strb As String, strc As String, strd As String, stre As String, strf As String, strg As String
stra = "=sh1!$H$3:$K$3,sh1!$N$3:$O$3,sh1!$R$3:$S$3," & _
"sh1!$E$6:$N$6,sh1!$O$6:$AC$6,sh1!$AD$6:$BG$6,sh1!$BH$6:$BR$6," & _
"sh1!$BS$6:$CG$6,sh1!$CH$6:$CQ$6,sh1!$CV$6:$DB$6,sh1!$DC$6:DR6," & _
"sh1!$DS$6:$EH$6,sh1!$EI$6:$ER$6,sh1!$ES$6:$FA$6,sh1!$FB$6:$FD$6," & _
"sh1!$FE$6:$FF$6,sh1!$FG$6:$FH$6,"

57 (ﾜｯﾁｮｲ b7cc-W1il) · 2021/09/17(金) 22:50:14.26

続き
strb = "sh1!$E$7:$N$7,sh1!$O$7:$AC$7,sh1!$AD$7:$BG$7,sh1!$BH$7:$BR$7," & _
"sh1!$BS$7:$CG$7,sh1!$CH$7:$CQ$7,sh1!$CV$7:$DB$7,sh1!$DC$7:DR7," & _
"sh1!$DS$7:$EH$7,sh1!$EI$7:$ER$7,sh1!$ES$7:$FA$7,sh1!$FB$7:$FD$7," & _
"sh1!$FE$7:$FF$7,sh1!$FG$7:$FH$7,"
strc = "sh1!$E$8:$N$8,sh1!$O$8:$AC$8,sh1!$AD$8:$BG$8,sh1!$BH$8:$BR$8," & _
"sh1!$BS$8:$CG$8,sh1!$CH$8:$CQ$8,sh1!$CV$8:$DB$8,sh1!$DC$8:DR8," & _
"sh1!$DS$8:$EH$8,sh1!$EI$8:$ER$8,sh1!$ES$8:$FA$8,sh1!$FB$8:$FD$8," & _
"sh1!$FE$8:$FF$8,sh1!$FG$8:$FH$8,"
strd = "sh1!$E$9:$N$9,sh1!$O$9:$AC$9,sh1!$AD$9:$BG$9,sh1!$BH$9:$BR$9," & _
"sh1!$BS$9:$CG$9,sh1!$CH$9:$CQ$9,sh1!$CV$9:$DB$9,sh1!$DC$9:DR9," & _
"sh1!$DS$9:$EH$9,sh1!$EI$9:$ER$9,sh1!$ES$9:$FA$9,sh1!$FB$9:$FD$9," & _
"sh1!$FE$9:$FF$9,sh1!$FG$9:$FH$9,"
stre = "sh1!$E$10:$N$10,sh1!$O$10:$AC$10,sh1!$AD$10:$BG$10,sh1!$BH$10:$BR$10," & _
"sh1!$BS$10:$CG$10,sh1!$CH$10:$CQ$10,sh1!$CV$10:$DB$10,sh1!$DC$10:DR10," & _
"sh1!$DS$10:$EH$10,sh1!$EI$10:$ER$10,sh1!$ES$10:$FA$10,sh1!$FB$10:$FD$10," & _
"sh1!$FE$10:$FF$10,sh1!$FG$10:$FH$10,"

2021/09/17(金) 22:50:45.98

続き
strf = "sh1!$E$11:$N$11,sh1!$O$11:$AC$11,sh1!$AD$11:$BG$11,sh1!$BH$11:$BR$11," & _
"sh1!$BS$11:$CG$11,sh1!$CH$11:$CQ$11,sh1!$CV$11:$DB$11,sh1!$DC$11:DR11," & _
"sh1!$DS$11:$EH$11,sh1!$EI$11:$ER$11,sh1!$ES$11:$FA$11,sh1!$FB$11:$FD$11," & _
"sh1!$FE$11:$FF$11,sh1!$FG$11:$FH$11,"
strg = "sh1!$E$12:$N$12,sh1!$O$12:$AC$12,sh1!$AD$12:$BG$12,sh1!$BH$12:$BR$12," & _
"sh1!$BS$12:$CG$12,sh1!$CH$12:$CQ$12,sh1!$CV$12:$DB$12,sh1!$DC$12:DR12," & _
"sh1!$DS$12:$EH$12,sh1!$EI$12:$ER$12,sh1!$ES$12:$FA$12,sh1!$FB$12:$FD$12," & _
"sh1!$FE$12:$FF$12,sh1!$FG$12:$FH$12"
ActiveWorkbook.Names.Add Name:="Nyuryoku2", RefersToLocal:=stra & strb & strc & strd & stre & strf & strg

2021/09/17(金) 23:08:43.51

255文字

**デフォルトの名無しさん** (JP 0Hfb-qDHA) · 2021/09/17(金) 23:31:47.79

>>57
1274文字まで
質問のために加工したんでなければ、その範囲はもっとシンプルにまとめられるぞ
"=$H$3:$K$3,$N$3:$O$3,$R$3:$S$3,$E$6:$CQ$12,$CV$6:$FH$12"

57 (ﾜｯﾁｮｲ b7cc-W1il) · 2021/09/18(土) 09:25:55.38

>>61
ありがとうございます。シート名を削除したら動きました

2021/09/18(土) 09:39:13.68

>>62
横から失礼
RangeはUnionにすると実質文字制限がなくなる

**デフォルトの名無しさん** (JP 0Hfb-qDHA) · 2021/09/18(土) 10:16:04.89

>>63
引数はRangeじゃなくてStringなんよ

2021/09/18(土) 12:10:53.45

名前定義ってRangeオブジェクトからでもできるのか知らなかった
Range("B1:G10").Name = "Nyuuryoku2"

2021/09/18(土) 12:26:09.80

便利だけどExcelの名前の定義ウインドウは使いにくすぎる

2021/09/18(土) 12:26:47.71

つまりたくさん定義すると管理しにくいんだよな