-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

GIOコードの計算をVBA化。

ネット上にメッシュコード関連の計算式があったので、Excelの関数として使えるようにVBA化しました。

参考にした計算式が乗っていたサイトは、残念ながら無くなってしまいましたが、魚拓がのこっていたので張り付けます↓

式をコード化したものが以下

‘—————————————————-
‘緯度経度から3次(1㎞)メッシュ
‘—————————————————-
Function LatLontoMesh(lat, lon)
Dim s1, a1
s1 = WorksheetFunction.Quotient(lat * 60, 40)
a1 = (lat * 60) – (s1 * 40)
s2 = WorksheetFunction.RoundDown(lon – 100, 0)
a2 = (lon – 100) – s2
s3 = WorksheetFunction.Quotient(a1, 5)
a3 = a1 – (s3 * 5)
s4 = WorksheetFunction.Quotient(a2 * 60, 7.5)
a4 = (a2 * 60) – (s4 * 7.5)
s5 = WorksheetFunction.Quotient(a3 * 60, 30)
s6 = WorksheetFunction.Quotient(a4 * 60, 45)

LatLontoMesh = s1 & s2 & s3 & s4 & s5 & s6
End Function

‘—————————————————-
‘3次(1㎞)メッシュから緯度経度を返す
‘—————————————————-
Function CalcLatLonFromMesh1km(mesh, ctr)
Dim strLat, strLon

‘5439-2308-2
‘3次LAT式
strLat = Left(mesh, 2) / 1.5 + (((Mid(mesh, 5, 1) * 5) / 100) / 60 * 100) + (((Mid(mesh, 7, 1) * 30) / 10000) / (60 * 60) * 10000)
‘3次LON式
strLon = Mid(mesh, 3, 2) + 100 + (((Mid(mesh, 6, 1) * 7.5) / 100) / 60 * 100) + (((Mid(mesh, 8, 1) * 45) / 10000) / (60 * 60) * 10000)

If ctr = “lat” Then CalcLatLonFromMesh1km = strLat
If ctr = “lon” Then CalcLatLonFromMesh1km = strLon

End Function

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

解決!

Excel2007を使っていますが

好奇心に負けて2013をちょっと並行で入れて、アンインストールしました。

すると、Excel立ち上げ時に必ず構成画面が出るようになってしまい

ファイルを開くまですごく時間がかかる!

Q&Aサイトには、Excelを一度すべてアンインストールしてから

入れなおせってあるけど、会社のPCにつき、CD貸出言いにくい・・

というわけで、だめもとでいろいろやってみたところ、

システムの復元 から インストール前の時点に復元することで

出なくなりました!

システムの復元は、スタートメニュー>すべてのプログラム>アクセサリ>システムツールの中にあります。

実行するとウィザードが立ち上がるので、「次へ」を押して、復元ポイントの選択から、

Excel2013を入れる前の日付を復元ポイントとして選択し、ウィザードに従ってリストアしていくと

PCをインストールする前の状態に戻してくれるので、変な警告も出なくなります。

但し、復元時点以降に入れたソフトウェアもExcel2013と共に無かったことになるので入れなおしになりますのでご注意ください。

なお、作成したテキストファイルとかはなくならないのでご安心を。

めでたし

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

リリースしたメール一括作成ツールについて、技術者向けの内容です。

概要

メール一括作成ツール(OutLook、Lotus)を外部から呼び出して使えます。

Excelを起動できるものなら何でも(.NET、Excel、JavaScript等のWEB関連・・・)

仕様

■外部から呼び出せるマクロ一覧

MAKE_MAIL_ITEM_TEST_NG() ・・・ 「メール作成」ボタンと同等の処理

宛先転記() ・・・アドレス帳シートの「宛先に追加」ボタンと同等の処理

■アドレス帳仕様

⇒名前定義

toriCode ・・・取引先コード範囲セル

⇒チェックボックスにチェックを入れる

A列のセルの値を”TRUE”にすると、チェックが入ります。

使用例:

