じゃばのひまつぶし：ExcelVBAによる競馬ソフト自作教室とJRA-VAN登録ソフト（おずたんシリーズ）のサポートサイトです。
JRA-VAN Data Lab対応 競馬ソフト自作教室 <New JVData Explorer> 基礎編：レース補てん展開					第９回 更新日 2012-01-31
今回は”レース補てん情報(RCOV)”データをワークシートに展開するサブルーチンを作ります。全部で８種類ですが、ここで作るのは６つです。残りの２つ（RAとSE）は前回までにすでに作成済みです。 特に断りのない限り記述は”Top"のコードウィンドウです １．”競走馬マスタ（UM）”を展開するサブルーチン[DiplayUMDataOnWorksheet] '********** ワークシートに競走馬マスタ（"UM"）データを表示(Top) ********** '[第９回] Sub DisplayUMDataOnWorksheet(ByVal FileID As String, ByVal DataSu As Long) Dim p As Long, q As Long Dim A$ '* ワークシートにタイトルを表示 GoSub DisplayTitle Select Case FileID Case "UM_RCOV" For p = 1 To UmDataSu '* 配列をデータ関数にセット Call SetData_UM(UmData(p), mUmData) '* ワークシートに表示 GoSub DisplayToWorksheet Next p End Select '* テーブル名をセット UmTable = Worksheets(FileID).Range("B2").Value '* セル幅を自動調整 Worksheets(FileID).Range("B4").CurrentRegion.EntireColumn.AutoFit Exit Sub '*-------------------------------------------------------- '* サブルーチン '*-------------------------------------------------------- '* ワークシートに表示 DisplayToWorksheet: With Worksheets(FileID) .Activate .Range("B1").Value = p .Range("B" & 4 + p).Value = mUmData.head.RecordSpec .Range("C" & 4 + p).Value = "'" & mUmData.head.DataKubun .Range("D" & 4 + p).Value = "'" & mUmData.head.MakeDate.Year & _ mUmData.head.MakeDate.Month & mUmData.head.MakeDate.Day .Range("E" & 4 + p).Value = "'" & mUmData.KettoNum .Range("F" & 4 + p).Value = "'" & mUmData.DelKubun .Range("G" & 4 + p).Value = "'" & mUmData.RegDate.Year & _ mUmData.RegDate.Month & mUmData.RegDate.Day .Range("H" & 4 + p).Value = "'" & mUmData.DelDate.Year & _ mUmData.DelDate.Month & mUmData.DelDate.Day .Range("I" & 4 + p).Value = "'" & mUmData.BirthDate.Year & _ mUmData.BirthDate.Month & mUmData.BirthDate.Day .Range("J" & 4 + p).Value = Trim(mUmData.Bamei) .Range("K" & 4 + p).Value = "'" & mUmData.UmaKigoCD .Range("L" & 4 + p).Value = mUmData.SexCD .Range("M" & 4 + p).Value = mUmData.HinsyuCD .Range("N" & 4 + p).Value = "'" & mUmData.KeiroCD .Range("O" & 4 + p).Value = Trim(mUmData.Ketto3Info(0).Bamei) .Range("P" & 4 + p).Value = Trim(mUmData.Ketto3Info(1).Bamei) .Range("Q" & 4 + p).Value = Trim(mUmData.Ketto3Info(2).Bamei) .Range("R" & 4 + p).Value = Trim(mUmData.Ketto3Info(3).Bamei) .Range("S" & 4 + p).Value = Trim(mUmData.Ketto3Info(4).Bamei) .Range("T" & 4 + p).Value = Trim(mUmData.Ketto3Info(5).Bamei) .Range("U" & 4 + p).Value = Trim(mUmData.Ketto3Info(6).Bamei) .Range("V" & 4 + p).Value = Trim(mUmData.Ketto3Info(7).Bamei) .Range("W" & 4 + p).Value = Trim(mUmData.Ketto3Info(8).Bamei) .Range("X" & 4 + p).Value = Trim(mUmData.Ketto3Info(9).Bamei) .Range("Y" & 4 + p).Value = Trim(mUmData.Ketto3Info(10).Bamei) .Range("Z" & 4 + p).Value = Trim(mUmData.Ketto3Info(11).Bamei) .Range("AA" & 4 + p).Value = Trim(mUmData.Ketto3Info(12).Bamei) .Range("AB" & 4 + p).Value = Trim(mUmData.Ketto3Info(13).Bamei) .Range("AC" & 4 + p).Value = mUmData.TozaiCD .Range("AD" & 4 + p).Value = "'" & mUmData.ChokyosiCode .Range("AE" & 4 + p).Value = mUmData.ChokyosiRyakusyo .Range("AF" & 4 + p).Value = Trim(mUmData.Syotai) .Range("AG" & 4 + p).Value = "'" & mUmData.BreederCode .Range("AH" & 4 + p).Value = Left(Trim(mUmData.BreederName), 12) .Range("AI" & 4 + p).Value = Left(Trim(mUmData.SanchiName), 12) .Range("AJ" & 4 + p).Value = "'" & mUmData.BanusiCode .Range("AK" & 4 + p).Value = Left(Trim(mUmData.BanusiName), 12) .Range("AL" & 4 + p).Value = Format(mUmData.RuikeiHonsyoHeiti, "###,###,###") .Range("AM" & 4 + p).Value = Format(mUmData.RuikeiHonsyoSyogai, "###,###,###") .Range("AN" & 4 + p).Value = Format(mUmData.RuikeiFukaHeichi, "###,###,###") .Range("AO" & 4 + p).Value = Format(mUmData.RuikeiFukaSyogai, "###,###,###") .Range("AP" & 4 + p).Value = Format(mUmData.RuikeiSyutokuHeichi, "###,###,###") .Range("AQ" & 4 + p).Value = Format(mUmData.RuikeiSyutokuSyogai, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuSogo.Chakukaisu(q) Next q .Range("AR" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuChuo.Chakukaisu(q) Next q .Range("AS" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuBa(0).Chakukaisu(q) Next q .Range("AT" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuBa(1).Chakukaisu(q) Next q .Range("AU" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuBa(2).Chakukaisu(q) Next q .Range("AV" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuBa(3).Chakukaisu(q) Next q .Range("AW" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuBa(4).Chakukaisu(q) Next q .Range("AX" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuBa(5).Chakukaisu(q) Next q .Range("AY" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuBa(6).Chakukaisu(q) Next q .Range("AZ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(0).Chakukaisu(q) Next q .Range("BA" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(1).Chakukaisu(q) Next q .Range("BB" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(2).Chakukaisu(q) Next q .Range("BC" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(3).Chakukaisu(q) Next q .Range("BD" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(4).Chakukaisu(q) Next q .Range("BE" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(5).Chakukaisu(q) Next q .Range("BF" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(6).Chakukaisu(q) Next q .Range("BG" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(7).Chakukaisu(q) Next q .Range("BH" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(8).Chakukaisu(q) Next q .Range("BI" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(9).Chakukaisu(q) Next q .Range("BJ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(10).Chakukaisu(q) Next q .Range("BK" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuJyotai(11).Chakukaisu(q) Next q .Range("BL" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuKyori(0).Chakukaisu(q) Next q .Range("BM" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuKyori(1).Chakukaisu(q) Next q .Range("BN" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuKyori(2).Chakukaisu(q) Next q .Range("BO" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuKyori(3).Chakukaisu(q) Next q .Range("BP" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuKyori(4).Chakukaisu(q) Next q .Range("BQ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mUmData.ChakuKaisuKyori(5).Chakukaisu(q) Next q .Range("BR" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 3 A$ = A$ & mUmData.Kyakusitu(q) Next q .Range("BS" & 4 + p).Value = "'" & A$ .Range("BT" & 4 + p).Value = "'" & mUmData.RaceCount End With Return '*-------------------------------------------------------- '* ワークシートにタイトルを表示 DisplayTitle: With Worksheets(FileID) .Activate '*タイトル記入 .Cells.Clear .Range("A1").ColumnWidth = 0.5 .Range("B2").Value = FileID & "_テーブル" .Range("B4").Value = "種別ID" .Range("C4").Value = "区分" .Range("D4").Value = "データ作成" .Range("E4").Value = "血統登録番号" .Range("F4").Value = "抹消" .Range("G4").Value = "登録年月日" .Range("H4").Value = "抹消年月日" .Range("I4").Value = "生年月日" .Range("J4").Value = "馬名" .Range("K4").Value = "馬記号" .Range("L4").Value = "性別" .Range("M4").Value = "品種" .Range("N4").Value = "毛色" .Range("O4").Value = "父・馬名" .Range("P4").Value = "母・馬名" .Range("Q4").Value = "父父・馬名" .Range("R4").Value = "父母・馬名" .Range("S4").Value = "母父・馬名" .Range("T4").Value = "母母・馬名" .Range("U4").Value = "父父父･馬名" .Range("V4").Value = "父父母･馬名" .Range("W4").Value = "父母父･馬名" .Range("X4").Value = "父母母･馬名" .Range("Y4").Value = "母父父･馬名" .Range("Z4").Value = "母父母･馬名" .Range("AA4").Value = "母母父･馬名" .Range("AB4").Value = "母母母･馬名" .Range("AC4").Value = "所属" .Range("AD4").Value = "調教師ｺｰﾄﾞ" .Range("AE4").Value = "調教師名" .Range("AF4").Value = "招待地域" .Range("AG4").Value = "生産者ｺｰﾄﾞ" .Range("AH4").Value = "生産者名" .Range("AI4").Value = "産地名" .Range("AJ4").Value = "馬主ｺｰﾄﾞ" .Range("AK4").Value = "馬主名" .Range("AL4").Value = "平地本賞金累計" .Range("AM4").Value = "障害本賞金累計" .Range("AN4").Value = "平地付加賞金累計" .Range("AO4").Value = "障害付加賞金累計" .Range("AP4").Value = "平地収得賞金累計" .Range("AQ4").Value = "障害収得賞金累計" .Range("AR4").Value = "総合着回数" .Range("AS4").Value = "中央合計着回数" .Range("AT4").Value = "芝直･着回数" .Range("AU4").Value = "芝右･着回数" .Range("AV4").Value = "芝左･着回数" .Range("AW4").Value = "ダ直･着回数" .Range("AX4").Value = "ダ右･着回数" .Range("AY4").Value = "ダ左･着回数" .Range("AZ4").Value = "障害･着回数" .Range("BA4").Value = "芝良･着回数" .Range("BB4").Value = "芝梢･着回数" .Range("BC4").Value = "芝重･着回数" .Range("BD4").Value = "芝不･着回数" .Range("BE4").Value = "ダ良･着回数" .Range("BF4").Value = "ダ梢･着回数" .Range("BG4").Value = "ダ重･着回数" .Range("BH4").Value = "ダ不･着回数" .Range("BI4").Value = "障良･着回数" .Range("BJ4").Value = "障梢･着回数" .Range("BK4").Value = "障重･着回数" .Range("BL4").Value = "障不･着回数" .Range("BM4").Value = "芝16下･着回数" .Range("BN4").Value = "芝22下･着回数" .Range("BO4").Value = "芝22超･着回数" .Range("BP4").Value = "ダ16下･着回数" .Range("BQ4").Value = "ダ22下･着回数" .Range("BR4").Value = "ダ22超･着回数" .Range("BS4").Value = "脚質傾向" .Range("BT4").Value = "登録ﾚｰｽ数" End With Return End Sub プログラムのポイント 横方向の表示セル数を節約するために着順成績を１着〜着外を連結しています。各着順データは３ケタなので、１着〜５着、および着外を連結すると１８ケタの文字列になります（頭から１着、２着・・・）。 ２．”騎手マスタ（KS）”データを展開するサブルーチン[DisplayKSDataOnWorksheet] '*********** ワークシートに騎手マスタ（"KS"）データを表示(Top) ************ '[第９回] Sub DisplayKSDataOnWorksheet(ByVal FileID As String, ByVal DataSu As Long) Dim p As Long, q As Long Dim A$ '* ワークシートにタイトルを表示 GoSub DisplayTitle Select Case FileID Case "KS_RCOV" For p = 1 To KsDataSu '* 配列をデータ関数にセット Call SetData_KS(KsData(p), mKsData) '* ワークシートに表示 GoSub DisplayToWorksheet Next p End Select '* テーブル名をセット KsTable = Worksheets(FileID).Range("B2").Value '* セル幅を自動調整 Worksheets(FileID).Range("B4").CurrentRegion.EntireColumn.AutoFit Exit Sub '*-------------------------------------------------------- '* サブルーチン '*-------------------------------------------------------- '* ワークシートに表示 DisplayToWorksheet: With Worksheets(FileID) .Activate .Range("B1").Value = p .Range("B" & 4 + p).Value = mKsData.head.RecordSpec .Range("C" & 4 + p).Value = "'" & mKsData.head.DataKubun .Range("D" & 4 + p).Value = "'" & mKsData.head.MakeDate.Year & _ mKsData.head.MakeDate.Month & mKsData.head.MakeDate.Day .Range("E" & 4 + p).Value = "'" & mKsData.KisyuCode .Range("F" & 4 + p).Value = "'" & mKsData.DelKubun .Range("G" & 4 + p).Value = "'" & mKsData.IssueDate.Year & _ mKsData.IssueDate.Month & mKsData.IssueDate.Day .Range("H" & 4 + p).Value = "'" & mKsData.DelDate.Year & _ mKsData.DelDate.Month & mKsData.DelDate.Day .Range("I" & 4 + p).Value = "'" & mKsData.BirthDate.Year & _ mKsData.BirthDate.Month & mKsData.BirthDate.Day .Range("J" & 4 + p).Value = mKsData.KisyuRyakusyo .Range("K" & 4 + p).Value = mKsData.SexCD .Range("L" & 4 + p).Value = mKsData.SikakuCD .Range("M" & 4 + p).Value = mKsData.MinaraiCD .Range("N" & 4 + p).Value = mKsData.TozaiCD .Range("O" & 4 + p).Value = Left(Trim(mKsData.Syotai), 12) .Range("P" & 4 + p).Value = "'" & mKsData.ChokyosiCode .Range("Q" & 4 + p).Value = mKsData.ChokyosiRyakusyo .Range("R" & 4 + p).Value = mKsData.HonZenRuikei(0).SetYear .Range("S" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(0).HonSyokinHeichi) / 100, "###,###,###") .Range("T" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(0).HonSyokinSyogai) / 100, "###,###,###") .Range("U" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(0).FukaSyokinHeichi) / 100, "###,###,###") .Range("V" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(0).FukaSyokinSyogai) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuHeichi.Chakukaisu(q) Next q .Range("W" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuSyogai.Chakukaisu(q) Next q .Range("X" & 4 + p).Value = "'" & A$ .Range("Y" & 4 + p).Value = mKsData.HonZenRuikei(1).SetYear .Range("Z" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(1).HonSyokinHeichi) / 100, "###,###,###") .Range("AA" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(1).HonSyokinSyogai) / 100, "###,###,###") .Range("AB" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(1).FukaSyokinHeichi) / 100, "###,###,###") .Range("AC" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(0).FukaSyokinSyogai) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(1).ChakuKaisuHeichi.Chakukaisu(q) Next q .Range("AD" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(1).ChakuKaisuSyogai.Chakukaisu(q) Next q .Range("AE" & 4 + p).Value = "'" & A$ .Range("AF" & 4 + p).Value = mKsData.HonZenRuikei(2).SetYear .Range("AG" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(2).HonSyokinHeichi) / 100, "###,###,###") .Range("AH" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(2).HonSyokinSyogai) / 100, "###,###,###") .Range("AI" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(2).FukaSyokinHeichi) / 100, "###,###,###") .Range("AJ" & 4 + p).Value = Format(Val(mKsData.HonZenRuikei(2).FukaSyokinSyogai) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuHeichi.Chakukaisu(q) Next q .Range("AK" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuSyogai.Chakukaisu(q) Next q .Range("AL" & 4 + p).Value = "'" & A$ '* 場所別成績（本年） A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(0).Chakukaisu(q) Next q .Range("AM" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(2).Chakukaisu(q) Next q .Range("AN" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(4).Chakukaisu(q) Next q .Range("AO" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(6).Chakukaisu(q) Next q .Range("AP" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(8).Chakukaisu(q) Next q .Range("AQ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(10).Chakukaisu(q) Next q .Range("AR" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(12).Chakukaisu(q) Next q .Range("AS" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(14).Chakukaisu(q) Next q .Range("AT" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(16).Chakukaisu(q) Next q .Range("AU" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuJyo(18).Chakukaisu(q) Next q .Range("AV" & 4 + p).Value = "'" & A$ '* 場所別成績（累計） A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(0).Chakukaisu(q) Next q .Range("AW" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(2).Chakukaisu(q) Next q .Range("AX" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(4).Chakukaisu(q) Next q .Range("AY" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(6).Chakukaisu(q) Next q .Range("AZ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(8).Chakukaisu(q) Next q .Range("BA" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(10).Chakukaisu(q) Next q .Range("BB" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(12).Chakukaisu(q) Next q .Range("BC" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(14).Chakukaisu(q) Next q .Range("BD" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(16).Chakukaisu(q) Next q .Range("BE" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuJyo(18).Chakukaisu(q) Next q .Range("BF" & 4 + p).Value = "'" & A$ '* 距離別成績（本年） A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuKyori(0).Chakukaisu(q) Next q .Range("BG" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuKyori(1).Chakukaisu(q) Next q .Range("BH" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuKyori(2).Chakukaisu(q) Next q .Range("BI" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuKyori(3).Chakukaisu(q) Next q .Range("BJ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuKyori(4).Chakukaisu(q) Next q .Range("BK" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(0).ChakuKaisuKyori(5).Chakukaisu(q) Next q .Range("BL" & 4 + p).Value = "'" & A$ '* 距離別成績（累計） A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuKyori(0).Chakukaisu(q) Next q .Range("BM" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuKyori(1).Chakukaisu(q) Next q .Range("BN" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuKyori(2).Chakukaisu(q) Next q .Range("BO" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuKyori(3).Chakukaisu(q) Next q .Range("BP" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuKyori(4).Chakukaisu(q) Next q .Range("BQ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mKsData.HonZenRuikei(2).ChakuKaisuKyori(5).Chakukaisu(q) Next q .Range("BR" & 4 + p).Value = "'" & A$ End With Return '*-------------------------------------------------------- '* ワークシートにタイトルを表示 DisplayTitle: With Worksheets(FileID) .Activate '*タイトル記入 .Cells.Clear .Range("A1").ColumnWidth = 0.5 .Range("B2").Value = FileID & "_テーブル" .Range("B4").Value = "種別ID" .Range("C4").Value = "区分" .Range("D4").Value = "データ作成" .Range("E4").Value = "騎手ｺｰﾄﾞ" .Range("F4").Value = "抹消" .Range("G4").Value = "免許年月日" .Range("H4").Value = "抹消年月日" .Range("I4").Value = "生年月日" .Range("J4").Value = "騎手名" .Range("K4").Value = "性別" .Range("L4").Value = "資格" .Range("M4").Value = "見習" .Range("N4").Value = "東西" .Range("O4").Value = "招待地域" .Range("P4").Value = "所属調教師ｺｰﾄﾞ" .Range("Q4").Value = "所属調教師名" .Range("R4").Value = "本年度" .Range("S4").Value = "本年平地本賞金合計" .Range("T4").Value = "本年障害本賞金合計" .Range("U4").Value = "本年平地付加賞金" .Range("V4").Value = "本年障害付加賞金" .Range("W4").Value = "本年平地着回数" .Range("X4").Value = "本年障害着回数" .Range("Y4").Value = "前年度" .Range("Z4").Value = "前年平地本賞金合計" .Range("AA4").Value = "前年障害本賞金合計" .Range("AB4").Value = "前年平地付加賞金" .Range("AC4").Value = "前年障害付加賞金" .Range("AD4").Value = "前年平地着回数" .Range("AE4").Value = "前年障害着回数" .Range("AF4").Value = "累計" .Range("AG4").Value = "累計平地本賞金合計" .Range("AH4").Value = "累計障害本賞金合計" .Range("AI4").Value = "累計平地付加賞金" .Range("AJ4").Value = "累計障害付加賞金" .Range("AK4").Value = "累計平地着回数" .Range("AL4").Value = "累計障害着回数" .Range("AM4").Value = "本年札幌成績" .Range("AN4").Value = "本年函館成績" .Range("AO4").Value = "本年福島成績" .Range("AP4").Value = "本年新潟成績" .Range("AQ4").Value = "本年東京成績" .Range("AR4").Value = "本年中山成績" .Range("AS4").Value = "本年中京成績" .Range("AT4").Value = "本年京都成績" .Range("AU4").Value = "本年阪神成績" .Range("AV4").Value = "本年小倉成績" .Range("AW4").Value = "累計札幌成績" .Range("AX4").Value = "累計函館成績" .Range("AY4").Value = "累計福島成績" .Range("AZ4").Value = "累計新潟成績" .Range("BA4").Value = "累計東京成績" .Range("BB4").Value = "累計中山成績" .Range("BC4").Value = "累計中京成績" .Range("BD4").Value = "累計京都成績" .Range("BE4").Value = "累計阪神成績" .Range("BF4").Value = "累計小倉成績" .Range("BG4").Value = "本年芝16下成績" .Range("BH4").Value = "本年芝22下成績" .Range("BI4").Value = "本年芝22超成績" .Range("BJ4").Value = "本年ダ16下成績" .Range("BK4").Value = "本年ダ22下成績" .Range("BL4").Value = "本年ダ22超成績" .Range("BM4").Value = "累計芝16下成績" .Range("BN4").Value = "累計芝22下成績" .Range("BO4").Value = "累計芝22超成績" .Range("BP4").Value = "累計ダ16下成績" .Range("BQ4").Value = "累計ダ22下成績" .Range("BR4").Value = "累計ダ22超成績" End With Return End Sub プログラムのポイント 横方向の表示セル数を節約するために着順成績を１着〜着外を連結しています。 ３．”調教師マスタ（CH）”データを展開するサブルーチン[DisplayCHDataOnWorksheet] '******** ワークシートに調教師マスタ（"CH"）データを表示(Top) *********** '[第９回] Sub DisplayCHDataOnWorksheet(ByVal FileID As String, ByVal DataSu As Long) Dim p As Long, q As Long Dim A$ '* ワークシートにタイトルを表示 GoSub DisplayTitle Select Case FileID Case "CH_RCOV" For p = 1 To ChDataSu '* 配列をデータ関数にセット Call SetData_CH(ChData(p), mChData) '* ワークシートに表示 GoSub DisplayToWorksheet Next p End Select '* テーブル名をセット ChTable = Worksheets(FileID).Range("B2").Value '* セル幅を自動調整 Worksheets(FileID).Range("B4").CurrentRegion.EntireColumn.AutoFit Exit Sub '*-------------------------------------------------------- '* サブルーチン '*-------------------------------------------------------- '* ワークシートに表示 DisplayToWorksheet: With Worksheets(FileID) .Activate .Range("B1").Value = p .Range("B" & 4 + p).Value = mChData.head.RecordSpec .Range("C" & 4 + p).Value = "'" & mChData.head.DataKubun .Range("D" & 4 + p).Value = "'" & mChData.head.MakeDate.Year & _ mChData.head.MakeDate.Month & mChData.head.MakeDate.Day .Range("E" & 4 + p).Value = "'" & mChData.ChokyosiCode .Range("F" & 4 + p).Value = "'" & mChData.DelKubun .Range("G" & 4 + p).Value = "'" & mChData.IssueDate.Year & _ mChData.IssueDate.Month & mChData.IssueDate.Day .Range("H" & 4 + p).Value = "'" & mChData.DelDate.Year & _ mChData.DelDate.Month & mChData.DelDate.Day .Range("I" & 4 + p).Value = "'" & mChData.BirthDate.Year & _ mChData.BirthDate.Month & mChData.BirthDate.Day .Range("J" & 4 + p).Value = mChData.ChokyosiRyakusyo .Range("K" & 4 + p).Value = mChData.SexCD .Range("L" & 4 + p).Value = mChData.TozaiCD .Range("M" & 4 + p).Value = Left(Trim(mChData.Syotai), 12) .Range("N" & 4 + p).Value = mChData.HonZenRuikei(0).SetYear .Range("O" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(0).HonSyokinHeichi) / 100, "###,###,###") .Range("P" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(0).HonSyokinSyogai) / 100, "###,###,###") .Range("Q" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(0).FukaSyokinHeichi) / 100, "###,###,###") .Range("R" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(0).FukaSyokinSyogai) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuHeichi.Chakukaisu(q) Next q .Range("S" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuSyogai.Chakukaisu(q) Next q .Range("T" & 4 + p).Value = "'" & A$ .Range("U" & 4 + p).Value = mChData.HonZenRuikei(1).SetYear .Range("V" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(1).HonSyokinHeichi) / 100, "###,###,###") .Range("W" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(1).HonSyokinSyogai) / 100, "###,###,###") .Range("X" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(1).FukaSyokinHeichi) / 100, "###,###,###") .Range("Y" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(0).FukaSyokinSyogai) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(1).ChakuKaisuHeichi.Chakukaisu(q) Next q .Range("Z" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(1).ChakuKaisuSyogai.Chakukaisu(q) Next q .Range("AA" & 4 + p).Value = "'" & A$ .Range("AB" & 4 + p).Value = mChData.HonZenRuikei(2).SetYear .Range("AC" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(2).HonSyokinHeichi) / 100, "###,###,###") .Range("AD" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(2).HonSyokinSyogai) / 100, "###,###,###") .Range("AE" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(2).FukaSyokinHeichi) / 100, "###,###,###") .Range("AF" & 4 + p).Value = Format(Val(mChData.HonZenRuikei(2).FukaSyokinSyogai) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuHeichi.Chakukaisu(q) Next q .Range("AG" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuSyogai.Chakukaisu(q) Next q .Range("AH" & 4 + p).Value = "'" & A$ '* 場所別成績（本年） A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(0).Chakukaisu(q) Next q .Range("AI" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(2).Chakukaisu(q) Next q .Range("AJ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(4).Chakukaisu(q) Next q .Range("AK" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(6).Chakukaisu(q) Next q .Range("AL" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(8).Chakukaisu(q) Next q .Range("AM" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(10).Chakukaisu(q) Next q .Range("AN" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(12).Chakukaisu(q) Next q .Range("AO" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(14).Chakukaisu(q) Next q .Range("AP" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(16).Chakukaisu(q) Next q .Range("AQ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuJyo(18).Chakukaisu(q) Next q .Range("AR" & 4 + p).Value = "'" & A$ '* 場所別成績（累計） A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(0).Chakukaisu(q) Next q .Range("AS" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(2).Chakukaisu(q) Next q .Range("AT" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(4).Chakukaisu(q) Next q .Range("AU" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(6).Chakukaisu(q) Next q .Range("AV" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(8).Chakukaisu(q) Next q .Range("AW" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(10).Chakukaisu(q) Next q .Range("AX" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(12).Chakukaisu(q) Next q .Range("AY" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(14).Chakukaisu(q) Next q .Range("AZ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(16).Chakukaisu(q) Next q .Range("BA" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuJyo(18).Chakukaisu(q) Next q .Range("BB" & 4 + p).Value = "'" & A$ '* 距離別成績（本年） A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuKyori(0).Chakukaisu(q) Next q .Range("BC" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuKyori(1).Chakukaisu(q) Next q .Range("BD" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuKyori(2).Chakukaisu(q) Next q .Range("BE" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuKyori(3).Chakukaisu(q) Next q .Range("BF" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuKyori(4).Chakukaisu(q) Next q .Range("BG" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(0).ChakuKaisuKyori(5).Chakukaisu(q) Next q .Range("BH" & 4 + p).Value = "'" & A$ '* 距離別成績（累計） A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuKyori(0).Chakukaisu(q) Next q .Range("BI" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuKyori(1).Chakukaisu(q) Next q .Range("BJ" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuKyori(2).Chakukaisu(q) Next q .Range("BK" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuKyori(3).Chakukaisu(q) Next q .Range("BL" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuKyori(4).Chakukaisu(q) Next q .Range("BM" & 4 + p).Value = "'" & A$ A$ = "" For q = 0 To 5 A$ = A$ & mChData.HonZenRuikei(2).ChakuKaisuKyori(5).Chakukaisu(q) Next q .Range("BN" & 4 + p).Value = "'" & A$ End With Return '*-------------------------------------------------------- '* ワークシートにタイトルを表示 DisplayTitle: With Worksheets(FileID) .Activate '*タイトル記入 .Cells.Clear .Range("A1").ColumnWidth = 0.5 .Range("B2").Value = FileID & "_テーブル" .Range("B4").Value = "種別ID" .Range("C4").Value = "区分" .Range("D4").Value = "データ作成" .Range("E4").Value = "調教師ｺｰﾄﾞ" .Range("F4").Value = "抹消" .Range("G4").Value = "免許年月日" .Range("H4").Value = "抹消年月日" .Range("I4").Value = "生年月日" .Range("J4").Value = "調教師名" .Range("K4").Value = "性別" .Range("L4").Value = "所属" .Range("M4").Value = "招待地域" .Range("N4").Value = "本年度" .Range("O4").Value = "本年平地本賞金合計" .Range("P4").Value = "本年障害本賞金合計" .Range("Q4").Value = "本年平地付加賞金" .Range("R4").Value = "本年障害付加賞金" .Range("S4").Value = "本年平地着回数" .Range("T4").Value = "本年障害着回数" .Range("U4").Value = "前年度" .Range("V4").Value = "前年平地本賞金合計" .Range("W4").Value = "前年障害本賞金合計" .Range("X4").Value = "前年平地付加賞金" .Range("Y4").Value = "前年障害付加賞金" .Range("Z4").Value = "前年平地着回数" .Range("AA4").Value = "前年障害着回数" .Range("AB4").Value = "累計" .Range("AC4").Value = "累計平地本賞金合計" .Range("AD4").Value = "累計障害本賞金合計" .Range("AE4").Value = "累計平地付加賞金" .Range("AF4").Value = "累計障害付加賞金" .Range("AG4").Value = "累計平地着回数" .Range("AH4").Value = "累計障害着回数" .Range("AI4").Value = "本年札幌成績" .Range("AJ4").Value = "本年函館成績" .Range("AK4").Value = "本年福島成績" .Range("AL4").Value = "本年新潟成績" .Range("AM4").Value = "本年東京成績" .Range("AN4").Value = "本年中山成績" .Range("AO4").Value = "本年中京成績" .Range("AP4").Value = "本年京都成績" .Range("AQ4").Value = "本年阪神成績" .Range("AR4").Value = "本年小倉成績" .Range("AS4").Value = "累計札幌成績" .Range("AT4").Value = "累計函館成績" .Range("AU4").Value = "累計福島成績" .Range("AV4").Value = "累計新潟成績" .Range("AW4").Value = "累計東京成績" .Range("AX4").Value = "累計中山成績" .Range("AY4").Value = "累計中京成績" .Range("AZ4").Value = "累計京都成績" .Range("BA4").Value = "累計阪神成績" .Range("BB4").Value = "累計小倉成績" .Range("BC4").Value = "本年芝16下成績" .Range("BD4").Value = "本年芝22下成績" .Range("BE4").Value = "本年芝22超成績" .Range("BF4").Value = "本年ダ16下成績" .Range("BG4").Value = "本年ダ22下成績" .Range("BH4").Value = "本年ダ22超成績" .Range("BI4").Value = "累計芝16下成績" .Range("BJ4").Value = "累計芝22下成績" .Range("BK4").Value = "累計芝22超成績" .Range("BL4").Value = "累計ダ16下成績" .Range("BM4").Value = "累計ダ22下成績" .Range("BN4").Value = "累計ダ22超成績" End With Return End Sub プログラムのポイント 横方向の表示セル数を節約するために着順成績を１着〜着外を連結しています。 ４．”生産者マスタ（BR）”データを展開するサブルーチン[DisplayBROnWorksheet] '*********** ワークシートに生産者マスタ（"BR"）データを表示(Top) ********** '[第９回] Sub DisplayBRDataOnWorksheet(ByVal FileID As String, ByVal DataSu As Long) Dim p As Long, q As Long Dim A$ '* ワークシートにタイトルを表示 GoSub DisplayTitle Select Case FileID Case "BR_RCOV" For p = 1 To BrDataSu '* 配列をデータ関数にセット Call SetData_BR(BrData(p), mBrData) '* ワークシートに表示 GoSub DisplayToWorksheet Next p End Select '* テーブル名をセット BrTable = Worksheets(FileID).Range("B2").Value '* セル幅を自動調整 Worksheets(FileID).Range("B4").CurrentRegion.EntireColumn.AutoFit Exit Sub '*-------------------------------------------------------- '* サブルーチン '*-------------------------------------------------------- '* ワークシートに表示 DisplayToWorksheet: With Worksheets(FileID) .Activate .Range("B1").Value = p .Range("B" & 4 + p).Value = mBrData.head.RecordSpec .Range("C" & 4 + p).Value = "'" & mBrData.head.DataKubun .Range("D" & 4 + p).Value = "'" & mBrData.head.MakeDate.Year & _ mBrData.head.MakeDate.Month & mBrData.head.MakeDate.Day .Range("E" & 4 + p).Value = "'" & mBrData.BreederCode .Range("F" & 4 + p).Value = Left(Trim(mBrData.BreederName), 20) .Range("G" & 4 + p).Value = Left(Trim(mBrData.Address), 12) .Range("H" & 4 + p).Value = mBrData.HonRuikei(0).SetYear .Range("I" & 4 + p).Value = Format(Val(mBrData.HonRuikei(0).HonSyokinTotal) / 100, "###,###,###") .Range("J" & 4 + p).Value = Format(Val(mBrData.HonRuikei(0).Fukasyokin) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mBrData.HonRuikei(0).Chakukaisu(q) Next q .Range("K" & 4 + p).Value = "'" & A$ .Range("L" & 4 + p).Value = mBrData.HonRuikei(1).SetYear .Range("M" & 4 + p).Value = Format(Val(mBrData.HonRuikei(1).HonSyokinTotal) / 100, "###,###,###") .Range("N" & 4 + p).Value = Format(Val(mBrData.HonRuikei(1).Fukasyokin) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mBrData.HonRuikei(1).Chakukaisu(q) Next q .Range("O" & 4 + p).Value = "'" & A$ End With Return '*-------------------------------------------------------- '* ワークシートにタイトルを表示 DisplayTitle: With Worksheets(FileID) .Activate '*タイトル記入 .Cells.Clear .Range("A1").ColumnWidth = 0.5 .Range("B2").Value = FileID & "_テーブル" .Range("B4").Value = "種別ID" .Range("C4").Value = "区分" .Range("D4").Value = "データ作成" .Range("E4").Value = "生産者ｺｰﾄﾞ" .Range("F4").Value = "生産者名" .Range("G4").Value = "所在地" .Range("H4").Value = "本年度" .Range("I4").Value = "本年本賞金合計" .Range("J4").Value = "本年付加賞金合計" .Range("K4").Value = "本年着回数" .Range("L4").Value = "累計" .Range("M4").Value = "累計本賞金合計" .Range("N4").Value = "累計付加賞金合計" .Range("O4").Value = "累計着回数" End With Return End Sub プログラムのポイント 横方向の表示セル数を節約するために着順成績を１着〜着外を連結しています。 ５．”馬主マスタ（BN）”データを展開するサブルーチン[DisplayBNDataOnWorksheet] '************ ワークシートに馬主マスタ（"BN"）データを表示(Top) ********** '[第９回] Sub DisplayBNDataOnWorksheet(ByVal FileID As String, ByVal DataSu As Long) Dim p As Long, q As Long Dim A$ '* ワークシートにタイトルを表示 GoSub DisplayTitle Select Case FileID Case "BN_RCOV" For p = 1 To BnDataSu '* 配列をデータ関数にセット Call SetData_BN(BnData(p), mBnData) '* ワークシートに表示 GoSub DisplayToWorksheet Next p End Select '* テーブル名をセット BnTable = Worksheets(FileID).Range("B2").Value '* セル幅を自動調整 Worksheets(FileID).Range("B4").CurrentRegion.EntireColumn.AutoFit Exit Sub '*-------------------------------------------------------- '* サブルーチン '*-------------------------------------------------------- '* ワークシートに表示 DisplayToWorksheet: With Worksheets(FileID) .Activate .Range("B1").Value = p .Range("B" & 4 + p).Value = mBnData.head.RecordSpec .Range("C" & 4 + p).Value = "'" & mBnData.head.DataKubun .Range("D" & 4 + p).Value = "'" & mBnData.head.MakeDate.Year & _ mBnData.head.MakeDate.Month & mBnData.head.MakeDate.Day .Range("E" & 4 + p).Value = "'" & mBnData.BanusiCode .Range("F" & 4 + p).Value = Left(Trim(mBnData.BanusiName), 20) .Range("G" & 4 + p).Value = Left(Trim(mBnData.Fukusyoku), 12) .Range("H" & 4 + p).Value = mBnData.HonRuikei(0).SetYear .Range("I" & 4 + p).Value = Format(Val(mBnData.HonRuikei(0).HonSyokinTotal) / 100, "###,###,###") .Range("J" & 4 + p).Value = Format(Val(mBnData.HonRuikei(0).Fukasyokin) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mBnData.HonRuikei(0).Chakukaisu(q) Next q .Range("K" & 4 + p).Value = "'" & A$ .Range("L" & 4 + p).Value = mBnData.HonRuikei(1).SetYear .Range("M" & 4 + p).Value = Format(Val(mBnData.HonRuikei(1).HonSyokinTotal) / 100, "###,###,###") .Range("N" & 4 + p).Value = Format(Val(mBnData.HonRuikei(1).Fukasyokin) / 100, "###,###,###") A$ = "" For q = 0 To 5 A$ = A$ & mBnData.HonRuikei(1).Chakukaisu(q) Next q .Range("O" & 4 + p).Value = "'" & A$ End With Return '*-------------------------------------------------------- '* ワークシートにタイトルを表示 DisplayTitle: With Worksheets(FileID) .Activate '*タイトル記入 .Cells.Clear .Range("A1").ColumnWidth = 0.5 .Range("B2").Value = FileID & "_テーブル" .Range("B4").Value = "種別ID" .Range("C4").Value = "区分" .Range("D4").Value = "データ作成" .Range("E4").Value = "馬主ｺｰﾄﾞ" .Range("F4").Value = "馬主名" .Range("G4").Value = "服色標示" .Range("H4").Value = "本年度" .Range("I4").Value = "本年本賞金合計" .Range("J4").Value = "本年付加賞金合計" .Range("K4").Value = "本年着回数" .Range("L4").Value = "累計" .Range("M4").Value = "累計本賞金合計" .Range("N4").Value = "累計付加賞金合計" .Range("O4").Value = "累計着回数" End With Return End Sub プログラムのポイント 横方向の表示セル数を節約するために着順成績を１着〜着外を連結しています。 ６．”レコードマスタ（RC）”データを展開するサブルーチン[DisplayRCDataOnWorksheet] '********** ワークシートにレコードマスタ（"RC"）データを表示(Top) ********** '[第９回] Sub DisplayRCDataOnWorksheet(ByVal FileID As String, ByVal DataSu As Long) Dim p As Long, q As Long Dim A$ '* ワークシートにタイトルを表示 GoSub DisplayTitle Select Case FileID Case "RC_RCOV" For p = 1 To RcDataSu '* 配列をデータ関数にセット Call SetData_RC(RcData(p), mRcData) '* ワークシートに表示 GoSub DisplayToWorksheet Next p End Select '* テーブル名をセット RcTable = Worksheets(FileID).Range("B2").Value '* セル幅を自動調整 Worksheets(FileID).Range("B4").CurrentRegion.EntireColumn.AutoFit Exit Sub '*-------------------------------------------------------- '* サブルーチン '*-------------------------------------------------------- '* ワークシートに表示 DisplayToWorksheet: With Worksheets(FileID) .Activate .Range("B1").Value = p .Range("B" & 4 + p).Value = mRcData.head.RecordSpec .Range("C" & 4 + p).Value = "'" & mRcData.head.DataKubun .Range("D" & 4 + p).Value = "'" & mRcData.head.MakeDate.Year & _ mRcData.head.MakeDate.Month & mRcData.head.MakeDate.Day .Range("E" & 4 + p).Value = "'" & mRcData.RecInfoKubun .Range("F" & 4 + p).Value = "'" & mRcData.ID.Year & mRcData.ID.MonthDay .Range("G" & 4 + p).Value = "'" & mRcData.ID.JyoCD .Range("H" & 4 + p).Value = "'" & mRcData.ID.Kaiji .Range("I" & 4 + p).Value = "'" & mRcData.ID.Nichiji .Range("J" & 4 + p).Value = "'" & mRcData.ID.RaceNum .Range("K" & 4 + p).Value = "'" & mRcData.TokuNum .Range("L" & 4 + p).Value = Left(Trim(mRcData.Hondai), 15) .Range("M" & 4 + p).Value = mRcData.GradeCD .Range("N" & 4 + p).Value = "'" & mRcData.SyubetuCD .Range("O" & 4 + p).Value = mRcData.Kyori .Range("P" & 4 + p).Value = "'" & mRcData.TrackCD .Range("Q" & 4 + p).Value = mRcData.RecKubun .Range("R" & 4 + p).Value = mRcData.RecTime .Range("S" & 4 + p).Value = mRcData.TenkoBaba.TenkoCD .Range("T" & 4 + p).Value = mRcData.TenkoBaba.SibaBabaCD .Range("U" & 4 + p).Value = mRcData.TenkoBaba.DirtBabaCD .Range("V" & 4 + p).Value = "'" & mRcData.RecUmaInfo(0).KettoNum .Range("W" & 4 + p).Value = Left(Trim(mRcData.RecUmaInfo(0).Bamei), 9) .Range("X" & 4 + p).Value = "'" & mRcData.RecUmaInfo(0).UmaKigoCD .Range("Y" & 4 + p).Value = "'" & mRcData.RecUmaInfo(0).SexCD .Range("Z" & 4 + p).Value = "'" & mRcData.RecUmaInfo(0).ChokyosiCode .Range("AA" & 4 + p).Value = Left(Trim(mRcData.RecUmaInfo(0).ChokyosiName), 6) .Range("AB" & 4 + p).Value = "'" & mRcData.RecUmaInfo(0).Futan .Range("AC" & 4 + p).Value = "'" & mRcData.RecUmaInfo(0).KisyuCode .Range("AD" & 4 + p).Value = Left(Trim(mRcData.RecUmaInfo(0).KisyuName), 6) End With Return '*-------------------------------------------------------- '* ワークシートにタイトルを表示 DisplayTitle: With Worksheets(FileID) .Activate '*タイトル記入 .Cells.Clear .Range("A1").ColumnWidth = 0.5 .Range("B2").Value = FileID & "_テーブル" .Range("B4").Value = "種別ID" .Range("C4").Value = "区分" .Range("D4").Value = "データ作成" .Range("E4").Value = "ﾚｺｰﾄﾞ識別" .Range("F4").Value = "開催年月日" .Range("G4").Value = "場所" .Range("H4").Value = "回次" .Range("I4").Value = "日次" .Range("J4").Value = "ﾚｰｽNo" .Range("K4").Value = "特別競走" .Range("L4").Value = "ﾚｰｽ名" .Range("M4").Value = "ｸﾞﾚｰﾄﾞ" .Range("N4").Value = "競走種別" .Range("O4").Value = "距離" .Range("P4").Value = "ｺｰｽ" .Range("Q4").Value = "ﾚｺｰﾄﾞ区分" .Range("R4").Value = "ﾀｲﾑ" .Range("S4").Value = "天候" .Range("T4").Value = "芝馬場" .Range("U4").Value = "ダ馬場" .Range("V4").Value = "ﾚｺｰﾄﾞ馬血統番号" .Range("W4").Value = "馬名" .Range("X4").Value = "馬記号" .Range("Y4").Value = "性別" .Range("Z4").Value = "調教師ｺｰﾄﾞ" .Range("AA4").Value = "調教師名" .Range("AB4").Value = "負担" .Range("AC4").Value = "騎手ｺｰﾄﾞ" .Range("AD4").Value = "騎手名" End With Return End Sub ”RCOV”データの中の”RA”と”SE”については先に作ったサブルーチンを使います。 ７．次に前回同様に前回のサブルーチン[Worksheet_BeforeDoubleClick]の中のサブルーチンに追加します(Topシート）。 下記のコードを追加します。追加するのは２種類あります。 （１）内部サブルーチン"OpenFileData"の中に追加するコード 追加する場所は前回の　Case "O6" のブロックの下と　End Select の間です。 '***********第９回で追加するコードは以下に追加************** '[第９回] Case "CH" '* 調教師マスタ ChDataSu = FileLen(CopyFile$) / ChDataSize ReDim ChData(1 To ChDataSu) As String For q = 1 To ChDataSu Line Input #FN, ChData(q) Next q Case "BN" '* 馬主マスタ BnDataSu = FileLen(CopyFile$) / BnDataSize ReDim BnData(1 To BnDataSu) As String For q = 1 To BnDataSu Line Input #FN, BnData(q) Next q Case "BR" '* 生産者マスタ BrDataSu = FileLen(CopyFile$) / BrDataSize ReDim BrData(1 To BrDataSu) As String For q = 1 To BrDataSu Line Input #FN, BrData(q) Next q Case "KS" '* 騎手マスタ KsDataSu = FileLen(CopyFile$) / KsDataSize ReDim KsData(1 To KsDataSu) As String For q = 1 To KsDataSu Line Input #FN, KsData(q) Next q Case "UM" '* 競走馬マスタ UmDataSu = FileLen(CopyFile$) / UmDataSize ReDim UmData(1 To UmDataSu) As String For q = 1 To UmDataSu Line Input #FN, UmData(q) Next q Case "RC" '* レコードマスタ RcDataSu = FileLen(CopyFile$) / RcDataSize ReDim RcData(1 To RcDataSu) As String For q = 1 To RcDataSu Line Input #FN, RcData(q) Next q '[第９回]ここまで （２）内部サブルーチン[JVDataSetAndDisplay]の中に追加するコード 追加する場所は前回の Case "O6" 　　Call DisplayO6DataOnWorksheet(FileID, O6DataSu) とend Select の間です。 '************第９回で追加するコードは以下に************* Case "UM" '* 競走馬マスタ表示 Call DisplayUMDataOnWorksheet(FileID, UmDataSu) Case "KS" '* 騎手マスタ表示 Call DisplayKSDataOnWorksheet(FileID, KsDataSu) Case "CH" '* 調教師マスタ表示 Call DisplayCHDataOnWorksheet(FileID, ChDataSu) Case "BR" '* 生産者マスタ表示 Call DisplayBRDataOnWorksheet(FileID, BrDataSu) Case "BN" '* 馬主マスタ表示 Call DisplayBNDataOnWorksheet(FileID, BnDataSu) Case "RC" '* レコードマスタ表示 Call DisplayRCDataOnWorksheet(FileID, RcDataSu) '[第９回]ここまで 以上で、”レース補てん情報[RCOV]”データをワークシートに展開するサブルーチンは完了です。 上書き保存してください。 ８．プログラムの実行 ファイル名をダブルクリックするとそれぞれのシートにデータが展開されると思います。 残るのは“速報系データ”用のみです。ようやく終わりが見えてきました。 ページトップへ
Copyright(C):Jaba 2000-2016