Excel VBA 質問スレ Part51
レス数が950を超えています。1000を超えると書き込みができなくなります。
スレ立ての際は一行目に
!extend:checked:vvvvv:1000:512
と入れてスレ立てして下さい
ExcelのVBAに関する質問スレです
コード書き込みや作成依頼もOK
※関連スレ
VBAなんでも質問スレ Part2
http://mevius.2ch.net/test/read.cgi/tech/1432173164/
Access VBA 質問スレ Part1
http://mevius.2ch.net/test/read.cgi/tech/1328536426/
Excel総合相談所 126
https://mevius.2ch.net/test/read.cgi/bsoft/1496487719/
※前スレ
Excel VBA 質問スレ Part50
http://mevius.2ch.net/test/read.cgi/tech/
VIPQ2_EXTDAT: checked:vvvvv:1000:512:----: EXT was configured 👀
Rock54: Caution(BBR-MD5:0be15ced7fbdb9fdb4d0ce1929c1b82f) >>885
>セルに入力した選手コードを選手名に置換するような動きをさせたい
セルに選手コードを入力して、Enterを押した途端に選手名に置き換わるような動きをさせたいんだな？そうなんだな？ >>887
検索置換のダイアログでシート内の選手コードを一括変換する、みたいな手作業の業務手順をそのままマクロ化しようとしているのかもしれないし、そういう場合はそもそもシートの設計や業務手順に問題がある場合が多い。
最初にVLOOKUP使えと指摘したあんたの感覚は基本的には正しいと思うぞ。 >>885
単発ならワークシート関数でやった方が簡単
VLOOKUPで変換したのを用意しといてそれをごそっと上書きする
何回もやるなら、上記をマクロにする
このくらいならマクロの記録でもいけそう
全部VBA上でやる方法もあるけどビギナーには大変でしょう
こんな感じでいいんじゃなかろうか
Dim i
i = スタートする行
Do While Not Worksheets(1).Cells(i, 1) = ""
Worksheets(1).Cells(i, 1) = WorksheetFunction.VLookup _
(Worksheets(1).Cells(i, 1), Worksheets(2).Range("名簿範囲"), 2, 0)
i = i + 1
Loop
シートのインデックスとかは書き換えてね
意味不明ならマクロ記録かさらなる修行をオススメする >>889
VBAに慣れてる人ならセル値の置換と聞いただけでワークシート更新イベントを使いたいのかなって思いがちだよな
俺も最初はそう思ったわ
でも質問者の質問内容を推測抜きで素直に読むとそうとは限らないんだよな >>888
トーナメントの結果を入力するようになっているのですが既に報告用ワークシートはあるのですが入力件数が多くなりすぎて最終的にはバーコードによる入力を試みようとしています
また、その報告用ワークシートは書式が決まっており並べ替えエラーチェックのマクロが既にあるので行を入れたりして関数をいれる対応ができないという状態です
>>889
入力後にマクロ実行用のボタンを押して置換するような想定でした
エンターを押した瞬間変わるものでも大丈夫です >>887
>>891
ありがとうございます
家に帰り次第試してみます
>>890
今まではワークシート内から対戦選手の名前を探してきてコピーアンドペーストで対応していたのですが参加者が年々増えてきて（今年の見込みは約千人位）追い付かなくなってきたために速度向上を目的としています >>884
VBAに記述のあるWorksheets("Sheet1") の箇所を、
並べて比較でペアにしているシートに特定する方法が思いつかないのです。 >>895
ファイル名とかシート名がランダムでないなら
固定値じゃないの？
どんなファイル名か分からない、シート名か分からないってなら都度入力するしかないな。
データ形式で特定することもできるけど、
無駄な作業にしか思えない。 >>893
こんな感じならどうかな
以下は、実行すると報告用シート内に含まれる選手コード全てを一気に置換する
複数の選手コードを含む場合も一発で置換できるはず
名簿にはコードの欄が空白の行がなく、名簿シートのA2セル以下に選手コードが入力されているものと仮定してる(A1は表見出し)
セル値の一部が選手コードと一致する場合も置換されてしまうので、コードのみが入力されているセルの値だけを確実に置換したい場合、Replaceメソッドの引数のLookAt:=xlPart をLookAt:=xlWholeに変更してくれ
Sub ReplaceCode
Dim sh_list As WorkSheet '選手名簿シート
Dim sh_report As WorkSheet '報告用シート
Dim i As Long
Set sh_list = WorkSheets("選手名簿シート")
Set sh_report = WorkSheets("報告用シート")
For i = 2 To sh_list.Range("A2").End("xlDown").Row
sh_report.Cells.Replace What:=sh_list.Cells(i, 1).Value, Replacement:=sh_list.Cells(i, 2).Value, LookAt:=xlPart
Next i
End Sub >>896
ファイルのフォーマットが複数あるので、
事前にファイル名とシート名を規定できないのです。
なので、せめてペアにしたファイル同士が特定できれば、
ファイルとシートを選択する手間が省けると思ったのですが・・・
３つ以上開けなくなるけど、workbooksからマクロブックを除外した先頭２つを比較する方法で考えてみます。 >>898
それは運用を変えよう。機械的に処理するなら人間側が合わせる必要がある。
手動で命名規則に沿うか、命名自体を自動的にして人間が意識しないようにするか。 エクセル2010でCSVファイルを開いた場合、
UTF8やshiftjisを自動で判定して表示してくれますが、
この時に判定された文字コードを取得する方法はありますか。
用途は、外部データの取り込みでCSVを開きなおすための文字コードの特定です。 >>901
ありがとうございます。
試してみます。 >>897
実行時エラー13
型が一致しませんとなっていて
For~の行でエラーを出していて止まるみたいです
あと報告シートが選手の強さ別に複数に分かれているのですが今選手コードの入力したシート（つまりいま開いているマクロ実行用のボタンを押したシート）を処理したいという場合どうするとよいのか教えていただけると助かります >>904
エラーの出たコードを転載して見せてもらえませんか？こちらの環境ではうまく動いているんですが、私がコードを転載したときにミスをしたかもしれないので
それから、コードはボタンマクロ(ボタン名_Clickのような名前のプロシージャ)として各シートのシートモジュールに書いているんでしょうか？ >>905
Sub ReplaceCode()
Dim sh_list As Worksheet '選手名簿シート
Dim sh_report As Worksheet '報告用シート
Dim i As Long
Set sh_list = Worksheets("選手名簿")
Set sh_report = Worksheets("対戦結果表_A級")
For i = 2 To sh_list.Range("A2").End("xlDown").Row
sh_report.Cells.Replace What:=sh_list.Cells(i, 1).Value, Replacement:=sh_list.Cells(i, 2).Value, LookAt:=xlPart
Next i
End Sub
シート作成用のコードが既にあるのですがその中のコードを流用して各シートに実行用のボタンを置く計画でした
ActiveSheet.Buttons.Add(2, 1.5, 125, 18.75).Select
Selection.OnAction = "CSV_SAVE"
Selection.Characters.Text = Worksheets(2).Cells(1, 1).Text & "級のCSVファイル作成"
With Selection.Characters(Start:=1, Length:=15).Font
.Name = "ＭＳ Ｐゴシック"
.FontStyle = "標準"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
これのCSV_SAVEをReplaceCodeにしてボタンの名前と場所をかえるつもりでしたがあまりよくない方法なのでしょうか
必要であれば既にあるコードを出しますので教えてください >>903
application.nkf オブジェクトが見つかりませんでした。
下記のapplicationオブジェクト内にも、nkfプロパティが見つかりません。
https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/application-object-excel
オブジェクトの参照方法が違うのでしょうか。 >>906
申し訳ない、Forの行でEnd("xlDown")となってるの、自分の転記ミスでした
エラーの原因もこれでしょう
二重引用符なしでEnd(xlDown)と書き直して動かしてみてください 質問です。
ソルバーで普通は目的セルをrangeなどで指定しますが、シートのセルにアクセスせず、vba内の変数を直接ソルバーにかけてvba内だけで完結したいのですが、方法あるでしょうか？ >>908
うまくいきました
ありがとうごさいます セルのA列にある文字列をコンボboxにaddですべて入れた後に、一文字入力する度に候補が絞られてくって出来ます？
コード教えてもらえないですか？ 一文字入力する度にコンボボックスの候補を絞ればいいんとちゃうか？
知らんけど 社内に部品表があって、重複している名前を省いて部品種類一覧の表を作ろうと思ってます。
・ネジM5
・ネジM5
・ナットM5
・ナットM5
↓
・ネジM5
・ナットM5
というイメージです。
自分なりに下記のマクロを組んでみました。
Dim row As Long, col As Long, D As Range
col = 1
row = 1
Do Until Cells(row, col) = ""
If Not Cells(row, col) = Cells(row + 1, col) Then
If D Is Nothing Then
Set D = Cells(row, col)
Else
Set D = Union(D, Cells(row, col))
End If
End If
row = row + 1
Loop
この後レンジDをコピペするマクロです。
部品の種類が100とか200なら動いてくれますが、本来20,000件超の部品の種類があり、こちらを処理するとフリーズします。
デバックのウィンドウはユニオンのところを指定してきてるんですが、ユニオンにはまとめられる量の限界などがあるのでしょうか？ >>913
Excel VBAでやってもいいけど、RANGEオブジェクトに溜め込むやり方よりは、素直に別シートに値をコピー、さらにそれを検索して別の値が出てきたら別シートにコピーでいいんじゃないのか？
そもそもExcelのフィルタ関連の機能でもできるからVBAを使う理由がわからない。 DBMSがコンソールなんかに返した結果を
Excelセルに上手く貼り付ける方法ってある? >>918
詳細忘れたけど代入する事で、そのまま貼り付けられるメソッドがあったはず。 >>913
ADOなら一瞬で終わる
いろいろ突っ込まれているように、別の方法のが簡単だと思うけど
シート名やら列名は読みかえてね
Dim adocon As Object, adors As Object
Set adocon = CreateObject("ADODB.Connection")
Set adors = CreateObject("ADODB.Recordset")
With adocon
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0"
.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name
End With
Set adors = adocon.Execute("SELECT DISTINCT 部品名 FROM [部品表$]")
Worksheets("部品種類一覧").Range("a1").CopyFromRecordset adors
adocon.Close >>913
下手にマクロ組むよりExcelの標準機能の重複削除を使え。
VBAとは違って並列処理できるから速度が段違い 重複なしリストはいろいろやり方あるけど、標準機能使うかadoでやるかがスマートだろうね
dictionaryもありだけど、fsoのやつはなんか取り回しがいまいちじゃね ここ初心者スレかと思ってスレタイ見なおしてしまったわw
オレ以外初心者しかおらんやんw .netframeworkのlistって重複削除機能なかったっけ？ >>927
リストにあるかないかはわかるみたい
これなら配列に突っ込んで総当たりしていくのと変わらなそう
読みやすくはなるけど
Dim partsList As Object
Set partsList = CreateObject("System.Collections.ArrayList")
Dim partsData
Set partsData = Range("データの列")
Dim parts
For Each parts In partsData
If Not partsList.Contains(parts.Value) Then
partsList.Add parts.Value
End If
Next
出力処理省略 >>924
> dictionaryもありだけど、fsoのやつはなんか取り回しがいまいちじゃね
意味不明
Dictionaryとfsoになんの関係があるんだ？ >>913
とりま、Union関数は遅いから絶対に使うな、それだけ
セル数がちょっと増えると簡単にフリーズする
普通に配列とかに入れてった方がいい >>930
とりま、なんでいきなりそれを言いだしたのかよく分からんが
遅いのはお前のプログラムが下手糞なだけ
脈絡のない下手糞自慢ごくろうさん require 'set'
File.readlines("ファイル名").to_set.map { |item| puts item }
Ruby の集合を使うと、以下のような行区切りのファイルを、
あ
い
あ
あ
う
い
以下のような結果にできる。
ただし、集合にはデフォルトで、順番は無いので、
順番が必要なら、お好みの基準でソートする
あ
い
う 毎度思うんだが、Rubyって他言語のスレにまで布教しに来なきゃならないほど不人気なのか？ Ruby だと、テキスト処理が、めちゃめちゃ簡単に作れる
他の言語のように、悪戦苦闘しない どうせなら引っ越し準備も兼ねてPythonにしようゼ 単純に2万個のセルをUnionするだけで1時間かかる
Sub aaa()
Debug.Print Time
Dim u As Range
Set u = Cells(1, 1)
For r = 3 To 40000 Step 2
Set u = Union(u, Cells(r, 1))
Next
Debug.Print Time
End Sub 2万個のデータは、テキストデータだけなら、1MB ぐらいだろ
>>932
のRuby でのテキスト処理なら、1秒ぐらいじゃないか？ >>937
空気読んでね
ここはテキスト処理の質問スレじゃなくて
Excel VBAの質問スレだから そんなに時間が掛かるのなら、一旦データをエクスポートして、
データをテキスト処理してから、インポートして戻せば？
たかが、2万個のデータで、1時間は現実的ではない
そんなに時間が掛かっても、どうしても、VBA でやりたいのか？ あくまでUnionを使った場合であって高速に処理する方法はいくらでもあるからなぁ。
それに仕事でVBA使う環境だと他社との協調も考えなきゃいけないからRubyインストールする方がよっぽど非現実的だし。
まぁ、たぶんおちょくってるつもりなんだろうけど、そもそもVBA以外の環境がないって前提のスレだから、
Rubyマンの煽りが煽りになっていないっていう・・・。 重複の削除はワークシートでやった方がてっとり早いと思う >>940
> そもそもVBA以外の環境がないって前提のスレだから
そんな前提で質問に来る人もいるだろうけど、
「最適解」を求めている人が、とりあえずVBAだとどうなるか、と思って質問する場合もあるでしょ
だから、質問者が「全体にＶＢＡ以外はダメ」って制限をかけていない限りは、
上でも、ACCESSを進めている人もいたけど、実用面から考えた回答も質問者の利益になると思うんだけど
（質問者がそう思っていても、何も知らないからそう思ってるってパターンすらあり得るかな） >>942
Excel VBA質問スレでの「実用面」は、一般的な企業向けWindows PC(Office製品入り)で出来る事に制限されると思うけど。
それに質問者が明言した事以外は「出来ない」と考えておくのが常識だと思うね。
だいたい他の言語に興味があるなら、その言語のスレで質問すりゃいいわけだし、
どんな自己弁護をしたところで特定言語のスレで他言語の話をする理由にはならないよ。
せめて質問者に対してVBA以外の開発環境は無いか訊ね、Rubyが使えるという自発的な回答が得られた時に初めて、
Rubyスレに誘導して、そちらで話を展開すべきだね。 >>943
> それに質問者が明言した事以外は「出来ない」と考えておくのが常識だと思うね。
リアルでも、相手に聞きもせずに勝手にそんな縛りをかけて要件定義する人はいないと思うよ
> せめて質問者に対してVBA以外の開発環境は無いか訊ね、Rubyが使えるという自発的な回答が得られた時に初めて、
> Rubyスレに誘導して、そちらで話を展開すべきだね。
環境を聞くのは勿論ありだが、「○○だとより簡単にできるよ」ってこちらから言う事に何の問題があるのか？
今ある環境内で必ず実現する必要があるなら別だが、そうでない場合もよくあることでしょ
ま、いずれにせよ、何も聞かないうちに勝手に「○○に違いない」って決めつけるのは、
要件定義では一番しちゃいけないことだよ >>944
仕事している風を装いたいんだろうけど、明らかに経験が無いの丸出しなんだよな・・・。
そもそもVBAスレでRubyの話を出してくる事自体が問題なんだよ。
君が問題になっているのは要件定義の話ではなく開発環境の話だ。
明確な線引きをするなら、このスレではxlsmに記録し、特別な準備なしに実行できるものでなければ、何であれ許容範囲外だ。 またそうやって明文化されてもいない自分ルールを展開していくー 明文化されてはいないけど、過去から今までのスレでの反応を見る限りは、
だいたいxlsmファイル単体でマクロの実行が補完できるかどうかが拒絶反応の出る出ないを分けてるよ。
ただ個別にマクロが記録された二つ以上のファイルを連携させる場合の話もあるから「xlsmに記録し、準備なしに実行可能な」と定義しただけ。
押しつけるつもりはないけど、この定義で問題が無ければテンプレに加えてキチ避けしてほしいくらい。 >>929
ごめんscriptingRuntimeのやつ、の間違いだ VBAをメインとして他言語も補助的に使う。
それなりにあると思う 同じ現象が起きるようにできるだけ簡単なコードを用意しました
まっさらなxlsmにUserForm1を挿入し、TextBox1とTextBox2を横に並べます
UserForm1モジュールに下記のようにコードを記述します
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 39 Then
With TextBox2
.SetFocus
.SelStart = 0
End With
End If
End Sub
UserForm1を呼んだ後、TextBox2にabと記入します
このとき、TextBox1からTextBox2に矢印キー右でうつろうとするとaの前ではなくaとbの間にカーソルが来てしまいます。
どうすればaの前にカーソルが来るようになりますか？もちろんabは例示にすぎずいろんな文字列が入ります
よろしくお願いします >>950ですけど、KeyCode=40、つまり矢印キー下にセッティングして
TextBox1から2に移ると頭にカーソルきますね
ということは、入力した矢印キー右がTextBox2に移ったあとも生きててその影響が出てるってことですかね
まぁ原因分かっても解決方法が分からないのですが 試していないけれど
With TextBox2 の前に
KeyCode = 0 を入れたらどう >>949
VBAでは書きにくい or 実行速度がめちゃ遅い
かつ
その言語なら簡単に書ける or 実行速度が速い
ならありうると思う
なのでAccessとかADOならまだわかるけどRubyはないな >>952
しゅごい・・・できました
ありがとうございました 。と。の間に入ってる文字を全部同じ文字に置換したい場合ってどうすればいいでしょうか？
。。 →。ccc。
。akok。 →。ccc。
。3293i9ia。 →。ccc。
みたいな感じです 数式の中のってのがよくわかんないから実際の数式を書いてくれ こういう事がやりたいって感じです
わかりづらくてすいません
=123456789(この9桁の数字はランダム) → =123ccc789
=123456789(この9桁の数字はランダム) → =12ccc3456789 2週間くらい前から、
フォームコントロールのボタンを配置すると
エクセルファイルの起動が
いつもの起動時間の5倍くらいかかる様になり困っています。
ちなみに、新規のファイルでも同じ現象になり、
他のパソコンでも同じ現象になっています。
どなたか、解決方法を知らないでしょうか？
今使っている環境は
Windows 10、バージョン 1709
Excel2016 バージョン 1712
です。 >>960
草生やしてんじゃねえよ真面目に質問しろ >>958
みんなこれでわかるの？
上と下に規則性があるように見えないんだが
上は値を置き換えてて、下は挿入してるよね >>913
そのテキストファイルがCSVファイルみたいにカンマ区切りで
ヘッダとして項目名が付いてて項目名に重複が無ければ
そのファイルにSQLぶん投げて結果セット取ってこれるんだけどなぁ 地道にループぶん回してFomulaLocalとInStrとMidしてちょ いや、ループするにしても
VbScriptingDictionary使うかCollection使って
値をキーとして登録して
同じキーがあれば無視、無ければ登録追加で
最後にその取っておいた内容を
展開するようにすればそこそこのレスポンスで
行けるんじゃないかな。
SQLで行けるならそれが最速だとは思うけど。 ちゃんとしたDBMSならともかく、エクセル表にSQL投げて実行速度が速いとか考えられんけど ADOだとそれなりに早いイメージなんだけど
実際はどうなのかね
誰か検証していてもよさそうだが 一項目だけの比較なんかだとVBAでやるのと変わらないけど、色々と条件が増えていくとSQLの方が速度が落ちにくい。
あと他の誰かが見ることも考慮すると、SQLで見えるようにしておいた方が分かりやすい。
ただExcelでSQLというのは絶望的に使いづらい。 UPDATEはできるけどDELETEができないんだっけ？ できなくてもやらなきゃいけない時があるんだよ男にはな エクセルのテーブル機能ってどうなんよ
あんまり使わんから馴染みないんだけどsql使いやすくなったりせんの？ >>975
猫もだよ
穴に逃げた虫を懸命に取ろうとしている こういうのを見ると
早くPython導入して欲しいって思うよな
アンケート取られてたからちょっと入門見たけど結構面白いわアレ
こういった集約的なことに関してもずっと楽に作れそう
VBやC#でも後の方で覚えることになるラムダ式や
イテレーターとかごく基本的な当たり前のこととして
いきなり最初から出てくるし VBAですら暴走しまくりのお前らにpythonなんか与えたら社会を困らす力作マクロ()がますます増えちゃうじゃんかw コードをまともに読み書き出来ないメゾット君が心配するような事でもないけどな VBAを廃止なんてできるわけないからPythonと入り乱れたプログラムになるんだろーか？ 影響するのは最新バージョンだけだろうし、Excel版Pythonの初期バージョンだと使い物にならなかったり参考文献が少なかったりの問題で、
本格的に使われるようになるにしても五年以上かかりそう。そしてそこからさらに数年経たないと、そのバージョン使う会社が出てこなさそう。
っていうか今までもVBAをVB.Netへ移行させようとして失敗してたらしいし、AIブームに乗じた一時的なもんで実現しないんじゃないの。 Pythonって起動にめっちゃ時間がかかるから（5秒くらい）
普段使いのちょっとしたツール作るには使い物にならないという印象 多分Python載ったとしてもしばらくはVBAと共存だろうとは思う。
勉強嫌いな爺さん達にはPythonが来るのは驚異以外の何物でもないかも知れないけど
導入されたら少なからず徐々に普及し始める。
なぁに、EXCELの中のコンパクトに纏まった世界の話だし
勉強するにはうってつけだと思う。
少なくとも今後のVBAよりは次に繋がるよ。 今まで言語を変えるような具体的な話は出てなかったからなぁ
まさかObject指向言語じゃなくていきなり関数型言語が来るとはなぁ PythonはAIブームの収束と共に消えていきそうな雰囲気だから将来性っていうと疑問が・・・。 レス数が950を超えています。1000を超えると書き込みができなくなります。
