VBAで変数の取扱い方を教えてください。（初心者です）

質問

現在アクティブになっているセルに、下記のようにリストＡというブックのシートＡの任意のセルの式（文字）を取得したいと考えています。
下記ではR22C2となっていますが、この部分に任意のセルを取り込めるように変数を使いたいのですが、どのように変数を入れ込めばよいのか分かりません。（Ｒ１Ｃ１形式でなくＡ１形式でも構わないのですが）

ActiveCell.FormulaR1C1 = "='[リストA.xls]シートA '!R22C2"

また、取得先のセルが空っぽの時の表示が０になってしまうのですが、無表示にすることは可能でしょうか？
初歩的な質問かも知れませんがよろしくお願いいたします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

＞リストＡというブックのシートＡの任意のセルの式（文字）を取得したいと考えています。

たぶん、ブックのシートの値を取り出すという意味だと思います。
このコードの欠点は、一度、セルに式を置いて、Excel側のリンク機能を使わないといけないことですね。式を置かずに、取り出す方法もありますが、それはご要望次第です。また、この類のコードは、ユーザー定義関数に変換できません。以下のコードは、A1方式でも、R1C1方式でも、どちらでも可能です。

また、取得先のセルが空っぽ(Empty)と「０」とを区別できませんので、「０」の時は、消去するようにしました。

なお、ブック名が見つからないときは、メッセージが出ますが、シート名が違うときは、Excelからダイアログが出ます。

Sub GetDatafromBook()
'他のブックから値を取り出す式の入力

Dim myRow As Variant, myCol As Variant, FileName As String
Dim SheetName As String, ModeA1 As String

　FileName = "リストＡ"　　'ファイル名
　SheetName = "シートＡ"　　'シート名
　myRow = 22　　'行数
　myCol = 2　　　'列数
　ModeA1 = ""　'A1方式、こちらに入れると、こちらが優先される。

　If Len(Dir(FileName)) = 0 Then MsgBox "ブック名が違います。", 64: Exit Sub
　If Len(ModeA1) = 0 Then
　'R1方式
　ActiveCell.FormulaR1C1 = _
　"='[" & FileName & "]" & SheetName & "'!R" & myRow & "C" & myCol

　Else
　'A1方式
　ActiveCell.FormulaLocal = _
　"='[" & FileName & "]" & SheetName & "'!" & ModeA1

　End If
　'式が０の場合は、中身が削除され。(0とEmtpyの区別はない)
　If ActiveCell.Value = 0 Then ActiveCell.ClearContents
　'値に変換
　 'ActiveCell.Value = ActiveCell.Value
End Sub

お礼

ありがとうございました。
みなさんのご回答を元に悩みが解決しました。
詳しく書いていただきとても参考になりました。

回答No.2

hana-hana3

Dim R
Dim C

R = 1
C = 1

ActiveCell.FormulaR1C1 = "='[リストA.xls]シートA '!R" & R & "C" & C

'空白の時に０表示を消す場合
ActiveCell.FormulaR1C1 = "=IF('[リストA.xls]シートA '!R" & R & "C" & C & "="""","""",'[リストA.xls]シートA '!R" & R & "C" & C & ")"

'Ａ１型式
Dim Cel
Cel = "A1"

ActiveCell.FormulaR1C1 = "='[リストA.xls]シートA '!" & Cel

お礼

ありがとうございました。
みなさんのご回答を元に悩みが解決しました。

回答No.1

papayuka

関数式の中で変数化したい部分を & でつなげればOKです。

Sub Test1()
　For i = 1 To 10
　　ActiveCell.Value = "='[リストA.xls]シートA '!B" & i
　　ActiveCell.Offset(1, 0).Activate
　Next i
End Sub

ちょっと解りづらいと思いますが、下記は IF で "" の時に "" にしてます。
"" を入れるのは """" のようにします。

Sub Test2()
　For i = 1 To 10
　　ActiveCell.Value = "=IF('[リストA.xls]シートA '!B" & i & _
　　　　　　　　　　　 "="""","""",'[リストA.xls]シートA '!B" & i & ")"
　　ActiveCell.Offset(1, 0).Activate
　Next i
End Sub

お礼

ありがとうございました。
みなさんのご回答を元に悩みが解決しました。

補足

早速のご回答ありがとうございました。
ご提示頂いたプログラムで対処できそうです。
ところでFormulaR1C1の場合、相対位置を示すため変数が2つ必要ですがこのような場合はどのように表記するのでしょうか？
それと折角ご回答頂いたのに恐縮ですが後のプログラムは意味がよく理解できませんでした。