<VBA:別のExcelBookからメール一括作成ツールを呼び出す>    

Sub CallMailTool()
‘//ツール起動
Workbooks.Open “C:\Users\ms\Documents\リリース済\Vectorメールツール\Lotusメール一括作成ツール.xls”

‘//アドレス帳シートを開き、取引先コードを検索
With ActiveWorkbook.Worksheets(“アドレス帳”)
Dim FoundCell As Range ‘検索結果のセル格納用変数
‘検索(toriCode = 名前定義。アドレス帳の取引先コードのセル範囲)
Set FoundCell = .Range(“toriCode”).Find(What:=”10223455″)

‘検索結果なし
If FoundCell Is Nothing Then
MsgBox “取引先がありませんでした”

‘検索結果あり
Else
‘チェックボックスにチェックを入れる(A列のセルにTRUEを入れる)
FoundCell.Offset(0, -1).Value = “TRUE”

‘//アドレス帳シートのチェックした項目を宛先シートに転記
Application.Run “Lotusメール一括作成ツール.xls!宛先転記”

‘//メール作成実行
Application.Run “Lotusメール一括作成ツール.xls!MAKE_MAIL_ITEM_TEST_NG”

‘//完了メッセージ
MsgBox “完了しました”
End If
End With

具体的な使用シーン

・社内WEBサイトで検索した結果を「本文」シートに自働転記してメール作成

・別のExcelツールで集計した代金未納の取引先コードを取得し、すべてに催促メールを作成

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

「Excelの高速化」の記事についてのおまけです。大したもんでなく恥ずかしいのですが、コードをさらします。

案外忘れがち?ループの回数は、処理速度低下の原因になりますので可能であれば減らした方がいいです。

この例では行のコピー&挿入です。

<シチュエーション>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

書式や数式で整えた入力欄100行が用意されているが、1000行入力しなければならないのであった・・。

コピー&挿入を1行づつやるとループのしすぎで遅い。かといって、一度に900行挿入すると大量すぎて重い。

上限500行に押さえてループ回数を少なくすれば快適なのであった。。。(あらすじ終わり)

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

<作戦>

・用意した入力欄100行を超えたら、超えた分の行を挿入。但し1度に上限500行

  最大値500の取得方法→ワークシート関数のMin()で「500」と「入力が必要な行数」を比較、どちらか小さいほうを取得

   (別に関数を使わなくても If i>500 挿入行=500 とやってもよい。好みで)

     例: answer = Application.WorksheetFunction.Min(500,入力データ数) 

     Forループで大→小方向に入力行を回して行き、追加した行数分iを減らせばOK。

   例:① MIN(500、900)で小さい方「500」を挿入、→ 残り400 MIN(500、400)で小さいほう「400」を挿入 →残り0

<コード>

‘▽100行よりデータが多い場合行を挿入する——————————————▽

If  入力データーの数 > 100 Then  ‘100行よりデータが多い場合

For i = 入力データーの数 – 100 To 1 Step -1

‘上限五百
挿入行 = Application.WorksheetFunction.Min(i, 500) ‘500以上だったら補正
‘基にする行をコピー
Range(“CopyCell”).EntireRow.Copy
‘複数行選択して挿入
Range(“CopyCell”).Resize(Abs(挿入行), 1).EntireRow.Insert
‘カットコピーモードをオフに
Excel.Application.CutCopyMode = False
‘iから挿入した行を引く。+1するのはForの先頭に行ったときに1減るから
i = i – 挿入行 + 1

Next

End If

‘△—————————————————————————-△

If分でもいいけど、MinやMaxがすっきりしてて好きなんだ・・

 

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

続きです。

どうやらExcel2013の大きな変更点として、ちょっとした動きも全部アニメーション表示されるようになったようです。

↓アニメーション参考動画(外国のです)

セルをクリックするだけで、アクティブであることを示す太いワクが

クリックしたセルにヌルッと写るアニメーションが表示されます。数字を変えるとグラフも自動でヌルッと動きます。

