Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Mac OS X : Excel 2016)
マクロを使った全角・半角の変換について
投稿日時: 18/02/08 10:30:43
投稿者: kumaten

はじめて投稿します。
どうぞよろしくお願いいたします。
 
今回、クライアントからExcelで支給される商品データに対してマクロを実行し「アルファベット」「数字」「特定の記号」を半角(「カタカナ」については、半角カナではなく全角)に変換するという作業が発生しました。
 
恥ずかしながらマクロは触ったことがなく、Webサイトの情報を参考(というか流用)に、ある程度はうまくいきました。しかし、指定しているはずの”/”(スラッシュ)」、”−”(ハイフン)、” ”(スペース)、”.”(ピリオド)が全角のままに残ってしまうのです。
 
自己解決ができなさそうなので、皆様、どうかご教示いただけませんでしょうか。
 
 
今回のマクロはこちらになります。

ーーここから
 
'***************************************************************
' カタカナは全角化、英数字および記号を半角化するユーザー定義関数
' 引数:文字列 変換対象となる文字列を指定します
' 引数:数字  数字半角化オプション(省略可) 規定値:True
' 引数:記号  記号半角化オプション(省略可) 規定値:False
'***************************************************************
Function myStrFmt(文字列 As String, Optional 数字 As Boolean = True, Optional 記号 As Boolean = False)
 
  Dim ReplaceList As String
  Dim TargetStr As String
  Dim MAK As String, NUM As String, ALB As String
  Dim i As Long
 
  '半角化の対象とする文字を全角で定義
  MAK = "!#$%&?()*+−./:;<=>?@[¥]^_{|}。、,, ”
  NUM = "01234567890"
  ALB = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  '置換リスト作成
  ReplaceList = NUM & ALB & StrConv(ALB, vbLowerCase)
  If 数字 Then ReplaceList = ReplaceList & NUM
  If 記号 Then ReplaceList = ReplaceList & MAK
 
  '全角化
  文字列 = StrConv(文字列, vbWide)
  '置換
  For i = 1 To Len(ReplaceList)
    TargetStr = Mid(ReplaceList, i, 1)
    文字列 = Replace(文字列, TargetStr, StrConv(TargetStr, vbNarrow))
  Next i
  myStrFmt = 文字列
 
End Function
 
'マクロで関数を使用するサンプル(セルを選択した状態で実行)
Sub サンプル()
 
  On Error Resume Next
  Dim rngCell As Range
  Application.ScreenUpdating = False
  For Each rngCell In Selection
    If rngCell.Value <> "" Then
      rngCell.Value = myStrFmt(rngCell.Value, True, False)
    End If
  Next rngCell
  Application.ScreenUpdating = True
 
End Sub
 
 
ーーここまで
 
 
また、テストで使用したデータは以下の通りです。
 
ーーここから
 
【マクロ使用前】
PCJOY ブラック
PCJOY アクア
PCJOY ホワイト
PCJOY ブラック
PCJOY ホワイト
GTM2618HD/SW3
GTM2618HD/TBK
SU276Sブラック
SU276Wシルバー
SU276Wブラック
幅107×奥行83×高さ42cm
幅117×奥行80×高さ56cm
http://www.irisplaza.co.jp/Index.asp?KB=SHOSAI&SID=H245400F
https://www.irisohyama.co.jp/led/ceiling/cl8d−50.html
FH−G3317Y S
FW−4616L S
NA−023S
FK−C2−WP
IC−BT1 ホワイト
CL6D−5.0
CL8D−5.0
 

 
【マクロ使用後】
PCJOY ブラック
PCJOY アクア
PCJOY ホワイト
PCJOY ブラック
PCJOY ホワイト
GTM2618HD/SW3
GTM2618HD/TBK
SU276Sブラック
SU276Wシルバー
SU276Wブラック
幅107×奥行83×高さ42cm
幅117×奥行80×高さ56cm
http://www.irisplaza.co.jp/Index.asp?KB=SHOSAI&SID=H245400F
https://www.irisohyama.co.jp/led/ceiling/cl8d−50.html
FH−G3317Y S
FW−4616L S
NA−023S
FK−C2−WP
IC−BT1 ホワイト
CL6D−5.0
CL8D−5.0
 
ーーここまで
 
 
クライアントがWINのExcelで作業し、私はMacのExcelで作業しています。
その影響もあったりするんでしょうか…。
 
どうぞよろしくお願いいたします。

回答
投稿日時: 18/02/08 10:56:35
投稿者: sk

引用:
rngCell.Value = myStrFmt(rngCell.Value, True, False)

rngCell.Value = myStrFmt(rngCell.Value, True, True)
 
-------------------------------------------------------------
 
引数の指定を誤っているだけでしょう。

回答
投稿日時: 18/02/08 11:06:36
投稿者: WinArrow
投稿者のウェブサイトに移動

VBAを使わなくても
JIS関数で対応できますよ!
 
セルA1に半角/全角混在文字列があるとして
 
=JIS(A1)
ですべて全角になります。

回答
投稿日時: 18/02/08 11:10:11
投稿者: sk

補足:

引用:
ReplaceList = NUM & ALB & StrConv(ALB, vbLowerCase)

上記赤字部分のコードがあると、引数[数字]に渡された値
( True / False )が何であるかに関わらず、無条件で
全角数字が半角数字に変換されることになりますのでご注意下さい。
 
---------------------------------------------------------------
 
ReplaceList = ALB & StrConv(ALB, vbLowerCase)
 
---------------------------------------------------------------

回答
投稿日時: 18/02/08 11:14:21
投稿者: sk

WinArrow さんの引用:
=JIS(A1)
ですべて全角になります。

kumaten さんの引用:
カタカナは全角化、英数字および記号を半角化するユーザー定義関数

投稿日時: 18/02/08 11:35:12
投稿者: kumaten

sk さんの引用:
引用:
rngCell.Value = myStrFmt(rngCell.Value, True, False)

rngCell.Value = myStrFmt(rngCell.Value, True, True)
 
-------------------------------------------------------------
 
引数の指定を誤っているだけでしょう。

 
 
skさんありがとうございます!
 
早速変更して試したところ現状、こんな感じになりました。

 
【マクロ使用後】
PCJOY ブラック
PCJOY アクア
PCJOY ホワイト
PCJOY ブラック
PCJOY ホワイト
GTM2618HD/SW3
GTM2618HD/TBK
SU276Sブラック
SU276Wシルバー
SU276Wブラック
幅107×奥行83×高さ42cm
幅117×奥行80×高さ56cm
http://www.irisplaza.co.jp/Index.asp?KB=SHOSAI&SID=H245400F
https://www.irisohyama.co.jp/led/ceiling/cl8d−50.html
FH−G3317Y S
FW−4616L S
NA−023S
FK−C2−WP
IC−BT1 ホワイト
CL6D−5.0
CL8D−5.0
 
 
ほぼ希望通りですが、”−”だけ全角のままです。
VBAのエディタ(?)上で確認すると以下のようになっていて、おかしい感じがします。

MAK = "?!#$%&()*+?./:;<=>?@[¥]^_{|}。、,, "
 
うーん、知識不足ですみません…。

回答
投稿日時: 18/02/08 13:30:17
投稿者: sk

引用:
ほぼ希望通りですが、”−”だけ全角のままです。
VBAのエディタ(?)上で確認すると以下のようになっていて、おかしい感じがします。

MAK = "?!#$%&()*+?./:;<=>?@[¥]^_{|}。、,, "

一部の全角特殊記号に関しては、Windows と Mac では
それぞれ異なる文字コードが用いられているそうなので、
恐らくはその影響でしょう。
 
(その環境における)半角ハイフンに対応する
全角文字が何であるかは
 
--------------------------------------------------
 
StrConv("-", vbWide)
 
--------------------------------------------------
 
の戻り値から得られるかも知れません。
( Mac 環境がないので試せていません)

投稿日時: 18/02/08 15:07:06
投稿者: kumaten

sk さんの引用:
引用:
ほぼ希望通りですが、”−”だけ全角のままです。
VBAのエディタ(?)上で確認すると以下のようになっていて、おかしい感じがします。

MAK = "?!#$%&()*+?./:;<=>?@[¥]^_{|}。、,, "

一部の全角特殊記号に関しては、Windows と Mac では
それぞれ異なる文字コードが用いられているそうなので、
恐らくはその影響でしょう。
 
(その環境における)半角ハイフンに対応する
全角文字が何であるかは
 
--------------------------------------------------
 
StrConv("-", vbWide)
 
--------------------------------------------------
 
の戻り値から得られるかも知れません。
( Mac 環境がないので試せていません)

ありがとうございます!
この辺りを読んで勉強したのですが、ちょっと私はギブアップかも知れません…(T_T)

https://ywnb.net/p/201509/2465
http://www.4d.com/jp/blog/dash-punctuation.html
 
あとすみません…、教えていただいたこちらは、どのようにして確認すればよいですか?

StrConv("-", vbWide)

回答
投稿日時: 18/02/08 15:21:30
投稿者: sk

引用:
あとすみません…、教えていただいたこちらは、どのようにして確認すればよいですか?

StrConv("-", vbWide)

イミディエイトウィンドウに以下のコードを直接入力して
そのまま Enter キー( Mac の場合は Return キーかな)を
クリックしてみて下さい。
 
------------------------------------------------------
 
? StrConv("-", vbWide)
 
------------------------------------------------------

投稿日時: 18/02/08 15:37:46
投稿者: kumaten

sk さんの引用:
引用:
あとすみません…、教えていただいたこちらは、どのようにして確認すればよいですか?

StrConv("-", vbWide)

イミディエイトウィンドウに以下のコードを直接入力して
そのまま Enter キー( Mac の場合は Return キーかな)を
クリックしてみて下さい。
 
------------------------------------------------------
 
? StrConv("-", vbWide)
 
------------------------------------------------------

 
成功しました!

? StrConv("-", vbWide)

 
帰ってきたた”−”をMAKに追加してみましたが、結果は変わらずでした…(T_T)
なんでだーーーー!

回答
投稿日時: 18/02/08 15:50:33
投稿者: WinArrow
投稿者のウェブサイトに移動

すべて全角文字に変換するものと勘違いしていました。
申し訳ありません。
 
「−」の件
 
見た目には、同じに見えるが違う文字の例の一つ
 
「-」(マイナスの全角文字とカタカナの「ー」文字)
「○」(記号の○と漢数字の〇)
 

回答
投稿日時: 18/02/08 15:56:31
投稿者: sk

引用:
帰ってきたた””をMAKに追加してみましたが、結果は変わらずでした…(T_T)
なんでだーーーー!

StrConv("-", vbWide) の戻り値として返ってきた全角文字と、
各セルのテキストの一部として実際に格納されている全角文字
異なる文字(見た目は限りなく似ているが、文字コードが異なる。
この場合は Windows と Mac での全角マイナス等の文字コードの
違いによる可能性がある)からでしょうね。
 
その場合、前者の全角文字を StrConv 関数によって
半角ハイフンに変換することは可能でしょうけど、
後者の全角文字に関しては StrConv 関数では
半角ハイフンに変換出来ないはずです。
 
そういった全角文字に関しては「 StrConv 関数によって
その文字に対応する半角文字を取得する」のではなく、
個別に任意の半角文字を指定した上で Replace 関数による
文字列置換処理を実行する必要があるでしょう。

投稿日時: 18/02/08 16:01:42
投稿者: kumaten

そういった全角文字に関しては「 StrConv 関数によって
その文字に対応する半角文字を取得する」のではなく、
個別に任意の半角文字を指定した上で Replace 関数による
文字列置換処理を実行する必要があるでしょう。[/quote]
 
なるほど。
理解できました。もう少し勉強してみますね。
ありがとうございます!

回答
投稿日時: 18/02/08 18:05:00
投稿者: 細雪

他人さまのサイトへの直リンクで恐縮ですが、
http://www.4d.com/jp/blog/dash-punctuation.html
http://www.tokyocafe.net/slog/?eid=422
このような情報は参考になりますでしょうか。
 
 
私の手元にもMacOSの環境が無いので確認できずで申し訳ありませんが、
とりあえず、イミディエイトに
  ? ASC("ココ")
   ※ココにはお使いの「−」を入力してください。
でEnterキーを押すと、どんな数値が返ってくるでしょう?
WindowsのShift-JISだと「-32388」が多分返ってきます。
 
で、返ってきた数値を「XXXXX」として、
    範囲.Replace What:=Chr(XXXXX), Replacement:=Chr(45), LookAt:=xlPart
    ※Chr(45) は、Shift-JISで半角のハイフンです。
    ※範囲は適宜指定なさってください。シート全体で良いなら「Cells」でいけます。
とするとどうなるでしょう?
私自身で試せないので心苦しいのですが・・・

投稿日時: 18/02/08 18:20:13
投稿者: kumaten

私の手元にもMacOSの環境が無いので確認できずで申し訳ありませんが、
とりあえず、イミディエイトに
  ? ASC("ココ")
   ※ココにはお使いの「−」を入力してください。
でEnterキーを押すと、どんな数値が返ってくるでしょう?
WindowsのShift-JISだと「-32388」が多分返ってきます。
 
情報ありがとうございます!
Excelの商品データから使っている「−」をコピーしてイミディエイトウィンドウで入力すると63が返ってきました。

?ASC("?")
 63
 
続きもやってみます。

投稿日時: 18/02/08 19:51:40
投稿者: kumaten

[quote="細雪"]他人さまのサイトへの直リンクで恐縮ですが、
http://www.4d.com/jp/blog/dash-punctuation.html
http://www.tokyocafe.net/slog/?eid=422
このような情報は参考になりますでしょうか。
 
ゆっくり読みました。
まさにこれです!まさしくこの現象が起こっています。

http://www.tokyocafe.net/slog/?eid=422

投稿日時: 18/02/08 20:09:07
投稿者: kumaten

で、返ってきた数値を「XXXXX」として、
    範囲.Replace What:=Chr(XXXXX), Replacement:=Chr(45), LookAt:=xlPart
    ※Chr(45) は、Shift-JISで半角のハイフンです。
    ※範囲は適宜指定なさってください。シート全体で良いなら「Cells」でいけます。
とするとどうなるでしょう?
私自身で試せないので心苦しいのですが・・・
[/quote]
 
 
下記で試したところ、すべての文字が「-」に変換されました

Cells.Replace What:=Chr(63), Replacement:=Chr(45), LookAt:=xlPart
 
「−」の半角変換は諦めるしかないのでしょうか。
まあ、手作業でも良いのですが、惜しいところまでいっただけに、ちょっとがっかりですね…。

回答
投稿日時: 18/02/08 23:19:37
投稿者: WinArrow
投稿者のウェブサイトに移動

こちらもWi−ndows環境なので、確かめることできなですが、
 
>Chr(63)
この指定は、違っているような気がします。
63は、ASC(?)で求めたもので、
全角文字のハイフンではないですよね?
半角文字の?です。

回答
投稿日時: 18/02/08 23:29:11
投稿者: WinArrow
投稿者のウェブサイトに移動

↓参考になる?ページを紹介します
 
http://www.ilovex.co.jp/Division/ITD/archives/2008/02/windowsmac.html

回答
投稿日時: 18/02/09 09:26:21
投稿者: sk

引用:
Excelの商品データから使っている「−」をコピーして
イミディエイトウィンドウで入力すると63が返ってきました。

?ASC("?")
 63

VBE では全ての文字を記述出来るわけではなく、
例えば Unicode の環境依存文字などを入力すれば
別の文字に置き換わります。
(変数 MAK に代入しようとしている文字列リテラルの
 一部の文字が化けているのもそのためです、)
 
つまり上記の結果は「−」の文字コードを
示したものではないでしょう。
 
1. 「−」が含まれているセルから
   「−」のみを選択してクリップボードにコピーする。
 
2. 空のワークシートの A1 セルに
   「−」を貼り付ける。
 
3. 2 のセルが選択されている状態で、
   イミディエイトウィンドウに以下のコードを入力し、
   Enter キーをクリックする。
 
---------------------------------------------------------------
 
? Asc(ActiveCell)
 
---------------------------------------------------------------
 
まずは以上のコードの実行結果をご確認下さい。

投稿日時: 18/02/09 17:13:12
投稿者: kumaten

---------------------------------------------------------------
 
? Asc(ActiveCell)
 
---------------------------------------------------------------
 
まずは以上のコードの実行結果をご確認下さい。[/quote]
 
 
反応遅れました。
教えていただいた手順で出た値はこちらです!

? Asc(ActiveCell)
-32388

