VBAをMACで使えるようにしたい&アクティブシートだけを参照できるようにしたい

以前、以下の質問をして、いただいた回答からあとは自分で勉強しながら使っていこうと思っていたのですが、行き詰ってしまったので教えていただけますか？
https://oshiete.goo.ne.jp/qa/9139476.html

①
Macで使えない。
同じ作業をするものにMacユーザーがいたため、CreateObjectが使えません。
Macで代替となるようなもの、若しくは外部アプリケーションを使わずに行える方法はあるのでしょうか。

②
すべてのシートでマクロが動いてしまう。
マクロを実行するとアクティブなシートだけでなく、すべての「A」が付くシートのデータを対応するシートに出力してくれます。
シートは1日1シート使い、31日分あります。
さらに1シート内でも複数の計算をするようにしたいため、計算終了までに1分強かかってしまいます。
こちらはその時間待てばいいのですが、参照にする「A」が付くシートの指定をActiveSheetにすればいいのではと思い、いろいろやってみたのですが、結局うまく動きませんでした。
なにかいい方法を教えていただけますでしょうか。

素人の質問で申し訳ありませんが、よろしくお願いします。

下に以前の質問の抜粋を載せます。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
「VBAでシート名に対応したシートへ自動入力したい。」
質問抜粋
やりたいことは、例えば一つのブックにシート名が1~10までとA1~A10まであるとします。
シートA○のセルA1とセルA2に数字を入力したら、シート○のA1にシートA○のセルA1とセルA2の合計が自動入力されるようにするには、どのようなコードを書けばよいのでしょうか。
更に、できれば参照元であるシートA○を削除してもシート○に自動入力されたデータが消えないようにならないかと考えています。

回答でいただいたコード
Sub foo()
Dim dic As Object
Dim ws As Worksheet
Dim name As String

Set dic = CreateObject("Scripting.Dictionary")

With ActiveWorkbook
' A無しシートの存在チェック
For Each ws In .Worksheets
If Left(ws.name, 1) <> "A" Then
dic(ws.name) = True
End If
Next
' 計算＆記入
For Each ws In .Worksheets
If Left(ws.name, 1) = "A" Then
name = Mid(ws.name, 2)
If Not dic.Exists(name) Then
MsgBox name & "シートがありません", vbExclamation
Else
.Worksheets(name).Range("A1").Value _
= ws.Range("A1").Value + ws.Range("A2").Value
End If
End If
Next
End With
Set dic = Nothing
End Sub

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

No.2 ベストアンサー 回答者： siffon9 回答日時： 2015/12/30 11:35

こんにちは、No.1です。

> Set ws = Sheets(name)
> で「インデックスが有効範囲にありません」というエラーが出て自力では解決できませんでした。

これは、この行が実行される時点で、変数nameの値が未定になっているので、
「未定」という名前のシートは無いよ！というエラーになります。



あと、No.1のソースですが、Ａ○シートに対応する○シートがなかった場合のエラーメッセージ表示を追加したものを以下に書きますので、参考にしてください。
------------------------------------------------------------------
Sub foo()
On Error GoTo ERR_TRAP
Dim name As String
With ActiveWorkbook
If Left(ActiveSheet.name, 1) <> "A" Then
MsgBox "現在アクティブなシートのシート名にAがついていません。", vbExclamation
Exit Sub
End If
name = Mid(ActiveSheet.name, 2)
.Worksheets(name).Range("A1").Value _
= ActiveSheet.Range("A1").Value + ActiveSheet.Range("A2").Value
End With
GoTo LAST
ERR_TRAP:
MsgBox ActiveSheet.name & "に対応するA無しシートがありません。", vbExclamation
LAST:
End Sub

この回答へのお礼

なるほど！
確かに値が決まって無ければエラーになりますね。

しかし順番を変えればいいというわけでもないのですね。
今度は「オブジェクトはプロパティまたはメソッドをサポートしていません」と出ました。
これ以上教えていただくのも悪いので、この回答をベストアンサーにして閉め切ろうと思います。
今回分からなかったところは、これから少しずつ勉強していこうと思います。

エラーメッセージの追加ありがとうございます。
マクロを動かした時に、人為的ミスでエラーが出るかもしれないという予測をたて、対処しておくことの大切さがわかりました。

いろいろありがとうございました。

お礼日時：2015/12/30 12:48

No.1 回答者： siffon9 回答日時： 2015/12/29 16:57

件の回答をしたものです。

以下で如何でしょう？
現在アクティヴな、Ａ○シートのみ計算するようにしました。
------------------------------------------------------
Sub foo()
Dim name As String
With ActiveWorkbook
If Left(ActiveSheet.name, 1) <> "A" Then
MsgBox "現在アクティブなシートのシート名にAがついていません。"
Exit Sub
End If
name = Mid(ActiveSheet.name, 2)
.Worksheets(name).Range("A1").Value _
= ActiveSheet.Range("A1").Value + ActiveSheet.Range("A2").Value
End With
End Sub
------------------------------------------------------


> すべてのシートでマクロが動いてしまう。
> マクロを実行するとアクティブなシートだけでなく、すべての「A」が付くシートのデータを対応するシートに出力してくれます。

これは、１回のマクロ実行で複数のシートの計算を行うと考えたので、そう動作するように作成ました。


ご質問のときに詳細な仕様を記載いただければ、より適切に回答できたと思います。

この回答へのお礼

ありがとうございます！
完璧です！

前回は自分で勉強したいという思いもあり、細かい修正は自分で行うつもりでした。
それが逆に二度手間をかけさせてしまい申し訳ありません。
自分もはじめは全部のシートに働けばいいと思っていたのですが、思った以上に重くて変更を考えました。
前回の回答も、私の不十分な質問の中完璧に答えていただけてたと思います。

前回の回答もMid関数を使われていたことから、自分でもいろいろ調べつついじってみたのですが、エラーが出たり、出力先が常に一番若い数字のシートになったり、うまくいきませんでした。
ちなみにお見苦しいですが最終的に行きついていたのが以下のコードです。

------------------------------------------------------
Sub test()
Dim name As String
Dim ws As Worksheet
Dim aws As Worksheet

Set ws = Sheets(name)
Set aws = ActiveSheet

With ActiveWorkbook

name = Mid(aws.name, 2)

.ws.Range("A1").Value _
= aws.Range("A1").Value + aws.Range("A2").Value

End With
End Sub
------------------------------------------------------

Set ws = Sheets(name)
で「インデックスが有効範囲にありません」というエラーが出て自力では解決できませんでした。

はじめっから回答者さんは使ってないのですが、いろいろなサイトを見てるうちに、Setステートメントというのを覚えて、使って見たかったんですかね・・・。

しかし原因を調べてみたのですが、SETステートメントでSheetsの引数に変数を使ってはいけないというものが出てきませんでした。
常識なのかもしれませんが素人感覚ではなぜ使えないのか分からず、使えるようにするために四苦八苦して、「使わない」という選択肢が出てきませんでした。

今回いただいた回答で、無事使えるようになりました。
ありがとうございました。

お礼日時：2015/12/30 10:00