HELPの消し方

勝手にHELPが表示される

f:id:ExcelLover:20170902150414j:plain

ExcelのHELP、関数の引数を調べるのにとても便利でよく使います。
が、このHELPウィンドウ、呼び出してもいないのに(※正確には呼び出したつもりもない)勝手に表示されてイラッとくることも多々あります。
なんで出てくるかというと HELPを呼び出すショートカットキー「F1」をクリック しているからです。

HELPの消し方

F1で表示されたHELPを「×」をクリックして消すのは、恐らく今右手はキーボードの上にあると思われるので、マウスに手を伸ばさないといけないことになり手間やストレスが余計にかかっていまします。
ショートカットキーで消すのが早く・ストレスレスで消せます。

・ Alt + F4
・ Ctrl + F1(未確認ですがExcel2003まではAlt + F4ではなくこちら)
・ Alt + Space → c (Altを押しながらスペースを押して、メニュー表示されたら C )
f:id:ExcelLover:20170902150434j:plain

個人的にはF4が遠いので Alt + Space → c を使っています。
この2つのショートカットキーはHELP以外の、Excel以外のウィンドウも消せますので何かと重宝します。

※世の中にはHELPのショートカットキー自体を潰す方法もあるようですね

Excel HELP 無効 - Google 検索

月末日付の求め方・EOMONTH 関数

月末日付の求め方

請求書の締切日などを記載するために月末の日付を算出しないといけないことは多々あると思われます。
今までは次のような数式を使って求めていました。

= Date(2017,8+1,1)-1

f:id:ExcelLover:20170902141015j:plain

これは求めたい月末の翌日1日を作成してから1日マイナスして月末を求める方法です。

しかしExcelにはそれ専用の便利な関数が用意されていました。
それがEOMONTH 関数です。

EOMONTH 関数の使い方

EOMONTH(開始日、月)

開始日・・・必ず指定します。 起算日を表す日付を指定します。 日付は、DATE 関数を使って入力するか、他の数式または他の関数の結果を指定します。 たとえば、2008 年 5 月 23 日を入力する場合は、DATE(2008,5,23) を使用します。 日付を文字列として入力した場合、エラーが発生することがあります。

月・・・必ず指定します。 開始日から起算した月数を指定します。 “月” に正の数を指定すると起算日より後の日付を返し、負の数を指定すると起算日より前の日付を返します。
メモ 月に整数以外の値を指定すると、小数点以下は切り捨てられます。

たとえばEOMONTH(“2017/8/30",1)と入力すると2017年8月の1ヶ月後の月末、つまり2017年9月30日が表示されます。
「月」に0を入力すると「開始日」の月の月末、マイナスの数値を入力すると「月」の数字だけ遡った月の月末が表示されます。

EOMONTH(“2017/8/30",0)→2017/8/31
EOMONTH("2017/8/30",-1)→2017/7/31 f:id:ExcelLover:20170902141214j:plain

Range.Heightプロパティは読み取り専用?

Heightプロパティで設定できない

Range(“A1”).Height = 20
セルの高さを設定するために上記のようなコードを書いたところ

オブジェクトが必要です

というエラーが出て設定できない事態に遭遇しました。

Helpで調べたところ

Returns or sets a Variant value that represents the height, in points, of the range.

と記載があり、読み書き両方できるように読めます。

読み書き可能でRangeオブジェクトも指定しているのに何故エラーが?

Helpとオブジェクトブラウザで記述が異なる

ところがオブジェクトブラウザで調べてみると

 読み取り専用

の文字が。

実際設定できないところを見るとオブジェクトブラウザの方が正しいと思われるけど、WebHelpなんだから正しい方に修正しておいてほしいですね。
(※英語を正しく読めてない可能性も大)

RowHeightプロパティで読み書き可能

Heightで設定ができないとなるとどうやって設定するかというと

RowHeightプロパティ

Range(“A1”).RowHeight = 20

で高さ設定。

Debug.Print Range(“A1”).RowHeight

で高さを取得できます。

ピボットテーブルの範囲の広げ方

本日頂きましたご質問は 「ピボットテーブルを作った後で対象のシートに列を追加したけど、 追加した列がピボットテーブルに表示されない。 どうしたら表示される?」

範囲の広げ方

  • 1.一度ピボットテーブルを削除して再度作成する
  • 2.データソースの変更を行う
  • 3.元データをテーブル化しておいて、更新をかける

1の方法は一度削除して再作成しますので最新の状態が反映されます。設定をいじりすぎてわけがわからなくなった時も初期化されますので下手に悩むより省力化です。

2の方法は現状のピボットテーブルはそのままで範囲だけ変更できます。

ピボットテーブルの範囲内をどこでも良いので選択してから
分析 → データソースの変更

f:id:ExcelLover:20170821232902j:plain

元データの範囲が表示されるのでお好みの範囲を設定

f:id:ExcelLover:20170821232932j:plain

無事に追加した列が表示されました。

3の方法は前段階としてピボットテーブルを作成する前に元データをテーブルに変換してからピボットテーブルを作成しておく必要があります。

2の方法と同じようにピボットテーブルの範囲内をどこでも良いので選択してから 分析 → 更新

f:id:ExcelLover:20170821232954j:plain

以上で追加した列が反映されます。

ピボットテーブルで行項目をすべての行に表示する

ピボットテーブルを取り敢えずつくるとこのように表示されています。 f:id:ExcelLover:20170820112724j:plain

スッキリしていて見やすいのですが、ここから更に関数を使ってデータの抽出を行う場合、行ラベルがすべての行に表示されていない事がネックになります。

行ラベルをすべての行に表示する

このように表示されると関数でのデータ抽出が行いやすくなります。 f:id:ExcelLover:20170820112746j:plain

フィールドの設定

f:id:ExcelLover:20170820112821j:plain

「小計とフィルター」タブで「なし」を選択。 小計は関数で集計するときには邪魔なので外します。

f:id:ExcelLover:20170820112841j:plain

「レイアウトと印刷」タブで「アイテムのラベルを表示形式で表示する」を選択し 「アイテムのラベルを繰り返す」にチェックを入れる。

f:id:ExcelLover:20170820112858j:plain

※ファイル形式が「xls」だとグレーアウトしていてチェックを入れれません。
※リボンのピボットテーブル-デザインからなら変更できます。

関数で数式を文字列として検索する方法

こんな感じの表を頂きました。

f:id:ExcelLover:20170803203600j:plain

この表には期首残の項目が無く、期末残の数式の中に埋め込まれているという恐ろしい作りになっています。 期首残の数値が必要だったため数式から抜き出そうと思いMid関数と Find関数 で抜き出そうとしたのですが、 Find関数では数式の結果に対してのみ検索可能なため上手く行きませんでした。

Formulatext関数で文字列として認識

Formulatext関数を使うと関数の結果ではなく数式自体を返してくれます。

上記の例でいうと449という結果ではなく=1000-SUM(B3:M3)という文字列に対して検索をかけることが出来ます。

以下のように利用して目的を達成しました。

f:id:ExcelLover:20170803203617j:plain

=MID(FORMULATEXT(N3),2,FIND(“-”,FORMULATEXT(N3))-2)

※実際には = を置換して文字列にしてから普通にMid、Find関数だけで抜き出したんだけどね

ユーザーフォームに動的にボタン等を追加する

今までユーザーフォームに配置するコマンドボタン等のコントロール類は ユーザーフォームの作成時に全てきっちり配置していました。

というかその方法しか知らなかった。

ところがコントロール類はユーザーフォームのイベントと連動して動的に、 しかも簡単に配置できる事を知ったのその備忘録。

まずはユーザーフォームを作成してコマンドボタンを1つ設置。

f:id:ExcelLover:20170730155019j:plain

このボタンをクリックする度にコマンドボタンを追加するようにしました。

f:id:ExcelLover:20170730155052j:plain

ユーザーフォームのコードは以下の通り。

Option Explicit
Dim myCol As Collection
Dim myCls As Class1

Private Sub UserForm_Initialize()
    Set myCol = New Collection
End Sub

Private Sub CommandButton0_Click()
    Dim myCmdBtn As MSForms.CommandButton
    
    Set myCmdBtn = UserForm1.Controls.Add("Forms.CommandButton.1", "CommandButton" & myCol.Count + 1, True)
    With myCmdBtn
        .Left = 10
        .Top = 10 + 20 * myCol.Count
        .Width = 150
        .Caption = "CommandButton" & myCol.Count + 1
    End With
    Set myCls = New Class1
    myCls.setBtn myCmdBtn
    myCol.Add myCls
End Sub

この内、動的にボタンを追加しているのは Set myCmdBtn = UserForm1.Controls.Add(“Forms.CommandButton.1”, “CommandButton” & myCol.Count + 1, True) の部分です。 追加したいフォームのコントロールコレクションに、追加したいコントロールを指定してやれば、フォームにコントロールが追加されます。

第1引数のコントロールを指定する文字列は以下の通り。

作成するコントロール 使用する ID
チェック ボックス (CheckBox) Forms.CheckBox.1
コンボ ボックス (ComboBox) Forms.ComboBox.1
コマンド ボタン (CommandButton) Forms.CommandButton.1
フレーム (Frame) Forms.Frame.1
イメージ (Image) Forms.Image.1
ラベル (Label) Forms.Label.1
リスト ボックス (ListBox) Forms.ListBox.1
マルチ ページ (MultiPage) Forms.MultiPage.1
オプション ボタン (OptionButton) Forms.OptionButton.1
スクロール バー (ScrollBar) Forms.ScrollBar.1
スピン ボタン (SpinButton) Forms.SpinButton.1
タブ ストリップ (TabStrip) Forms.TabStrip.1
テキスト ボックス (TextBox) Forms.TextBox.1
トグル ボタン (ToggleButton) Forms.ToggleButton.1

イベントの登録

動的に配置したボタンにはイベントが登録されていないので、このままではただの飾りになってしまう。

クラスを作成して以下のようなコードを登録

クラスを作成して以下のようなコードを登録
Option Explicit
Private WithEvents myCmdBtn As MSForms.CommandButton

Private Sub myCmdBtn_Click()
    MsgBox myCmdBtn.Caption
End Sub

Sub setBtn(Cmdbtn As MSForms.CommandButton)
    Set myCmdBtn = Cmdbtn
End Sub

witheventsキーワードを付けて変数を宣言すると、 変数の型でしているコントロールのイベントを認識することが出来ます。 この場合はCommandbuttonを指定しているのでコマンドボタンの各種イベントを 認識するクラスが作成されます。

参考記事

久しぶりに読み返してみたら、昔は読み飛ばしたであろう今回の内容を発見した。

かんたんプログラミング Excel2003 VBA コントロール・関数編

かんたんプログラミング Excel2003 VBA コントロール・関数編

イベントの共通化について。 thom.hateblo.jp