回答
投稿日時: 18/02/09 17:34:34
投稿者: sk

引用:
教えていただいた手順で出た値はこちらです!

? Asc(ActiveCell)
-32388

引用:
'全角化
文字列 = StrConv(文字列, vbWide)
'置換
For i = 1 To Len(ReplaceList)
    TargetStr = Mid(ReplaceList, i, 1)
    文字列 = Replace(文字列, TargetStr, StrConv(TargetStr, vbNarrow))
Next i
myStrFmt = 文字列

'全角化
文字列 = StrConv(文字列, vbWide)
'置換
For i = 1 To Len(ReplaceList)
    TargetStr = Mid(ReplaceList, i, 1)
    文字列 = Replace(文字列, TargetStr, StrConv(TargetStr, vbNarrow))
Next i
 
If 記号 Then
    文字列 = Replace(文字列, Chr(-32388), Chr(45))
End If
 
myStrFmt = 文字列
 
-----------------------------------------------------------------------
 
上記青字部分のステートメントを追記して
実行するとどうなるでしょうか。

投稿日時: 18/02/09 17:44:34
投稿者: kumaten

'全角化
文字列 = StrConv(文字列, vbWide)
'置換
For i = 1 To Len(ReplaceList)
    TargetStr = Mid(ReplaceList, i, 1)
    文字列 = Replace(文字列, TargetStr, StrConv(TargetStr, vbNarrow))
Next i
 
If 記号 Then
    文字列 = Replace(文字列, Chr(-32388), Chr(45))
End If
 
myStrFmt = 文字列
 
-----------------------------------------------------------------------
 
上記青字部分のステートメントを追記して
実行するとどうなるでしょうか。[/quote]
 
追記したところマクロは正常に実行されましたが、
変換後も変わらず「−」のみ全角のままです。

回答
投稿日時: 18/02/09 19:00:35
投稿者: sk

引用:
追記したところマクロは正常に実行されましたが、
変換後も変わらず「−」のみ全角のままです。

「−」を含むセル範囲を選択された状態で
実行しても変わらなかったということでしょうか。
 
引用:
成功しました!

? StrConv("-", vbWide)

引用:
文字列 = Replace(文字列, Chr(-32388), Chr(45))

文字列 = Replace(文字列, StrConv("-", vbWide), Chr(45))
 
----------------------------------------------------------------
 
また、上記のように書き換えた場合も同様でしょうか。

投稿日時: 18/02/09 19:18:33
投稿者: kumaten

sk さんの引用:
引用:
追記したところマクロは正常に実行されましたが、
変換後も変わらず「−」のみ全角のままです。

「−」を含むセル範囲を選択された状態で
実行しても変わらなかったということでしょうか。
 
引用:
成功しました!

? StrConv("-", vbWide)

引用:
文字列 = Replace(文字列, Chr(-32388), Chr(45))

文字列 = Replace(文字列, StrConv("-", vbWide), Chr(45))
 
----------------------------------------------------------------
 
また、上記のように書き換えた場合も同様でしょうか。

 
 
skさん粘り強くお付き合いいただき、ありがとうございます!
 
文字列 = Replace(文字列, StrConv("-", vbWide), Chr(45))
を追加したところ「−」が遂に半角の「-」になりました!!!!!
 
  
そして、見事に希望通りの結果が得られました。

  
【マクロ使用後】
PCJOY ブラック
PCJOY アクア
PCJOY ホワイト
PCJOY ブラック
PCJOY ホワイト
GTM2618HD/SW3
GTM2618HD/TBK
SU276Sブラック
SU276Wシルバー
SU276Wブラック
幅107×奥行83×高さ42cm
幅117×奥行80×高さ56cm
http://www.irisplaza.co.jp/Index.asp?KB=SHOSAI&SID=H245400F
https://www.irisohyama.co.jp/led/ceiling/cl8d-50.html
FH-G3317Y S
FW-4616L S
NA-023S
FK-C2-WP
IC-BT1 ホワイト
CL6D-5.0
CL8D-5.0
 
 
途中、あきらめかけましたが、うまくいって嬉しいです。
 
ほんの少しだけですが、マクロも触ってみて本人の勉強にもなりました。
ご回答くださった皆さまどうもありがとうございました!!