スポンサードリンク
無料ブログ作成サービス JUGEM
人気ブログランキング くつろぐ ブログ王
■ Mobile
qrcode
文字置換関数SUBSTITUTE
EXCELの関数(VBAではない)

SUBSTITUTE関数

文字列中の指定した文字を他の文字に置換する。

SUBSTITUTE(文字列, 検索文字列,置換文字列, 置換対象)

文字列 :置き換える文字を含む文字列またセル
検索文字列:置換前の文字列(ターゲット)
置換文字列:置換後の文字列
置換対象 :置き換えたい文字が複数ある場合に、左から何番目かを指定する
(省略すると、置換したい文字の全てが置換される)




【応用例・・文字列の中の最も右側にある特定の文字の位置の取得】

(例)文字列aa*bbb*cccc*ddで最も右側の「*」の文字位置は12となる。

セルR2C2に「aa*bbb*cccc*dd」が入っているとして(「#」は文字列に存在しない前提とする)
=FIND("#", SUBSTITUTE(R2C2, "*", "#", LEN(R2C2) - LEN(SUBSTITUTE(R2C2, "*", ""))))

概要:左側のSUBSTITUTEの置換対象は左から見て最後の「*」を指定している。(「*」の個数算出により)
これにより、最も右側の「*」は「#」に置換される。
FIND関数で「#」の位置を見つける。
ここでの「#」は便宜的に利用。確実に利用しない文字を使用するように。

| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
VBA 動的配列について
○固定配列の宣言例
要素数が既知の場合…

Dim strAr_name(3) As String

後に要素数を追加できない。


○動的配列の宣言
要素数が不明の場合…

・要素数を決めずに宣言
Dim strAr_name()

・後に再定義することができる
ReDim strAr_name(3)

strAr_name(0)="日本太郎"
strAr_name(1)="大和花子"

ReDim strAr_name(5)
更に再定義を繰り返すことはできるが
セットされていた値は保持されない(この例では添え字0〜2の要素の値は初期化される)

? strAr_name(0) ⇒ ""

「Preserve」を付けて
ReDim Preserve strAr_name(5)

と再定義を記述すれば、元の状態を維持したまま増設」される

前の例では
? strAr_name(0) ⇒ "日本太郎"
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
URL形式の文字列に勝手にハイパーリンクが設定される機能を解除する
リンク設定する気がないのに勝手にリンクされてウザい場面が多々ある。

その初期設定を解除するには、
「ファイル」→「オプション」
→「文章校正」→「オートコレクトのオプション(A)」
→「入力オートフォーマット」のタブ
→「インターネットとネットワークのアドレスをハイパーリンクに変更する」のチェックを外す。
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
数値が0(ゼロ)を表示させない方法
ワークシート上のセルの数値が0の場合に0を表示させない設定方法。

1.ワークシート上・すべての 0 を非表示にしたい場合
[オプション]ダイアログボックス→[表示]タブ→「ウィンドウオプション」の[ゼロ値]チェックボックスをオフ

2.特定のセルに対して 0 を非表示にする方法
(1)当該セルをselect
(2)[セルの書式設定]→[表示形式]→[分類]→「ユーザー定義」→[種類]ボックスに「G/標準;G/標準;」と入力。
(設定内容:プラス・マイナスの数値は標準形式、0 は非表示)
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
Excel VBA ファイル操作
◎読み出しRead
Dim noRd As Integer
Dim bufr As String
Dim tmpAr As Variant

noRd = FreeFile
Open inFile For Input As noRd
Do Until EOF(noRd)
Line Input #noRd, bufr
tmpAr = Split(buf, ",")

loop
Close noRd


◎書き込みWrite
Dim noWt As Integer
Dim bufw As String

noWt = FreeFile
Open "abc.txt" For Output As noWt
Print #noWt, bufw
Close noWt
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
Excel VBA 実行中のプログラムを停止させる方法
実行中のマクロ(プログラム)を停止させるには、

「Ctrl」キーを押しながら、「Pause」又は、「Break」キーを押下する。
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
Excel、設定いろいろ
R1C1形式設定方法
「ファイル」→「オプション」→「数式」→「数式の処理」

開発のタブを有効にする
「ファイル」→「オプション」→「数式」→「数式の処理」リボンのユーザー設定」
→コマンドの選択で「基本的なコマンド」、リボンのユーザー設定で「メインタブ」
開発にチェックONして「OK」。

「開発」→「マクロのセキュリティ」にてマクロの制限設定できる。

マクロのボタンをクイックアクセスツールバーに追加する方法。
(「表示」→「マクロ」より素早く操作可能化)
「ファイル」→「オプション」→「クイックアクセスツールバー」→コマンド一覧から「マクロ」選択するとマクロ一覧表示される。
→マクロを選択し「追加」、「変更」でアイコンイメージ選択して「OK」

VBAで変数宣言強制(Option Explicit)の設定方法
VBEの画面で「ツール」→「オプション」→「変数の宣言を強制する」にチェックを入れる。

セ仮叛瀋
コンパイルや実行時「ユーザー定義型は定義されていません」エラー表示する場合。
VBEで「ツール」→「参照設定」の「Nicrosoft Scripting Runtime」にチェック入れる。

Ε廛蹈哀薀猜埆(デザイン)モードと実行(通常の)モードの切り替え。
VBE画面で「実行」→デザインモードorデザインモードの終了、いずれか選択。

Г燭箸┐弌46-1」とセルに入力すると勝手に日付文字列と解釈されてしまう。
この場合、「ファイル」→「オプション」→「数式」→エラーチェックルールの2桁の年が含まれているセル(y)のチェックを外す。
また、そういう「セルの書式」=文字列に。

┛刷プレビューを以前のバージョンのようにする。
左上の「▽」(クイックアクセスツールバー)→「クイックアクセスツールバーのユーザー設定」表示
→「その他のコマンド」→「コマンドの選択」で「すべてのコマンド」
→一覧の中から「印刷プレビュー(全画面表示)」を追加。
※この時、右側のクイックアクセスツールバーのユーザー設定が「すべてのドキュメントに適用」が選択されている事を確認。
→右下の「OK」押下。
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
「worksheetfunctionクラスのmatchプロパティを取得できません」(実行時エラー’1004’)が出る
Application.WorksheetFunction.Match(stryyyymm, Range(Cells(lngMin, 10), Cells(lngMax, 10)), 0)
EXCEL VBA のmatchで検索していて
「worksheetfunctionクラスのmatchプロパティを取得できません」(実行時エラー'1004')のメッセージが出る

例えば、string型のstryyyymmで検索する時に、シート側の検索対象列のデータが数値型・・
型が食い違うと同メッセージが出ることがある。
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
ユーザーフォームのコピー方法
VBAで利用のユーザーフォームを意外に簡単にコピーできないなぁと不便に思っていたが、この方法なら簡単にできそう。

1.目的のブックとは別に、一時的に新しいブックを作成しておく。

2.VBEでコピーしたいユーザーフォーム名を新しいブックにドラッグする。
(新しいブック側に当該ユーザーフォームがコピーされる)

3.同じ名前では不都合があるので、新しいブックにコピーされたユーザーフォームの名前を変える。

4.名前を変えたユーザーフォームを目的のブックのVBEへコピーする。

5.不要になった新しいブックを削除する。
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
excel2010、VBA動かそうとして「メモリ不足です。完全に表示できません。」のメッセージ
「メモリ不足です。完全に表示できません。」

ガーン。ショック!
そして、あせった!
メモリ不足?

どうやら、
コントロール ツールボックスのコンボボックスやリストボックスで
ListFillRangeプロパティで参照しているシートが別のシートで、コンボ 、リストボックスが配置されているシートのズームとは異なる倍率で、100% 以外に設定されている場合に起こるらしい。

そういえば、リスト用に用意したページを70%にしてたからのようだ。
それを100%に戻したら、例のエラーが出なくなった。ホッ・・。
| 愛アース | 備忘録・EXCEL | comments(0) | trackbacks(0) | pookmark |
4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58 61 64 67 70 73 76 79 82 85 88 91 94 97 100 103 106 109 112 115 118 121 124 127 130 133 136 139 142 145 148 151 154 157 160 163 166 169 172 175 178 181 184 187 190 193 196 199 202 205 208 211 214 217 220 223 226 229 232 235 238 241 244 247 250 253 256 259 262 265 268 271 274