視覚的なパフォーマンスは未来的ですが、残念ながらスピード的なパフォーマンスが後退してしまったようです。

Continue Reading »

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

続きです。

英語サイトを検索した結果を適当に翻訳します。Why Excel2013 Slow?

GoogleChromeをお使いの方は、翻訳機能があるので、ご自分で調べてみても面白いかもしれません。

原因その②.PowerPivotを使っている?
Continue Reading »

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

最近、検索キーワードで「Excel2013 遅い」でやってくる人が多いようなので、

興味を持ってしらべてみました。なお、まだ使った事はなかったです。そうか、遅いのか。。。

以前の記事→http://chiroinu.freehostia.com/wordpress/?p=231

パソコン関連はアメリカが本場だと思っているので、いい情報が見つからない場合、

最終的には *はぁ~・・・↓↓↓*英語で調べてみるデス・・。HPが減るのであまりやらないけど。

というわけで「Why Excel2013 VBA Slow」で調べてみたところ、結構出てきました。

Continue Reading »

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

20140329追記・・・技術者向け情報、公開しました。

NotesとOutLookのメール一括作成ツールをベクターに公開しました。

複数のメールが一括作成できます。面倒なルーチンもこれで大幅に時間短縮!

説明動画: http://youtu.be/bJGnt2EoDR4

ロータスノーツ  Var:http://www.vector.co.jp/soft/winnt/net/se505887.html

OutLOOK Var:http://www.vector.co.jp/soft/win95/net/se505911.html

詳細は以下↓

Continue Reading »

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

「実行時エラー ‘3131’: FROM句の構文エラーです。」
ExcelVBAにてCSVファイルをテーブルとして接続する際、エラーが発生してしまう場合があります。下記にご注意!
①ファイル名に禁則文字が使われている場合[-ハイフンやスペース等]
②ファイル名のトータルが拡張子を含み59文字を超える場合。(XP、OFFICE2003のみで実験)

①はテーブル名を[]で囲えば解決します。お気をつけを・・・
 ⇒SELECT * FROM [悪い テーブル - 名.csv]


Function openSql()
Dim strSQL
Set CN = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
If Len(FileName) > 59 Then Err.Raise 1, , "ファイル名が長すぎます。" & vbCrLf _
& "ファイル名は拡張子を除き55文字以内に収めて下さい"

strSQL = “SELECT * FROM [” & FileName & “]”
CN.Open “Driver={Microsoft Text Driver (*.txt; *.csv)};” & _
“DBQ=” & FilePath & “;” & _
“ReadOnly=0;”

RS.CursorLocation = 3 ‘adUseClient
RS.Open strSQL, CN, 1

End Function

予約語と使用できない文字

データベース名・テーブル名・フィールド名に,予約語,および使用できない記号を使うとエラーが発生します。例として,Microsoft AccessとODBC環境下で使用できない文字と予約語を列挙してみました。

◎使えない記号・文字

「.(ピリオド)」「/(スラッシュ)」 「*(アスタリスク)」「:(コロン)」「!(感嘆符)」「#(シャープ)」「&(アンバサンド)」「-(ハイフン)」「?(疑問符)」 およびスペース

(参考:http://image.gihyo.co.jp/assets/files/book/2001/4-7741-1289-5/uldev/uldev_tips.htm#tips14)

SELECT * FROM [My Table]

http://msdn.microsoft.com/ja-jp/library/ms176027(v=SQL.105).aspx

-PR-
NTTPCのレンタルサーバー
基本情報技術者講座 28,000円から ★ぶっちぎり宅建ライブ開講★

エクセルVBAを高速化する7つの手法!

WindowsXP Excel2003の環境でも10分→1分に時間短縮した実績あり。

201403追記:下記の方法で解決しないExcel2013の方→http://chiroinu.freehostia.com/wordpress/?p=384

■マクロスピードアップまとめ■■■■■■■■■■■■■■■■

大前提:セルの結合は止めるべし!←5000件くらいの帳票でもセルの結合があるなしで

フリーズする!

Continue Reading »

Tags: