PR

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

「R1C1」形式を「A1」形式に変換

Visual Basic for Applications




_
「え~っと。。。ここ写真ブログだよね?」


とお思いの皆さん。

はなみずは生まれ変わったんです。


今日から真面目に一生懸命勉強します。



早速本題に入りましょう。

テキパキッ

如何せん、コードを書くと行数が増えるので、無駄な前置きは少なめに、と。


今回はタイトル通り「R1C1」形式を「A1」形式に変換するお話です。


ご存知の通り、VBAにおいては「A1」という形で命令する事が少なく、

「R(ROW=行)1」、「C(Column=列)1」という形式を多用します。


多くの場合、この「R1C1」形式で問題なくこなせるのですが、稀に「A1」形式が必要な場合があります。

例えば・・・


・・・思い付きません。

ワークシート関数を使う時かな?


それ以外でも必要性が出てきたので、はなみずは以下の対応をした事があります。

また使うかも知れないのでアップ。と言うのが今回の経緯です。


今日のデータはコレだっ!!

:一応データベース。。。


コピペでGO!
-------------------------------------
Sub henkan_v1()

Dim y As Integer '始点(列)
Dim ymax As Integer '終点(列)
Dim y1, y2, y3, y4
Dim ya As String 'A1形式始点
Dim yb As String 'A1形式終点
Dim x As Integer '始点(行)
Dim xmax As Integer '終点(行)
Dim i As Integer

'①DBの範囲を取得する
For x = 1 To 10
For y = 1 To 10
If Cells(x, y) <> "" Then
Exit For
End If
Next y
If Cells(x, y) <> "" Then
Exit For
End If
Next x

xmax = Cells(Rows.Count, y).End(xlUp).Row
ymax = Cells(x, Columns.Count).End(xlToLeft).Column

'②確認!
MsgBox "表の範囲は" _
& vbCrLf & y & "列" & x & "行" & "から" _
& vbCrLf & ymax & "列" & xmax & "行" & "まで"

'③取得した数値をA1方式に変換する
'chr(65~90)がA~Zに対応(最大列数256行)

'④始点のyをA1形式に変換
ya = (Chr(y + 64) & x)

'⑤終点のymaxをA1形式に変換
If ymax \ 26 <> 0 Then
y1 = Chr((ymax \ 26) + 64)
Else
y1 = ""
End If

If ymax Mod 26 <> 0 Then
y2 = Chr((ymax Mod 26) + 64)
Else
y2 = Chr((ymax Mod 26) + 65)
End If

yb = y1 & y2 & xmax

'⑥確認!
MsgBox "表の範囲は" _
& vbCrLf & ya & "から" & yb & "です"

End Sub
-------------------------------------

変換すべきは、列番号です。
エクセルのワークシートは行は数字のままなので、VBAで取得したデータのままで問題ありませんが、列番号はローマ字にしなくてはいけません。

①ではDBのデータ開始位置を探しています。
1行目の1~10列を探してデータが無い場合は、以後10行目までループします。
DBで10行10列目までにデータが無いのは考えづらいので、探す範囲はこれぐらいで良いかと。

と言うより、開始位置が分かれば直接「x=3」「y=2」と代入した方がコードが読みやすいかも。
「xmax」、「ymax」は前回勉強した最終セルの取得技ですね。

②実際にDBの範囲を取得できたかをメッセージボックスに表示させてみます。

③ここからが今回のキモです。
Chr関数を使います。
この関数は「Chr(n)」を構文とし、「n」の数値をASSCIIコードで文字変換してくれます。
ASSCIIコードでは「65」が「A」、「90」が「Z」となります。

④ここまで分かればもう簡単ですね。
2列目(B列)が開始位置なので、ASSCIIコードは66が該当します。

chr(66)

となればOKって事です。

ya = (Chr(y + 64) & x)
「y」はデータ開始列、今は「2」が代入されているので、Chr(66)で「B」と変換できます。
「x」はデータ開始行の「3」が代入されています。
「B」&「3」が変数「ya」に代入され、晴れて「B3」というアドレスが取得できたわけです。


⑤しかしここが厄介です。
先程の④は、最初に言ったように「データ開始列は10列以内だ」という前提がありました。
しかしDBの最終列と言うのは、どこまで伸びるか分かりません。
仮に27列目だったとしたらどうでしょう?

ya = (Chr(y + 64) & x)

この式に「yを27」にして当てはめると「Chr(91) & x」となります。
はて?Chr(91)?
さっきChr(90)がZだと書きました。Zの次の文字とは一体!?

実行すれば分かりますが、「 [ 」←コレが該当します。
これでは正しいアドレスが取得できませんし、エクセルでは「Z列」の次は「AA列」なんです。
ローマ字に変換できないし、列番号は2桁になるしで色々と考えないといけないようです。

そこで。

割り算です。
最終列数を26で割ります。すると以下の2通りに分岐します。
1)26で割れなかった場合
  最終列は26以下なので「(Chr(y + 64) & x)」が使えます。
2)26で割れた場合
  Chr(最終列を26で割った商 + 64)
  Chr(最終列を26で割った余り + 65)
  を&で繋ぐと2桁のアドレスも取得できます。
  「余り」を使う場合は、割り切れると「0」が発生するので「+ 65」となります。

⑥最後に「A1」形式でデータ範囲をメッセージボックスに表示させてみます。


自作関数についても触れようかと思ったのですが、眠くなってきたので割愛しまふ。
以下、強引にコードを短くした例。

コピペでGO!
-------------------------------------
Sub henkan_v2()

Dim y(1) As Variant '行
Dim ya(1) As Variant '変換用
Dim x(1) As Variant '列
Dim i As Integer

x(0) = 3
y(0) = 2
x(1) = Cells(Rows.Count, y(0)).End(xlUp).Row
y(1) = Cells(x(0), Columns.Count).End(xlToLeft).Column

For i = 0 To UBound(y)
ya(i) = y(i)
Call henkan(ya(i))
Next i

Range(ya(0) & x(0), ya(1) & x(1)).Select

End Sub

Function henkan(ByRef it As Variant)

Dim h1 As Variant
Dim h2 As Variant

If it \ 26 <> 0 Then
h1 = Chr((it \ 26) + 64)
Else
h1 = ""
End If

If it Mod 26 <> 0 Then
h2 = Chr((it Mod 26) + 64)
Else
h2 = Chr((it Mod 26) + 65)
End If

it = h1 & h2

End Function
-------------------------------------

メインのコードは10行程度。
「Function~」となっているのが、自作関数と呼ばれる部分です。
上記の場合はsub henkan_v2の「henkan(n)」の「n」の値を「Function~」でASSCIIコードに変換してhenkan_v2にデータを返してくれます。

今回は数値が文字になって返ってくる、つまりデータ型が変わることになります。
コードの11行目あたりで意味不明な記述
ya(i) = y(i)
Call henkan(ya(i))
があるのですが、
    Call henkan(y(i))
では、エラーが出てしまう。ハズなのですが…

あれ?通るなぁ。。。

-------------------------------------
For i = 0 To UBound(y)
Call henkan(y(i))
Next i

Range(y(0) & x(0), y(1) & x(1)).Select
-------------------------------------

For~以下をこうしてもOKっぽいです。
ちなみにこのコードはDBの範囲をメッセージボックスで表示しません。
しかしエクセルのDBを見てもらうと、DBが範囲選択されていると思います。



だんだん雑になってきました。

元々覚え書きとして始めた物なので、コレが限界ですね。


大体の事にお答えできませんが、何かあればはなみずまで一報ください。

一緒に悩んであげます♪
(役に立たねぇ~(ノ。・ω・)ノ

「ココはこーした方が良いよ!」的なアドバイスなど頂けると幸いです。
スポンサーサイト

テーマ : パソコン初心者 - ジャンル : コンピュータ

コメント

コメントの投稿

トラックバック


この記事にトラックバックする(FC2ブログユーザー)

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。