シートの存在を調べて存在しなければ新しいシートを作成
頻繁に使うロジックなので関数化してみました。
- 任意の名称を持つシートが存在するかどうか調べる
- 上記で存在しなければその名称で作成(もしくはあるシートをコピーしてその名称にする)
' 呼び出し例(1) Dim wsSheet As Worksheet Set wsSheet = SelectSheet("シートA", "テンプレート") ' → 常に wsSheet = ThisWorkbook.Sheets("シートA") となる。 ' ただし、「シートA」が存在すればそのシート、 ' 存在せず、「テンプレート」シートが存在すればそれをコピーしたもの、 ' 「テンプレート」シートも存在しない場合は新規シート。 ' 呼び出し例(2) Dim wsSheet As Worksheet Set wsSheet = SelectSheet("シートA") ' → 常に wsSheet = ThisWorkbook.Sheets("シートA") となる。 ' ただし、「シートA」が存在すればそのシート、 ' 存在しない場合は新規シート。 '* '* シートがなければ作成orコピーする関数 '* (有効なsBaseSheetが指定された場合、そのシートをコピーして名称をsSheetNameとする) '* '* [in] sSheetName:取得するシートの名称 '* sBaseSheet:コピー元 ※省略可 '* [out] SelectSheet:取得したシート '* '* 使用関数:CheckExistSheet '* Private Function SelectSheet(ByVal sSheetName As String, Optional ByVal sBaseSheetName As String = "") As Worksheet ' 変数 Dim wsSheet As Worksheet Dim bFlag As Boolean ' シートの存在を調べ、なければ作る If CheckExistSheet(sSheetName) <> True Then ' 存在しないので、作成orコピーする If sBaseSheetName = "" Or CheckExistSheet(sBaseSheetName) = False Then ' コピー元シートを指定されていないor無効なシートなので新規作成 ThisWorkbook.Sheets.Add After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Else ' コピー元シートをコピーして名称変更 ' ※Copyの引数はコピーした後の位置。After:=(Before:=)を用いて指定する With ThisWorkbook.Sheets(sBaseSheetName).Copy(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ActiveSheet.Name = sSheetName End With End If End If '指定された名称のシートを返す Set SelectSheet = ThisWorkbook.Sheets(sSheetName) End Function '* '* シートが存在するか調べる関数 '* '* [in] sSheetName:存在を調べるシート名 '* [out] CheckExistSheet:結果(存在すればTrue、しなければFalse) '* Private Function CheckExistSheet(ByVal sSheetName As String) As Boolean ' 変数 Dim wsSheet As Worksheet ' 戻り値のデフォルト値をFalseに CheckExistSheet = False For Each wsSheet In Worksheets If wsSheet.Name = sSheetName Then ' シートが存在すれば戻り値をTrueにしてループから抜ける CheckExistSheet = True Exit For End If Next wsSheet End Function
日時を扱ういろいろ PHP版
PHPでの日時の扱いについてですが、INT型のUnixタイムスタンプを用いるのが基本です。
例えば現在の日時を「YYYY-MM-DD hh:mm:ss」という形で求める場合、
$datetime = date("Y-m-d H:i:s", time())
とします。
time()で現在のUnixタイムスタンプを取得し、それを指定のフォーマットに変換しています。
フォーマットした日付文字列をつくる
date(フォーマット,Unixタイムスタンプ)を使用。
上記の説明のとおりですが、現在日時の場合はUnixタイムスタンプの指定を省略してもOKです。
フォーマットのパラメータに使用できる文字はマニュアルの通りですが、
個人的によく使用するものをこちらに挙げておきます。
Y | 年(西暦4桁) |
y | 年(西暦下2桁) |
m | 月(先頭ゼロ埋めする) |
n | 月(先頭ゼロ埋めしない) |
d | 日(先頭ゼロ埋めする) |
j | 日(先頭ゼロ埋めしない) |
N | 曜日(1:月曜 2:火曜 … 7:日曜) |
w | 曜日(0:日曜 1:月曜 … 6:土曜) |
t | 該当する月の日数(=末日) |
H | 時(24時間方式) |
i | 分 |
s | 秒 |
「t」が意外と便利。末日を取りたければ「Y-m-t」のフォーマットでOK。便利。
各要素をバラバラに取りたい場合であればgetdateが使いやすい場面もあるかも。
また、月だけ・日だけ…などを文字列ではなく数値として取りたい場合にはidateなんて関数もあります。
どうしても型を意識しなければならない場合には使える…かも?
(見慣れない関数入れるよりも、dateで取ってキャストした方が可読性高い気がしますが…)
日付からUnixタイムスタンプを取得する(1)
mktime(時, 分, 秒, 月, 日, 年, サマータイムかどうか) を使用するパターン。
引数は右から順に省略可能で、すべて省略した場合は現在時刻となります。
ただし、現在時刻を求める場合はtime()を使用すべきです。
(PHP5.1以降では、引数をすべて省略した場合noticeが出るようです)
引数はすべて整数で渡します。
範囲外の数字はPHPで判断して処理してくれます。
例えば月に13を指定すると繰り越して「1月」としてくれたり、
月に1、日に0を指定すると「12月31日」としてくれたりします。
// 例 $date = date("Y-m-d", mktime(0, 0, 0, 11, 31, 2013)); // 2013-12-01
日付からUnixタイムスタンプを取得する(2)
strtotime(日付文字列) を使用するパターン。
日付文字列には色々なものを入れられます。
// 例 $timestamp = strtotime("now"); // time()で充分ですが… $timestamp = strtotime("2013-11-01"); // 普通の日付 $timestamp = strtotime("01 November 2013"); // 海外式 $timestamp = strtotime("-1 day"); // 昨日の日付 $timestamp = strtotime("2013-11-01 -1 day"); // 2013/11/01の前日 $timestamp = strtotime("+1 week"); // 1週間後 $timestamp = strtotime("+1 month"); // 1ヶ月後 $timestamp = strtotime("+1 year"); // 1年後 $timestamp = strtotime("+1 hour"); // 1時間後 $timestamp = strtotime("+1 minute"); // 1分後 $timestamp = strtotime("+1 second"); // 1秒後 $timestamp = strtotime("next Monday"); // 次の月曜日 $timestamp = strtotime("last Sunday"); // 前の日曜日
…何でもアリですね。これがPHPの楽しいところでも便利なところでも怖いところでもあるというか…
「1日後」「1時間後」ならともかく、「1ヶ月後」なんかは意図した日付になるとは限らないので注意が必要。
日付が有効かどうかを調べる
checkdate(月, 日, 年) を使用。
有効な場合はTRUE、無効な場合はFALSEが返ってくる。
// 例 $result = checkdate(12,31,2013) // true $result = checkdate(12,32,2013) // false
日時を扱ういろいろ MySQL版
※MySQLを前提として書いています
現在日時の取得
-- YYYY-MM-DD hh:mm:ss SELECT NOW()
月の最終日の取得
-- 2013-06-10 を渡すと 2013-06-30 SELECT LAST_DAY(日付)
フォーマットして取得
SELECT DATE_FORMAT(日付, フォーマット)
フォーマット部分
- %Y 西暦年(4桁)
- %y 西暦年(2桁)
- %m 月(2桁ゼロ詰め)
- %c 月(ゼロ詰めなし)
- %d 日(2桁ゼロ詰め)
- %j 日(ゼロ詰めなし)
- %W 曜日(英語)
- %w 曜日(日:0、月:1、…、土:6)
- %k 時(24時間表記)
- %i 分
- %s 秒
フォーマット例:「2013/06/09」→ %Y/%m/%d
ここまで覚えておけば組み合わせで大体のことはできそうです
まあ覚えられないので書いてるんですけどね。
以下は蛇足。
現在日時の取得
SELECT current_timestamp -- YYYY-MM-DD hh:mm:ss ,current_date -- YYYY-MM-DD ,CURDATE() -- YYYY-MM-DD ,current_time -- hh:mm:ss ,CURTIME() -- hh:mm:ss
曜日の取得
SELECT WEEKDAY(日付) -- 月:0、火:1、…、日:6 ,DAYOFWEEK(日付) -- 日:1、月:2、…、土:7
これに加え、フォーマット指定の「%w」が「日:0、月:1、…、土:6」と
すべて戻り値が異なるので、
好みの方法で統一しておく方がリスクが少ない。
尚、PHPでのフォーマットにも
- 月:1、火:2、…、日:7
- 日:0、月:1、…、土:6
と2通りあり、合わせる必要がある場合はフォーマット指定を利用するとよい。
すごくまぎらわしい。
マルチバイト文字列関数の罠
ある時、CSVのデータをインポートする処理で不具合が発生。
「2件のデータをまとめてインポートすると1件のみ失敗して、
成功したデータを除いて再度その1件のみをインポートすると成功した」
いやいやそんなアホな。
色々調べた結果、この結論に至りました。
「マルチバイト文字列関数を使用するときは文字コードを指定した方が安全かもしれない」
…うーん…。
今でも微妙に納得いかないのですが。
ちなみに、当該箇所の処理はこうです。
- CSVを取り込む
- 改行区切りで1セットのデータに分割
- カンマ区切りでデータ内の各項目に分割して文字配列にする
- ある項目を全角数字から半角数字に変換(mb_convert_kanaを使用)
- 他項目も含めいろいろ調整したデータをDBに入れたりいろいろする
…という感じなのですが、どうやら全角→半角にする過程で誤変換されたようです。
有効な半角数字にならず、データ不備としてはじかれた、と。
何故か、その2件のデータを一緒に取り込んだ時のみ、片方だけ。
なので特定の文字列が誤変換されるというわけではないですし、問題なく複数レコードが取り込めることだってありました。
(開発時にも、もちろんテストしましたし…)
ともかく、
>全角数字から半角数字に変換(mb_convert_kanaを使用)
これを
$hoge = mb_convert_kana($hoge, "a")
と書いていたのを
$hoge = mb_convert_kana($hoge, "a", "UTF-8")
こうすると改善されました。
誤変換しない場合の方が圧倒的に多いというのがネックですね…。
テストで気付きにくいですし。
他のマルチバイト系関数を使用する際も、文字コードが指定できるものはした方が安全かもしれません。
関係ありそうでしかもちょくちょく使う関数一覧
(リンクは公式マニュアル)
- mb_convert_kana
- 半角・全角間の変換を行う
- mb_strstr / mb_stristr / mb_strpos / mb_strrpos / mb_stripos / mb_strripos
- 文字列Aの中に文字列Bが最初(または最後)に現れる位置を探す
- mb_strlen
- 文字列の文字数を返す
- mb_substr
- 文字列の一部を返す
- mb_strtolower / mb_strtoupper
- 文字列をすべて小文字(または大文字)にする
文字列のトリム(左端・右端にある特定の文字列を削除 )
以下MySQLで確認していますが、Oracle・PostgreSQLでも同じ書き方である模様。
例えば都道府県欄に「○○県」と入っており「県」は除いて表示したい場合など、は以下のようにします。
SELECT TRIM(オプション 削除する文字列 FROM フィールド名) FROM テーブル名
オプションは、
両端から削除:BOTH (※省略可)
左端から削除:LEADING
右端から削除:TRAILING
となります。
-- 「○○県」から右端の「県」を取り除いて表示 SELECT TRIM(TRAILING '県' FROM `pref`) FROM `pref_master`
【ネタ】手作りソート
何やら「A1:A10に入力された数値データを昇順に並び変えてB1:B10に格納せよ」という学校の課題的なお題を頂いたので作ってみました。
もちろんソート機能は使用禁止です。
(つまり実務で使う意味は皆無のネタコードです)
Option Explicit 'ソート範囲 Const SORT_RANGE As String = "A1:A10" Const RESULT_RANGE As String = "B1:B10" 'ソート個数 Const SORT_COUNT As Long = 10 '難しいことを考えるのは面倒なので、 '一番小さい数字を見つけたら一番上に入れて、 '残りの数字からさらに一番小さい数字を見つけたら…を繰り返すソートにします。 Public Sub Main() 'ワークシートを定義 Dim wsSheet As Worksheet Set wsSheet = ThisWorkbook.Sheets("Sheet1") 'ソート範囲内の数字を配列にぶちこむ 'この方法だと1列しかなくても二次元配列になる。 '※(1,1)~(10,1) Dim lNumList As Variant lNumList = wsSheet.Range(SORT_RANGE) '一番小さい数字を見つけて並べ替えるのを繰り返す Dim i As Long Dim j As Long Dim lTmpNum As Long Dim lTmpAdd As Long For i = 1 To SORT_COUNT - 1 '一番小さい数字を探す lTmpNum = lNumList(i, 1) '仮数字に先頭の数字をセット lTmpAdd = i '仮数字の座標をセット For j = i + 1 To SORT_COUNT If lNumList(j, 1) < lTmpNum Then '仮数字よりも小さいものを見つけたらそっちを仮数字に lTmpNum = lNumList(j, 1) '元の一番小さい数字と新しい一番小さい数字を入れ替える lNumList(j, 1) = lNumList(lTmpAdd, 1) lNumList(lTmpAdd, 1) = lTmpNum End If Next 'この時点でlTmpNumに入っているものが一番小さい 'それ以外でまた並べ替える Next ' 結果をワークシートに書き込む wsSheet.Range(RESULT_RANGE) = lNumList End Sub
ワークシートの最大行数、最大列数を取得
最大行数は Rows.Count
最大列数は Columns.Count
で取得できます。
' 使用例(1):「Sheet1」シートの全セルのデータや書式設定を削除 With ThisWorkbook.Sheets("Sheet1") Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Clear End With ' 使用例(2):可変値が範囲外にならないようなエラーチェック Dim lRow As Long Dim lCol As Long lRow = ~~ lCol = ~~ If (0 < lRow And lRow <= Rows.Count) And (0 < lCol And lCol <= Columns.Count) Then ThisWorkbook.Sheets("Sheet1").Cells(lRow, lCol).Value = "hoge" Else MsgBox ("範囲外やで") End If
ボタンひとつで簡易grep
何度もgrepをかけるとき、いちいちウインドウ出てくることや
検索フォルダが「今開いているファイルの位置」になるのが非常に鬱陶しいので、
「あらかじめ指定されたフォルダ以下にて今選択している文字列をgrep」するマクロを作りました。
ショートカットキー割り当てておくと非常に楽です。
私は折り返し位置を81文字に設定しているので、
grep後に調整しています(コメントアウトしている部分)。
//grepフォルダ $curdir = "C:\\hoge\\hage\\"; //grep対象ファイル(拡張子とか) $filter = "*.c;*.cpp;*.h"; //選択範囲の文字列を取得 $word = gettext(seltopx,seltopy,selendx,selendy); //grep grep $word,$filter,$curdir,icon,subdir; ////折り返し位置変更 //config "w2000";
…という記事を旧ブログから移行してきたのですが、今読むと「ねぇよそんな作業」って感じです…。
かつては何故かすんげえ頻度ですんげえ大量にgrepする現場にいたので…。
VBAでセルの結合
セルを結合するには、MeageCellsプロパティを用います。
' A1~C3のセルを結合 Range("A1:C3").MergeCells = True '現在選択している範囲を結合 Selection.MergeCells = True
ここで、結合したいセルのうち2つ以上にデータが入っていると、警告が表示されます。
これを無視して(表示させずに)結合したい場合は、
あらかじめ「Application.DisplayAlerts」をFalseに設定しておけばOKです。
Application.DisplayAlerts = False Selection.MergeCells = True Application.DisplayAlerts = True
また、逆にセルの結合を解除したい場合は、もちろんMergeCellsプロパティをFalseに設定すればOKです。