VBA

定数のみを選択する VBA マクロ

久々の投稿です。

 

ネタはたくさんあるんですが、更新する時間がとれず。。

 

ってことで今回のネタ 「定数のみを選択するマクロ」

 

金額のリストや計算書では手入力セルとシート関数セルがありますよね。

週次の計算の更新をするために、

この手入力セルとシート関数セルをわけて消すのも面倒ですよね。

 

そんなときに使える

 

Range.SpecialCells メソッド

 

セルの取得とは言っても、たくさんの取得種類があります。

 

XlCellType 定数
xlCellTypeAllFormatConditions 表示形式が設定されているセル -4172
xlCellTypeAllValidation 条件の設定が含まれているセル -4174
xlCellTypeBlanks 空の文字列 4
xlCellTypeComments コメントが含まれているセル -4144
xlCellTypeConstants 定数が含まれているセル 2
xlCellTypeFormulas 数式が含まれているセル -4123
xlCellTypeLastCell 使われたセル範囲内の最後のセル 11
xlCellTypeSameFormatConditions 同じ表示形式が設定されているセル -4173
xlCellTypeSameValidation 同じ条件の設定が含まれているセル -4175
xlCellTypeVisible すべての可視セル 12

 

今回は定数を取得するので、xlCellTypeConstants となります。

サンプルは下記です♪

定数は消えて、数式は残ります。

Sub SelectType()
Cells.SpecialCells(xlCellTypeConstants).Select
Selection.ClearContents
End Sub

 

 

ではでは♪

バイナリーモードで読み込む(そもそもバイナリーってなんだ?) VBAマクロ

テキストファイルを操作するマクロ書いてたらぶち当たったので備忘録として。

 

バイナリーモードってのは 2進数で表現されたデータ(テキストファイル以外)を読み込みますよってこと

 

テキストファイル以外のファイルを扱う時に使うって覚えておけば問題ないかな!

 

あんま使わないけどw

 

サンプルコード

 

Sub filesousa()
Dim fun As String
Dim Num As Integer
Num = FreeFile
Open “‘C:\Users\Yusuke\Documents\名前入力.txt” For Binary As #Num ※下線部分は適宜作成してください。
fun = Space(FileLen(“C:\Users\Yusuke\Documents\名前入力.txt”))
Get #Num, , fun
Range(“A1”).Value = fun
Close Num
End Sub

 

セルに関数を入れる VBA マクロ

マクロで合計出せるけど、しがらみがあって(笑)マクロにできないという場面があるかと思います。

 

そんなときにセルに関数を挿入したいなと思うかもしれません。

 

そんな時に使用するのが Formula プロパティ

 

Range.Formula

 

もしくは

 

Cells.Formula

 

を使用すればサクッとできます♪

 

Sub Formulasam()
Range(“A1:A10”).Value = 5
Range(“A11”).Formula = “= SUM(A1:A10)”
End Sub

 

 

 

 

ペースト方法を指定して貼り付け!PasteSpecial VBAマクロ

値で貼り付けたいのにミスった!ってことあるかと思います。

 

そんな時には PasteSpecialメソッド を使いましょう。

 

xlPasteValues -4163 値のみ
xlPasteComments -4144 コメントを貼り付け
xlPasteFormulas -4123 数式を貼り付け
xlPasteFormats -4122 コピーしたソースの形式を貼り付け
xlPasteAll -4104 すべてを貼り付け
xlPasteValidation 6 入力規則を貼り付け
xlPasteAllExceptBorders 7 輪郭以外のすべてを貼り付け
xlPasteColumnWidths 8 コピーした列の幅を貼り付け
xlPasteFormulasAndNumberFormats 11 数式と数値の書式を貼り付け
xlPasteValuesAndNumberFormats 12 値と数値の書式を貼り付け
xlPasteAllUsingSourceTheme 13 ソースのテーマを使用してすべてを貼り付け
xlPasteAllMergingConditionalFormats 14 すべてを貼り付け、条件付き書式をマージ

 

ごちゃごちゃ余計な引数は使わん!って人は下記のサンプルをご閲覧あれ♪

 

Sub Pastes()
Range(“A1”) = “ペースト練習”
Range(“A1”).Select
Selection.Copy
Range(“E1”).PasteSpecial xlPasteValues
End Sub

 

ではでは。

 

 

変更を保存しないで閉じる VBA マクロ

マクロ書いているとブックとブックの操作ってよくあると思います。

指定のブックを開いてにょろにょろする とかですね。

 

ただ開いたブックを閉じるときに ”変更を保存しますか?”

ってメッセージがマクロ実行中に起きると何だか萎えますよね笑

 

そんな時は Closeメソッドを使ってサクッと解消しましょ♪

Closeメソッド

Workbooks.Close 

Savechanges:(省略可)=True(変更を保存する)もしくはFalse(保存しない) 

Filename:=ファイル名を指定して保存(省略可)

既定値はダイアログを表示します。

 

サンプルは下記

 

ではでは♪(*´ω`*)

文字列を検索するマクロ VBA

何かと便利な検索機能はVBAでも使うシーンがあるかと思います。

 

そこで使うのはFindメソッド

構文

Object:Rangeオブジェクト

What:(必須)検索データを指定

After:(省略可) 指定したセルの次のセルから検索を始める

LookIn:(省略可)検索対象を指定 数式(xlFormulas)、値(xlValues)、コメント(xlComments)

LookAt:(省略可)完全一致(xlWhole)もしくは部分一致(xlPart)かを指定

SearchOrder:(省略可) 検索方向を指定する。列方向(横方向)で検索(xlByColumns)、行方向(縦方向)に検索する(xlByRows)

SearchDirection:(省略可) 前方に検索(xlNext:既定値)、後方に検索(xlPrevious)

MatchCase:(省略可) 大文字と小文字を区別する(True)、区別しない(False)

MatchByte:(省略可) 半角と全角を区別する(True)、区別しない(False)

SearchFormat:(省略可) 書式の検索をする (True)、検索しない(False)

検索文字が見つからない場合は ”Nothing” を返します。

 

構文長いですね(^-^;

 

長い!

余計な構文いいから文字探したいんだ!( ゚Д゚)

 

という人は下記のサンプル使ってみてはいかがでしょう。

Sub sagasutest()
Dim R As Range
Set R = Cells.Find(what:=”ミスチル”)
If R Is Nothing Then
MsgBox “文字が見つかりません”
Else
MsgBox “文字が見つかりました。”
R.Activate
End If

End Sub

ミスチルが好きなので

例文でミスチルとしていますが検索したい文字を入力してください笑

 

ではでは

今月末日・先月末日・来月末日 を返すマクロ VBA

事務職してるとexcelで月末日を出さなきゃならなくなりますよね。

 

下記、サンプルコードです。

 

Sub Hiduke()
‘今月の末日
Debug.Print DateSerial(Year(Date), Month(Date) + 1, 0)
‘前月の月末日
Debug.Print DateSerial(Year(Date), Month(Date), 0)
‘翌月の月末日
Debug.Print DateSerial(Year(Date), Month(Date) + 2, 0)
End Sub

 

DateSerialはVBA使っていると必須のデータ型関数です。

 

覚えれば非常に便利です♪

VBA コピーの状態を解除する コピーモードの解除

マクロを書いて処理させられたのはいいけど、

コピーの状態がされたまま ってありがちです。

 

そんな時には

Application.CutCopyMode

を使用しましょう。

サンプルコードは下記です。

 

Sub copyR()
Range(“A1”).Copy
Application.CutCopyMode = False
End Sub

 

ちなみにApplication.CutCopyModeには返り値があります。

xlcopyコピーなら 1 を xlcutなら 2 を返します。

コピーされている状態 なのか、 切り取っている状態 なのか判別することができます。

使うことはなさそうですが笑

Sub copyR1()

Dim Modemiru
Range(“A1”).Copy
Modemiru = Application.CutCopyMode
Debug.Print Modemiru

Range(“A1”).Cut
Modemiru = Application.CutCopyMode
Debug.Print Modemiru

End Sub

 

イミディエイトウィンドウには

と表示されます。

複数選択(離れたセル)の行数を数える マクロ エクセルVBA

複数選択された(離れたセル)を選択をした場合の行の数えるマクロ

 

ユーザーフォーム作ってたら必要なマクロになったので考えてみました。

連続していない、離れたセルの行は”Rows.count”では数えられないんですね(*_*)

てなわけで使うのは”selection.areas”  Areasプロパティです。

 

Sub Areas()

Dim Hani As Range

Dim Rcount As Integer

Set Hani = Range(selection.address)

For Each kazu In Hani
Rcount = Rcount + Selection.Areas.Count
Next

Debug.Print Rcount

End Sub

 

やっと作りたいマクロが作れたよ~

おやすみなさい

みんな知らない(´ω`)エクエルマクロ(VBA)を書くって実はめちゃ楽しいこと♪

こんにちは

管理者のようじえです。

このブログではエクセルマクロにまつわる話と気まま日記を投稿していきます。

なぜエクセルなのか自己紹介をかねて書いていきます。

ちなみに何か発信したりするのって苦手なので、

変な文章書いてても温かい目で見守っていただければと思いますw

 

もともと私は社会人になるまでエクセルはまともにさわっていませんでした

中学時代に無料のレンタルPCをいじいじしていていましたが、

IT音痴な両親(当然ではありますが)に反対され泣く泣くPCを捨てられたのを

今では恨みを込めつつ冗談で両親に突っ込んでます笑

 

新卒で一社目のBlack企業に入社して営業職に就いてエクセルを触るようになりました。

もちろん最初はエクセルなんてわかりませんのでカチカチいじってました。

関数式を見てもIFってなんじゃこりゃ、Vlookってなんだ!

式長っ!!!読めないだろこりゃw なんてチンプンカンプンな状態でした。

計算やらなんやらせっかく関数で一瞬でできる機能も使えず、

無駄な時間をかけていちいち手作業で計算して時間をかけて見積書を作ってました。

そんなこんなで1社目のBlack企業を退職し、2社目メーカーの事務職として就職しました。

 

事務職となり、まともにエクセルと向き合うようになりました。

月間数億円のお金をやり取りする仕入れ先とエクセルで原価計算を行い、

大量のデータと様々な種類の製品を扱うようになりました。

原価計算等のチェック(A4サイズで10枚以上)は全て印刷して、紙とエクセルを付け合わせてチェック。チェックしては訂正など。

なんとも頭のおかしくなるような作業を毎週8時間以上続けていました。

単純作業・チェックの苦手な私は全然仕事ができませんでした。

今思い返しても何のスキルにもならず、本当にバカバカしい時間を過ごしていました

 

仕事のできない私ですが、どうにか楽にできる方法はないか模索しました。

ココでエクセルの書式やシート関数を覚え、ある程度のエクセルの知識はついていきました。

ただ、書式やシート関数の知識はついても作業時間と労力は大きくは変わらず。シート関数、書式の設定だけだと限界がありました。

チェックの苦手な私は何度もミス。怒られてはまたミス。

ミスする自分に自信を無くし、周りからは君はクビ!と同等な扱いでした。

仕事に対する気力も失い、

人として価値がないんだなと空虚で喪失感漂う日々を送っていました。。。

当時は本当に辛かったです

 

本気でどうにかならないものかと。

考えているところでネット上でExcelマクロの本格的な講座を見てなけなしのお金を払って受講しました。

初めてマクロを見たときは驚きと感動でした。

こんなものがあるのかと。

ダメな自分はこれをやるしかないと思い、本気でエクセルマクロに没頭しました。

平日は会社から帰って夜遅くまで自宅でパソコンをいじいじ。

休日も予定がなければ朝から晩までマクロをいじいじ。そんな日々を過ごしていました

 

ついに本格的に実務で使用する自分で作ったマクロを完成させました。

初めて作ったマクロは土日を使い、日曜は一睡もせず月曜を迎えて作ったマクロです。

自分が作ったマクロを会社ではじめて使用しました。

内容はブックから他のブックへの転記で、初心者にしてはなかなか手ごわい内容でした。

手ごわい内容のわりに他の人には手ごわさが伝わらない葛藤もありましたw 今見ればショボショボコードですが笑

紆余曲折を経て初めて自分が作ったマクロがうまくいったときは何よりも自分が一番嬉しかったです。

 

その後どんどんマクロを導入していき、

会社の部署の共有リストの作成を自動化。複雑な条件の製品のリストアップの自動化などなど

それまで一日潰していた原価計算も今や小一時間程度 30分程度となりました。

 

さらに外部ライブラリの操作・フォルダ操作を理解し、フォルダ内のファイルを自由自在に操りリストアップ。

種々の項目の計算や細かく複雑な計算などできるようになりました。

このあたりである程度のスキルがついたなと実感し、

やっとこさほんの少し自分に対して自信がでてきました。社内でもVBAで一目置かれるようになりました。

 

ザクっと話しましたがマクロの全容を覚えるのは一概に簡単とは言えないです。

順調にマクロを覚えられたかと言われればもちろん、NOです。

 

なんでうまくいかないんだ。なんでココでエクセルさんブチ切れちゃうの!

などなど たくさん、たくさん 壁にぶつかりました 挫折しかけました。

それなりの時間と安くないお金も使いました。

 

それでも折れなかったのは自分に対する期待とマクロの可能性を実感できたこと。

そしてエクセルマクロを好きになったからです。

せっかく仕事として好きなことをみつけたので勉強がてらみなさんと情報共有したいなと思い、サイトを立ち上げました♪

ちなみに今は他の言語に夢中です笑

 

VBAで挫折された方、辞めてしまった方たくさんいると思います。

機会があればもう一度向き直ってVBAをいじってみてください。何度もコードを書いてみてください。

ふと、こんなもんかと理解できることもあります。

ご質問あれば私でできる範囲で尽力します♪お気軽にご質問ください(^^)