image

VLOOKUP、COUNTIF、SUMIFなどの関数は、ワークシート関数と呼ばれます。通常、Excelで事前定義されており、ワークシートですぐに使用できる関数は、ワークシート関数です。 VBAでこれらの関数の背後にあるコードを変更したり表示したりすることはできません。

一方、ユーザー定義関数およびMsgBoxやInputBoxなどのVBAに固有の関数はVBA関数です。私たちは皆、VBAでVBA関数を使用する方法を知っています。しかし、VBAでVLOOKUPを使用したい場合はどうでしょうか。どうすればいいですか？この記事では、まさにそれを探求します。

VBAでのワークシート関数の使用

image

ワークシート関数にアクセスするには、Applicationクラスを使用します。ほとんどすべてのワークシート関数は、Application.WorksheetFunctionクラスにリストされています。そして、ドット演算子を使用して、それらすべてにアクセスできます。

任意のサブに、Application.WorksheetFunctionを記述します。そして、関数の名前を書き始めます。 VBAのインテリセンスは、使用可能な関数の名前を表示します。関数名を選択すると、Excelの他の関数と同様に、変数が要求されます。ただし、変数をVBAで理解できる形式で渡す必要があります。たとえば、範囲A1：A10を渡す場合は、Range（ “A1：A10″）のような範囲オブジェクトとして渡す必要があります。

それでは、いくつかのワークシート関数を使用して、それをよりよく理解しましょう。

VBAでVLOOKUP関数を使用する方法

image

VBAでVLOOKUP関数を使用する方法を示すために、ここにサンプルデータがあります。 VBAを使用して、指定されたログインIDの名前と都市をメッセージボックスに表示する必要があります。データは範囲A1：K26に分散されます。

ALT + F11を押してVBEを開き、モジュールを挿入します。以下のコードを参照してください。

Sub WsFuncitons()

Dim loginID As String

Dim name, city As String

loginID = "AHKJ_1-3357042451"

'Using VLOOKUP function to get name of given id in table

name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)

'Using VLOOKUP function to get city of given id in table

city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)

MsgBox ("Name: " & name & vbLf & "City: " & city)

End Sub

このコードを実行すると、この結果が得られます。

image

VBAが結果をメッセージボックスに出力する速度を確認できます。それでは、コードを調べてみましょう。

どのように機能しますか？

1.

Dim loginID As String

薄暗い名前、文字列としての都市

最初に、VLOOKUP関数によって返された結果を格納するために、文字列型の2つの変数を宣言しました。 VLOOKUPによって返される結果が文字列値になると確信しているため、文字列型変数を使用しました。ワークシート関数が数値、日付、範囲などのタイプの値を返すことが予想される場合は、その種類の変数を使用して結果を格納します。ワークシート関数によって返される値の種類がわからない場合は、バリアント型変数を使用してください。

2.

loginID = “AHKJ_1-3357042451”

次に、loginID変数を使用してルックアップ値を格納しました。ここでは、ハードコードされた値を使用しました。参照も使用できます。例えば。

Range（ “A2″）。Valueを使用して、範囲A2からルックアップ値を動的に更新できます。

3.

name = Application.WorksheetFunction.VLookup（loginID、Range（ “A1：K26″）、2、0）ここでは、VLOOKUP関数を使用して取得します。関数を右揃えにして括弧を開くと、必要な引数が表示されますが、Excelに表示されるほど説明的ではありません。自分で見て。

どの変数をどのように使用する必要があるかを覚えておく必要があります。いつでもワークシートに戻って、記述変数の詳細を確認できます。

ここで、ルックアップ値はArg1です。 Arg1の場合、loginIDを使用します。ルックアップテーブルはArg2です。 Arg2には、Range（ “A1：K26″）を使用しました。 ExcelのようにA2：K26を直接使用しなかったことに注意してください。列のインデックスはArg3です。

Arg3の場合、名前が2番目の列にあるため、2を使用しました。ルックアップタイプはArg4です。 Arg4として0を使用しました。

city = Application.WorksheetFunction.VLookup（loginID、Range（ “A1：K26″）、4、0）

同様に、都市名を取得します。

4.

MsgBox（ “名前：”＆名前＆vbLf＆ “都市：”＆都市）

最後に、Messageboxを使用して名前と都市を印刷します。

VBAでワークシート関数を使用する理由ワークシート関数は膨大な計算能力を備えており、ワークシート関数の能力を無視するのは賢明ではありません。たとえば、データセットの標準偏差が必要で、そのためのコード全体を記述したい場合、数時間かかることがあります。ただし、VBAでワークシート関数STDEV.Pを使用して、一度に計算を行う方法を知っている場合。

Sub GetStdDev()

std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))

End Sub

image

複数のワークシート関数の使用VBA

いくつかの値を取得するためにインデックス一致を使用する必要があるとしましょう。では、VBAで数式をどのように記述しますか。これは私があなたが書くと思うものです：

Sub IndMtch()

Val = Application.WorksheetFunction.Index(result_range, _

Application.WorksheetFunction.Match(lookup_value, _

lookup_range, match_type))

End Sub

これは間違いではありませんが、長いです。複数の関数を使用する正しい方法は、Withブロックを使用することです。以下の例を参照してください：

Sub IndMtch()

With Application.WorksheetFunction

Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type))

val2 = .VLookup(arg1, arg2, arg3)

val4 = .StDev_P(numbers)

End With

End Sub

image

ご覧のとおり、Withブロックを使用して、Application.WorksheetFunctionのプロパティと関数を使用することをVBAに通知しました。だから私はどこでもそれを定義する必要はありません。ドット演算子を使用して、INDEX、MATCH、VLOOKUP、およびSTDEV.P関数にアクセスしました。 End Withステートメントを使用すると、完全修飾関数名を使用せずに関数にアクセスすることはできません。

したがって、VBAで複数のワークシート関数を使用する必要がある場合は、ブロックとともに使用してください。

すべてのワークシート関数がApplication.WorksheetFunctionを介して使用できるわけではありません。一部のワークシート関数は、VBAで直接使用できます。 Application.WorksheetFunctionオブジェクトを使用する必要はありません。

たとえば、文字列、左、右、中央、トリム、オフセットなどの文字数を取得するために使用されるLen（）のような関数。これらの関数はVBAで直接使用できます。これが例です。

Sub GetLen()

Strng = "Hello"

Debug.Print (Len(strng))

End Sub

image

ここでは、Application.WorksheetFunctionオブジェクトを使用せずにLEN関数を使用しました。

同様に、left、right、mid、charなどの他の関数を使用できます。

Sub GetLen()

Strng = "Hello"

Debug.Print (Len(strng))

Debug.Print (left(strng,2))

Debug.Print (right(strng,1))

Debug.Print (Mid(strng, 3, 2))

End Sub

上記のサブを実行すると、次のように返されます：

5

He

o

ll

そうですね、これがVBAでExcelのワークシート関数を使用する方法です。私が十分に説明し、この記事があなたを助けたことを願っています。この記事またはその他のVBAに関連するものについて質問がある場合は、以下のコメントセクションで質問してください。それまでは、以下の他の関連トピックについて読むことができます。

関連記事：

link：/ excel-macros-and-vba-what-is-csng-function-in-excel-vba [ExcelVBAのCSng関数とは] | SCng関数は、任意のデータ型を単精度浮動小数点数に変換するVBA関数です（「数値である場合」）。私は主にCSng関数を使用して、テキスト形式の数値を実際の数値に変換します。

link：/ vba-how-to-get-text-number-in-reverse-through-vba-in-microsoft-excel [MicrosoftExcelのVBAを介してテキストと番号を逆に取得する方法] | *番号を逆にするVBAではループとmid関数を使用するテキスト。 1234は4321に変換され、「you」は「uoy」に変換されます。これがスニペットです。

link：/ formating-in-vba-format-table-depending-on-number-formats-using-vba-in-microsoft-excel [MicrosoftExcelのVBAを使用してカスタム番号形式でデータをフォーマットする] | Excelの特定の列の数値形式を変更するには、このVBAスニペットを使用します。ワンクリックで指定フォーマットから指定フォーマットまでの数値フォーマットをカバーします。

link：/ Tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [ワークシート変更イベントを使用して変更が行われたときにマクロを実行する] |したがって、シートが更新されるたびにマクロを実行するには、VBAのワークシートイベントを使用します。

link：/ events-in-vba-run-macro-if-any-change-made-on-sheet-range [指定された範囲のシートに変更が加えられた場合はマクロを実行] |指定した範囲の値が変更されたときにマクロコードを実行するには、このVBAコードを使用します。指定された範囲で行われた変更を検出し、イベントを発生させます。

link：/ events-in-vba-simplest-vba-code-to-highlight-current-row-and-column-using [現在の行と列を強調表示する最も簡単なVBAコード] |この小さなVBAスニペットを使用して、シートの現在の行と列を強調表示します。

人気の記事：

link：/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を高めるための50のExcelショートカット] |あなたの仕事をより速くしてください。これらの50のショートカットにより、Excelでの作業がさらに高速になります。

link：/ forms-and-functions-introduction-of-vlookup-function [ExcelのVLOOKUP関数] |これは、さまざまな範囲やシートから値を検索するために使用される、Excelの最も使用され人気のある関数の1つです。

link：/ Tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] |この驚くべき関数を使用して、条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。

ダッシュボードを準備するには、Countif関数が不可欠です。

link：/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法] |これは、ダッシュボードのもう1つの重要な機能です。これは、特定の条件で値を合計するのに役立ちます。