Sub test1()
Range(Cells(3, 2), Cells(2, 2).End(xlToRight).End(xlDown)).Select End Sub |
Sub test2()
Cells(2, 2).CurrentRegion.Offset(1, 0).Select End Sub |
Sub test3()
Cells(2, 2).CurrentRegion.Offset(1, 0).Resize(Cells(2, 2).CurrentRegion.Rows.Count - 1).Select End Sub |
Sub test4()
Range(Cells(3, 2), Cells(2, 2).CurrentRegion.Item(Cells(2, 2).CurrentRegion.Count)).Select End Sub |
Sub test5()
Range(Cells(3, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, Columns.Count).End(xlToLeft)).Select End Sub |
Sub test6()
Range(Rows(3), Rows(Cells(Rows.Count, 2).End(xlUp).Row)).Select End Sub |
Sub test7() Range(Cells(3, 2), ActiveSheet.UsedRange.Item(ActiveSheet.UsedRange.Count)).Select End Sub |
Sub test8()
Range(Cells(3, 2), Cells.SpecialCells(xlLastCell)).Select End Sub |
Sub test9()
Intersect(Cells(2, 2).CurrentRegion, Cells(2, 2).CurrentRegion.Offset(1, 0)).Select End Sub |
Sub sample1()
Dim i As Long For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 Rows(i).Insert Next i End Sub |
Sub sample2()
Dim i As Long For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1 Columns(i).Insert Next i End Sub |
Sub sample1() Sheets.Add ActiveSheet.Name = "新規シート" ' ・・・いろいろな処理 Application.DisplayAlerts = False Sheets("新規シート").Delete Application.DisplayAlerts = True End Sub |
Sub sample2() Dim NewSht As Worksheet Set NewSht = Sheets.Add NewSht.Name = "新規シート" ' ・・・いろいろな処理 Application.DisplayAlerts = False NewSht.Delete Application.DisplayAlerts = True End Sub |
Sub sample1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select End Sub |
Sub sample2()
Dim selSht As Variant Dim i As Long ReDim selSht(1 To Sheets.Count) For i = 1 To Sheets.Count selSht(i) = Sheets(i).Name Next i Sheets(selSht).Select End Sub |
Sub sample3()
Dim mySht As Variant For Each mySht In Sheets mySht.Select False Next End Sub |
Sub sample4()
Sheets.Select End Sub |
Sub sample1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintOut End Sub |
Sub sample2()
Dim i As Integer Dim mySht As Variant For Each mySht In ActiveWorkbook.Sheets If TypeName(mySht) = "Worksheet" Then mySht.PrintOut End If Next End Sub |
Sub sample3() Dim mySht As Variant Dim i As Long For i = 1 To Sheets.Count If TypeName(Sheets(i)) = "Worksheet" Then If IsEmpty(mySht) Then ReDim mySht(0) Else ReDim Preserve mySht(UBound(mySht) + 1) End If mySht(UBound(mySht)) = Sheets(i).Name End If Next i If Not IsEmpty(mySht) Then Sheets(mySht).PrintOut End If End Sub |
Sub sample4() Dim mySht As Variant Dim FirstFlg As Boolean FirstFlg = False For Each mySht In ActiveWorkbook.Sheets If TypeName(mySht) = "Worksheet" Then If FirstFlg = False Then mySht.Select FirstFlg = True Else mySht.Select False End If End If Next If FirstFlg = True Then ActiveWindow.SelectedSheets.PrintOut End If End Sub |
Sub sample5()
ActiveWorkbook.PrintOut End Sub |
Sub sample1()
Worksheets("Sheet1").Columns("A:B").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Worksheets("Sheet2").Range("A1"), Unique:=True End Sub |
Sub sample2()
Worksheets("Sheet1").Range("A:B").AdvancedFilter Action:=xlFilterInPlace, Unique:=True Worksheets("Sheet1").Range("A1").CurrentRegion.Resize(, 2).SpecialCells(xlCellTypeVisible).Copy _ Worksheets("Sheet2").Range("A1") Worksheets("Sheet1").ShowAllData End Sub |
Sub sample3()
Dim oldCode() As Variant Dim i As Long, j As Long Dim ws2 As Worksheet Set ws2 = Worksheets("Sheet2") With Worksheets("Sheet1") ReDim oldCode(1 To 2) ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, 2)).Value = .Range(.Cells(1, 1), .Cells(1, 2)).Value j = 2 For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row If oldCode(1) <> .Cells(i, 1) Or _ oldCode(2) <> .Cells(i, 2) Then ws2.Cells(j, 1) = .Cells(i, 1) ws2.Cells(j, 2) = .Cells(i, 2) oldCode(1) = .Cells(i, 1) oldCode(2) = .Cells(i, 2) j = j + 1 End If Next i End With End Sub |
Sub Bookを開く() Dim wb As Workbook Dim buf As String Dim BookPath As String BookPath = "C:\Book1.xls" 'ブックの存在チェック buf = Dir(BookPath) If buf = "" Then MsgBox BookPath & vbCrLf & "は存在しません。" Exit Sub End If '同名ブックのチェック For Each wb In Application.Workbooks If wb.Name = buf Then wb.Activate '指定ブックに移動 MsgBox buf & vbCrLf & "は既に開いています" Exit Sub End If Next 'イベントを停止 Application.EnableEvents = False 'ブックを開く Workbooks.Open BookPath Application.EnableEvents = True End Sub |
Sub sample1()
ThisWorkbook.Save ThisWorkbook.Close End Sub |
Sub sample2()
ThisWorkbook.Close SaveChanges:=True End Sub |
Sub sample4()
ThisWorkbook.Close SaveChanges:=False End Sub |
Sub sample3()
ThisWorkbook.Saved = True ThisWorkbook.Close End Sub |
Sub sample5()
If ThisWorkbook.Saved = False Then ThisWorkbook.Save End If ThisWorkbook.Close End Sub |
Sub sample1()
Range("B2:F11").Borders().Weight = xlHairline Range("B2:F11").BorderAround Weight:=xlMedium Range("B3:F3").Borders(xlEdgeTop).Weight = xlThin Range("C2:C11").Borders(xlEdgeLeft).Weight = xlThin End Sub |
Sub マクロ開始()
Application.ScreenUpdating = False '画面描画を停止 Application.EnableEvents = False 'イベントを抑止 Application.DisplayAlerts = False '確認メッセージを抑止 Application.Calculation = xlCalculationManual '計算を手動に End Sub |
Sub マクロ終了()
Application.StatusBar = False 'ステータスバーを消す Application.Calculation = xlCalculationAutomatic '計算を自動に Application.DisplayAlerts = True '確認メッセージを開始 Application.EnableEvents = True 'イベントを開始 Application.ScreenUpdating = True '画面描画を開始 End Sub |
Sub sample() Dim FilterRange As Range With Worksheets("Sheet1") Set FilterRange = .Range("A1").CurrentRegion End With FilterRange.AutoFilter Field:=1, Criteria1:="○×△" With Worksheets("Sheet2") .UsedRange.ClearContents FilterRange.SpecialCells(xlCellTypeVisible).Copy .Range("A1") End With End Sub |
Sub sample2() If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter End If '通常のフィルター処理を行う End Sub |
Sub Macro1()
Range("A1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$B$11").AutoFilter Field:=1, Operator:= _ xlFilterValues, Criteria2:=Array(2, "6/5/2011") End Sub |
Sub Macro1() Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="2011/6/5" End Sub |
Sub Macro1()
ActiveSheet.Range("$A$1:$B$11").AutoFilter Field:=1, _ Criteria1:=Format(CDate("2011/6/5"), Range("A2").NumberFormatLocal) End Sub |
Sub Macro1()
ActiveSheet.Range("$A$1:$B$11").AutoFilter Field:=1, _ Criteria1:=">=" & CLng(CDate("2011/6/5")), _ Operator:=xlAnd, _ Criteria2:="<=" & CLng(CDate("2011/6/5")) End Sub |
Sub sample()
Dim rtn As Boolean ActiveSheet.Copy ActiveSheet.Cells.Interior.Color = xlNone rtn = Application.Dialogs(xlDialogPrint).Show ActiveWorkbook.Close SaveChanges:=False Select Case rtn Case True MsgBox "印刷されました。" Case False MsgBox "印刷がキャンセルされました。" End Select End Sub |
Sub sample2()
Dim rtn As Boolean Dim strSht As Variant Dim i As Integer strSht = Array("印刷しないシート1", "印刷しないシート2") For i = LBound(strSht) To UBound(strSht) Sheets(strSht(i)).Visible = False Next i rtn = Application.Dialogs(xlDialogPrint).Show For i = LBound(strSht) To UBound(strSht) Sheets(strSht(i)).Visible = True Next i Select Case rtn Case True MsgBox "印刷されました。" Case False MsgBox "印刷がキャンセルされました。" End Select End Sub |
Sub sample()
Dim nm As Name Dim i As Long i = 1 For Each nm In ActiveWorkbook.Names Cells(i, 1) = nm.Name Cells(i, 2) = "'" & nm.Value Cells(i, 3) = nm.Parent.Name i = i + 1 Next End Sub |
Sub sample2()
Dim nm As Name For Each nm In ActiveWorkbook.Names nm.Visible = True Next End Sub |
Sub sample3()
Dim nm As Name On Error Resume Next For Each nm In ActiveWorkbook.Names nm.Delete Next On Error GoTo 0 End Sub |
Sub sample4()
Dim nm As Name On Error Resume Next For Each nm In ActiveWorkbook.Names If nm.Value Like "*[#]REF!*" Then nm.Delete End If Next On Error GoTo 0 End Sub |
Sub sample5()
Dim i As Long On Error Resume Next For i = 1 To ActiveWorkbook.Names.Count ActiveWorkbook.Names(i).Delete Next On Error GoTo 0 End Sub |
Sub sample()
Dim myRange As Range For Each myRange In Cells.SpecialCells(xlCellTypeComments) With myRange.Comment.Shape .Top = myRange.Top .Left = myRange.Offset(, 1).Left '以下でも同じ '.Left = myRange.Left + myRange.Width .TextFrame.AutoSize = True .TextFrame.Characters.Font.Size = 11 .TextFrame.Characters.Font.Color = vbBlue End With Next End Sub |
Sub sample()
Dim i As Long, j As Long Dim ary ary = ActiveSheet.UsedRange For i = LBound(ary, 1) To UBound(ary, 1) For j = LBound(ary, 2) To UBound(ary, 2) If ary(i, j) = Date Then ActiveSheet.UsedRange.Cells(i, j).Select Exit Sub End If Next j Next i End Sub |
Sub sample1()
Dim strBookName As String Workbooks.Open "ブックのフルパス" strBookName = ActiveWorkbook.Name ・・・処理 Workbooks(strBookName).Close SaveChanges:=False End Sub |
Sub sample2()
Dim wb As Workbook Set wb = Workbooks.Open("ブックのフルパス") ・・・処理 wb.Close SaveChanges:=False End Sub |
Sub sample3()
Dim i As Integer For i = 1 To Workbooks.Count If Workbooks(i).Name Like "*○○○*.xls" Then Workbooks(i).Close SaveChanges:=False End If Next i End Sub |
Sub sample4()
Dim wb As Workbook For Each wb In Workbooks If InStr(wb.Name, "○○○") > 0 Then wb.Close SaveChanges:=False End If Next End Sub |
Sub sample()
Dim i As Long With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) For i = 1 To .Points.Count .Points(i).Interior.Color = Cells(i + 1, 1).Interior.Color Next i End With End Sub |
Private blnStop As Boolean
Private blnStart As Boolean Sub StopWatch() Dim dblTimer As Double If blnStart = True Then blnStop = True Exit Sub End If blnStart = True blnStop = False dblTimer = Timer Do Until blnStop = True Cells(1, 1) = Int((Timer - dblTimer) * 100) / 100 DoEvents Loop blnStart = False blnStop = False End Sub |
Sub sample1()
Dim LastRow As Long With Worksheets("元データ") LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row .Cells(1, 2) = "作業列" .Range(.Cells(2, 2), .Cells(LastRow, 2)) = "=COUNTIF($A$2:A2,A2)" .Cells(1, 1).AutoFilter Field:=2, Criteria1:=1 .Range(.Cells(1, 1), .Cells(LastRow, 1)).SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("ユニーク").Cells(1, 1) .Cells(1, 1).AutoFilter .Columns(2).Clear End With End Sub |
Sub sample2()
Dim LastRow As Long With Worksheets("元データ") LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row Worksheets("ユニーク").Cells(1, 1) = .Cells(1, 1) Call sample2sub(.Range(.Cells(2, 1), .Cells(LastRow, 1)), Worksheets("ユニーク").Cells(2, 1)) End With End Sub Sub sample2sub(MyRange As Range, Target As Range) Dim i As Long Dim blnFind As Boolean Dim MyArray Dim MyCell As Range For Each MyCell In MyRange If Not IsArray(MyArray) Then ReDim MyArray(1 To 1, 1 To 1) MyArray(1, UBound(MyArray, 2)) = MyCell Else blnFind = False For i = LBound(MyArray, 2) To UBound(MyArray, 2) If MyArray(1, i) = MyCell Then blnFind = True Exit For End If Next i If blnFind = False Then ReDim Preserve MyArray(1 To 1, 1 To UBound(MyArray, 2) + 1) MyArray(1, UBound(MyArray, 2)) = MyCell End If End If Next Target.Resize(UBound(MyArray, 2), 1) = WorksheetFunction.Transpose(MyArray) End Sub |
Sub sample3()
Dim LastRow As Long With Worksheets("元データ") LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row .Range(.Cells(1, 1), .Cells(LastRow, 1)).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Worksheets("ユニーク").Cells(1, 1), _ Unique:=True End With End Sub |
Sub sample1()
Dim i As Integer '系列のFor~Nextで使用 Dim rowMax As Long 'グラフ範囲の最終行 Dim colMax As Long 'グラフ範囲の最終列 Dim MyRange As Range 'グラフ範囲 Dim chartObj As ChartObject 'Chartオブジェクトのコンテナ rowMax = Cells(Rows.Count, 1).End(xlUp).Row 'グラフ範囲の最終行 colMax = Cells(2, Columns.Count).End(xlToLeft).Column 'グラフ範囲の最終列 Set MyRange = Range(Cells(1, 1), Cells(rowMax, colMax)) 'グラフ範囲 'Chartを追加、グラフ範囲の右隣に、グラフ範囲の倍の大きさで作成 Set chartObj = ActiveSheet.ChartObjects.Add(MyRange.Width, MyRange.Top, MyRange.Width * 2, MyRange.Height * 2) '追加されたChartオブジェクトに対する処理 With chartObj.Chart '元データ範囲の設定 .SetSourceData MyRange 'タイトル表示 .HasTitle = True .ChartTitle.Text = "=" & MyRange.Cells(1, 1).Address(ReferenceStyle:=xlR1C1, External:=True) '全系列に対する処理 For i = 1 To .SeriesCollection.Count With .SeriesCollection(i) Select Case i Case 1, 3 '売上金額 .ChartType = xlColumnClustered '縦棒グラフ .AxisGroup = 1 '主軸 .ApplyDataLabels 'データラベル表示 .DataLabels.NumberFormatLocal = "#,##," 'データラベルの表示形式 .Interior.Color = Cells(2, i + 1).Interior.Color '棒グラフの色 Case 2, 4 '昨年比 .ChartType = xlLine '折れ線グラフ .AxisGroup = 2 '第2軸 .Border.Color = Cells(2, i + 1).Interior.Color '折れ線グラフの色 End Select End With Next '主軸の書式設定 .Axes(xlValue).TickLabels.NumberFormatLocal = "#,###," '表示形式 '第2軸の書式設定 .Axes(xlValue, xlSecondary).MinimumScale = 0.8 '最小値 .Axes(xlValue, xlSecondary).MaximumScale = 1.2 '最大値 .Axes(xlValue, xlSecondary).MajorUnit = 0.05 '目盛間隔 .Axes(xlValue, xlSecondary).TickLabels.NumberFormatLocal = "0%" '表示形式 End With End Sub |
Sub sample2()
Dim i As Long '系列のFor~Nextで使用 Dim rowMin As Long 'グラフデータ範囲の開始行 Dim rowMax As Long 'グラフデータ範囲の最終行 Dim strFormula As String 'グラフデータ範囲の設定文字列 Dim strExternal() As String 'グラフのSERIES関数の引数毎に分割した文字列 Dim strAddress() As String 'ADDRESS文字列をシートとRANGE指定に分割した文字列 Dim newAddress1 As String 'SERIES関数の新しい系列名のADDRESS Dim newAddress2 As String 'SERIES関数の新しい系列値のADDRESS 'Chartオブジェクトに対する処理 With ActiveSheet.ChartObjects(1).Chart '全系列に対する処理 For i = 1 To .SeriesCollection.Count 'グラフデータ範囲の設定文字列 strFormula = .SeriesCollection(i).Formula '=SERIES(引数・・・)を引数だけにする strFormula = Replace(Replace(strFormula, "=SERIES(", ""), ")", "") 'SERIES関数の引数毎に分割 strExternal = Split(strFormula, ",") '系列名(SERIES関数の第2引数)の処理 'ADDRESS文字列をシートとRANGE指定に分割、ADDRESSにブック名が入っている場合は消去 strAddress = Split(Replace(strExternal(1), "[" & ThisWorkbook.Name & "]", ""), "!") '系列名の開始行 rowMin = Worksheets(strAddress(0)).Range(strAddress(1)).Item(1).Row '系列名の最終行 rowMax = Worksheets(strAddress(0).Cells(Rows.Count, Worksheets(strAddress(0)).Range(strAddress(1)).Item(1).Column).End(xlUp).Row 'SERIES関数の新しい系列名のADDRESS newAddress1 = Worksheets(strAddress(0)).Range(strAddress(1)).Resize(rowMax - rowMin + 1, 1).Address(External:=True) '系列値(SERIES関数の第3引数)の処理 'ADDRESS文字列をシートとRANGE指定に分割、ADDRESSにブック名が入っている場合は消去 strAddress = Split(Replace(strExternal(2), "[" & ThisWorkbook.Name & "]", ""), "!") 'SERIES関数の新しい系列値のADDRESS newAddress2 = Worksheets(strAddress(0)).Range(strAddress(1)).Resize(rowMax - rowMin + 1, 1).Address(External:=True) 'グラフデータ範囲の再設定 .SeriesCollection(i).Formula = "=SERIES(" & _ strExternal(0) & "," & _ newAddress1 & "," & _ newAddress2 & "," & _ i & ")" Next i End With End Sub |
Sub sample1()
With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://info.finance.yahoo.co.jp/ranking/?kd=1&tm=d&vl=a&mk=1&p=1", _ Destination:=Range("$A$1")) .Name = "?kd=1&tm=d&vl=a&mk=1&p=1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub |
Sub sample2()
Dim i As Long Dim j As Long Dim objITEM As Object Dim objIE As New InternetExplorer objIE.Visible = False objIE.Navigate "http://info.finance.yahoo.co.jp/ranking/?kd=1&tm=d&vl=a&mk=1&p=1" Call untilReady(objIE) Range("A1").CurrentRegion.Offset(1, 0).ClearContents i = 2 j = 1 For Each objITEM In objIE.Document.getElementsByTagName("td") Cells(i, j) = objITEM.innerText j = j + 1 If j > 10 Then j = 1 i = i + 1 End If Next objIE.Quit Set objITEM = Nothing Set objIE = Nothing End Sub Sub untilReady(objIE As Object, Optional ByVal WaitTime As Integer = 10) Dim starttime As Date starttime = Now() Do While objIE.Busy = True Or objIE.ReadyState <> READYSTATE_COMPLETE DoEvents If Now() > DateAdd("S", WaitTime, starttime) Then Exit Do End If Loop DoEvents End Sub |
Sub sample3()
Dim i As Long Dim j As Long Dim objXML As New MSHTML.HTMLDocument Dim htmlDoc As New MSHTML.HTMLDocument Dim objITEM As Object Set htmlDoc = objXML.createDocumentFromUrl("https://excel-ubara.com/EXCEL/EXCEL910.html", vbNullString) Call untilReady(htmlDoc) Range("A1").CurrentRegion.Offset(1, 0).ClearContents i = 2 j = 1 For Each objITEM In htmlDoc.getElementsByTagName("td") Cells(i, j) = objITEM.innerText j = j + 1 If j > 3 Then j = 1 i = i + 1 End If Next Set objITEM = Nothing Set htmlDoc = Nothing Set objXML = Nothing End Sub Sub untilReady(htmlDoc As MSHTML.HTMLDocument, Optional ByVal WaitTime As Integer = 10) Dim starttime As Date starttime = Now() Do Until htmlDoc.ReadyState = "complete" DoEvents If Now() > DateAdd("S", WaitTime, starttime) Then Exit Do End If Loop DoEvents End Sub |
Sub sample4()
Dim i As Long Dim j As Long Dim objXML As New MSXML2.XMLHTTP Dim htmlDoc As Object Set htmlDoc = New MSHTML.HTMLDocument Dim objITEM As Object With objXML .Open "GET", "https://excel-ubara.com/EXCEL/EXCEL910.html", False .send (Null) htmlDoc.write .responseText End With Range("A1").CurrentRegion.Offset(1, 0).ClearContents i = 2 j = 1 For Each objITEM In htmlDoc.getElementsByTagName("td") Cells(i, j) = objITEM.innerText j = j + 1 If j > 3 Then j = 1 i = i + 1 End If Next Set objITEM = Nothing Set htmlDoc = Nothing Set objXML = Nothing End Sub |
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim cmdBra1 As CommandBarControl Dim cmdBra2 As CommandBarControl '標準状態にリセット Application.CommandBars("Cell").Reset '全てのメニューを一旦削除 For Each cmdBra1 In Application.CommandBars("Cell").Controls cmdBra1.Visible = False Next '以下で、新規のメニューを追加 Set cmdBra1 = Application.CommandBars("Cell").Controls.Add() With cmdBra1 .Caption = "AA" .FaceId = 2 .OnAction = "AA" End With Set cmdBra1 = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup) With cmdBra1 .Caption = "BB" End With Set cmdBra2 = cmdBra1.Controls.Add() With cmdBra2 .Caption = "BB1" .FaceId = 3 .OnAction = "BB1" End With Set cmdBra2 = cmdBra1.Controls.Add() With cmdBra2 .Caption = "BB2" .FaceId = 4 .OnAction = "BB2" End With Application.CommandBars("Cell").ShowPopup Application.CommandBars("Cell").Reset Cancel = True End Sub |
Sub AA()
ActiveCell.Value = "AA" End Sub Sub BB1() ActiveCell.Value = "BB1" End Sub Sub BB2() ActiveCell.Value = "BB2" End Sub |
名前 | 説明 |
Type | 指定したコマンド バーに追加するコントロールの種類を指定します。使用できる定数は、MsoControl クラスの msoControlButton、msoControlEdit、msoControlDropdown、msoControlComboBox、 msoControlPopup のいずれかです。 |
Id | 組み込みのコントロールを表す整数を指定します。この引数を 1 に設定するか省略すると、指定した種類の空白のカスタム コントロールがコマンド バーに追加されます。 |
Parameter | 組み込みのコントロールの場合、この引数はコンテナー アプリケーションでコマンドを実行するときに使用されます。カスタム コントロールの場合、この引数を使用して、Visual Basic のプロシージャに情報を渡したり、Tag プロパティの 2 番目の値のようなコントロールの情報を格納することができます。 |
Before | コマンド バーにおける新しいコントロールの位置を表す数字を指定します。新しいコントロールは、指定した位置にあるコントロールの直前に挿入されます。この引数を省略すると、コントロールは指定したコマンド バーの末尾に追加されます。 |
Temporary | True を指定すると、新しいコントロールが一時的なものになります。このコントロールは、コンテナー アプリケーションの終了と同時に自動的に削除されます。この引数を省略すると、既定値の False になります。 |
Sub sample()
Const startNo As Integer = 1 '開始番号を指定 Const stopNo As Integer = 50 '終了番号を指定 Dim cmdBar As CommandBar Dim cmdBarCtl As CommandBarControl Dim i As Integer On Error Resume Next Set cmdBar = Application.CommandBars("FaceId") If cmdBar Is Nothing Then Set cmdBar = Application.CommandBars.Add(Name:="FaceId", Temporary:=True) For i = startNo To stopNo Set cmdBarCtl = cmdBar.Controls.Add(Type:=msoControlButton) With cmdBarCtl .FaceID = i .TooltipText = i End With Next cmdBar.Visible = True Else For i = startNo To stopNo With cmdBar.Controls(i - startNo + 1) .FaceID = i .TooltipText = i End With Next End If Set cmdBarCtl = Nothing Set cmdBar = Nothing End Sub |
Sub チャート作成()
Dim ws As Worksheet '対象シート Dim MyRange As Range 'グラフ範囲 Dim chartObj As ChartObject 'Chartオブジェクトのコンテナ Set ws = ActiveSheet Set MyRange = ws.Range(" A1 ").CurrentRegion 'グラフ範囲 'Chartを追加、グラフ範囲の右隣に、グラフ範囲の3倍の大きさで作成 Set chartObj = ws.ChartObjects.Add(MyRange.Width + MyRange.Left, MyRange.Top, MyRange.Width * 3, MyRange.Height * 1) '追加されたChartオブジェクトに対する処理 With chartObj.Chart '元データ範囲の設定 .SetSourceData MyRange '横棒グラフ .ChartType = xlBarClustered '系列に対する処理 With .SeriesCollection(1) .Interior.Color = vbBlue 'グラフの色 End With With .SeriesCollection(2) .AxisGroup = 2 '第2軸 .Interior.Color = vbRed 'グラフの色 End With '主軸の書式設定 With .Axes(xlValue) .TickLabels.NumberFormatLocal = "#,###;" '表示形式 .MinimumScale = -60 0 '最小値 .MaximumScale = 500 '最大値 .MajorUnit = 100 '目盛間隔 .ReversePlotOrder = True '反転する End With '主軸の幅 With .ChartGroups(1) .Overlap = 90 .GapWidth = 10 End With '第2軸の書式設定 With .Axes(xlValue, xlSecondary) .TickLabels.NumberFormatLocal = "#,###;" '表示形式 .MinimumScale = -600 '最小値 .MaximumScale = 500 '最大値 .MajorUnit = 100 '目盛間隔 .ReversePlotOrder = False '反転しない End With '第2軸の幅 With .ChartGroups(2) .Overlap = 90 .GapWidth = 10 End With '縦軸の書式設定 .Axes(xlCategory).TickLabels.Offset = 0 .Axes(xlCategory).MajorTickMark = xlNone '凡例 With .Legend .Left = 0 .Width = 75 .Height = 25 .Left = (chartObj.Chart.ChartArea.Width - .Width) / 2 .Top = chartObj.Chart.ChartArea.Height - .Height End With 'プロットエリア With .PlotArea .Left = 0 '左には自動で最小の余白が付く .Width = chartObj.Width '左右に自動で最小の余白が付く End With End With End Sub |
Sub sample1()
Dim i As Long Dim j As Long Dim FileName As Variant Dim dblScal As Double Dim sp As Shape FileName = Application.GetOpenFilename( _ FileFilter:="画像ファイル,*.bmp;*.jpg;*.gif", _ MultiSelect:=True) If Not IsArray(FileName) Then Exit Sub End If For Each sp In ActiveSheet.Shapes If sp.TopLeftCell.Column = 1 Then sp.Delete End If Next j = 1 For i = LBound(FileName) To UBound(FileName) Cells(j, 1).Select With ActiveSheet.Pictures.Insert(FileName(i)) 'サイズ調整、セル内に収める If Cells(j, 1).Width / .Width < Cells(j, 1).Height / .Height Then dblScal = WorksheetFunction.RoundDown(Cells(j, 1).Width / .Width, 2) Else dblScal = WorksheetFunction.RoundDown(Cells(j, 1).Height / .Height, 2) End If .Width = .Width * dblScal '縦横比を維持して縮小される End With j = j + 1 Next i End Sub |
Sub sample2()
Dim i As Long Dim j As Long Dim FileName As Variant Dim dblScal As Double Dim sp As Shape FileName = Application.GetOpenFilename( _ FileFilter:="画像ファイル,*.bmp;*.jpg;*.gif", _ MultiSelect:=True) If Not IsArray(FileName) Then Exit Sub End If For Each sp In ActiveSheet.Shapes If sp.TopLeftCell.Column = 1 Then sp.Delete End If Next j = 1 For i = LBound(FileName) To UBound(FileName) Cells(j, 1).Select With ActiveSheet.Shapes.AddPicture( _ FileName:=FileName(i), _ LinkToFile:=False, _ SaveWithDocument:=True , _ Left:=Selection.Left, _ Top:=Selection.Top, _ Width:= 0 , _ Height:= 0 ) '一旦、元のサイズに戻す .ScaleHeight 1, msoTrue .ScaleWidth 1, msoTrue 'サイズ調整、セル内に収める If Cells(j, 1).Width / .Width < Cells(j, 1).Height / .Height Then dblScal = WorksheetFunction.RoundDown(Cells(j, 1).Width / .Width, 2) Else dblScal = WorksheetFunction.RoundDown(Cells(j, 1).Height / .Height, 2) End If .Width = .Width * dblScal .Height = .Height * dblScal End With j = j + 1 Next i End Sub |
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
' 64bit 版のExcelの場合は、以下のようにPtrSafeを付けて下さい。 'Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sub untilReady(objIE As Object) Dim starttime As Date starttime = Now() Do While objIE.Busy = True Or objIE.ReadyState <> READYSTATE_COMPLETE Sleep 100 DoEvents If Now() > DateAdd("S", 10, starttime) Then Exit Do End If Loop End Sub |
Sub ソート検証()
Dim ws1 As Worksheet Dim ws2 As Worksheet Dim myAry1() Dim myAry2() Dim i As Long Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") myAry1 = ws1.Range("A1:A10000").Value 'myAry1は2次元配列なのでmyAry2で1次元配列に変更 ReDim myAry2(LBound(myAry1) To UBound(myAry1)) For i = LBound(myAry1) To UBound(myAry1) myAry2(i) = myAry1(i, 1) Next '1次元配列ソート Dim start As Double: start = Timer ' Call バブルソート(myAry2) ' Call 挿入ソート(myAry2) ' Call クイックソート(myAry2, LBound(myAry2), UBound(myAry2)) Debug.Print Timer - start 'myAry2は1次元配列なのでmyAry1で2次元配列に変更 For i = LBound(myAry2) To UBound(myAry2) myAry1(i, 1) = myAry2(i) Next ws2.Cells.Clear ws2.Range("A1:A10000").Value = myAry1 End Sub |
Sub バブルソート(ByRef argAry() As Variant)
Dim vSwap As Variant Dim i As Integer Dim j As Integer For i = UBound(argAry) To LBound(argAry) Step -1 For j = LBound(argAry) To i - 1 If argAry(j) > argAry(j + 1) Then vSwap = argAry(j) argAry(j) = argAry(j + 1) argAry(j + 1) = vSwap End If Next j Next i End Sub |
Sub 挿入ソート(ByRef argAry() As Variant)
Dim Low As Long Dim Upp As Long Low = LBound(argAry) Upp = UBound(argAry) Dim i As Long, j As Long Dim vSwap As Variant For i = Low + 1 To Upp vSwap = argAry(i) For j = i - 1 To Low Step -1 If argAry(j) > vSwap Then argAry(j + 1) = argAry(j) Else Exit For End If Next argAry(j + 1) = vSwap Next End Sub |
Sub クイックソート(ByRef argAry() As Variant, _
ByVal lngMin As Long, _ ByVal lngMax As Long) Dim i As Long Dim j As Long Dim vBase As Variant Dim vSwap As Variant vBase = argAry(Int((lngMin + lngMax) / 2)) i = lngMin j = lngMax Do Do While argAry(i) < vBase i = i + 1 Loop Do While argAry(j) > vBase j = j - 1 Loop If i >= j Then Exit Do vSwap = argAry(i) argAry(i) = argAry(j) argAry(j) = vSwap i = i + 1 j = j - 1 Loop If (lngMin < i - 1) Then Call クイックソート(argAry, lngMin, i - 1) End If If (lngMax > j + 1) Then Call クイックソート(argAry, j + 1, lngMax) End If End Sub |
Sub ソート検証()
Dim ws1 As Worksheet Dim ws2 As Worksheet Dim myAry1() Dim i As Long Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") myAry1 = ws1.Range("A1:B10000").Value '2次元配列ソート Dim start As Double: start = Timer 'Call バブルソート(myAry1, 1) 'Call クイックソート(myAry1, LBound(myAry1), UBound(myAry1), 1) Debug.Print Timer - start ws2.Cells.Clear ws2.Range("A1:B10000").Value = myAry1 End Sub |
Sub バブルソート(ByRef argAry() As Variant, ByVal keyPos As Long)
Dim vSwap Dim i As Integer Dim j As Integer Dim k As Integer For i = LBound(argAry, 1) To UBound(argAry, 1) For j = LBound(argAry) To UBound(argAry) - 1 If argAry(j, keyPos) > argAry(j + 1, keyPos) Then For k = LBound(argAry, 2) To UBound(argAry, 2) vSwap = argAry(j, k) argAry(j, k) = argAry(j + 1, k) argAry(j + 1, k) = vSwap Next End If Next j Next i End Sub |
Sub クイックソート(ByRef argAry() As Variant, _
ByVal lngMin As Long, _ ByVal lngMax As Long, _ ByVal keyPos As Long) Dim i As Long Dim j As Long Dim k As Long Dim vBase As Variant Dim vSwap As Variant vBase = argAry(Int((lngMin + lngMax) / 2), keyPos) i = lngMin j = lngMax Do Do While argAry(i, keyPos) < vBase i = i + 1 Loop Do While argAry(j, keyPos) > vBase j = j - 1 Loop If i >= j Then Exit Do For k = LBound(argAry, 2) To UBound(argAry, 2) vSwap = argAry(i, k) argAry(i, k) = argAry(j, k) argAry(j, k) = vSwap Next i = i + 1 j = j - 1 Loop If (lngMin < i - 1) Then Call クイックソート(argAry, lngMin, i - 1, keyPos) End If If (lngMax > j + 1) Then Call クイックソート(argAry, j + 1, lngMax, keyPos) End If End Sub |
Sub SheetSort(ByRef argAry() As Variant, ByVal keyPos As Long)
Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim xlApp As New Excel.Application Set wb = xlApp.Workbooks.Add Set ws = wb.ActiveSheet With ws Set rng = .Range(.Cells(LBound(argAry, 1), LBound(argAry, 2)), .Cells(UBound(argAry, 1), UBound(argAry, 2))) rng.Value = argAry rng.Sort Key1:=.Cells(1, keyPos), Order1:=xlAscending, Header:=xlNo argAry = rng.Value End With wb.Close SaveChanges:=False Set xlApp = Nothing End Sub |
Sub test3()
Dim ws1 As Worksheet Dim ws2 As Worksheet Dim myAry1() Dim i As Long Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") myAry1 = ws1.Range("A1:B10000").Value 'シートでソート Dim start As Double: start = Timer Call SheetSort(myAry1, 2) Debug.Print Timer - start ws2.Cells.Clear ws2.Range("A1:C10000") = myAry1 End Sub |
Option Explicit
Private tryCnt As Long Sub main() Debug.Print Timer Dim SuAry(1 To 9, 1 To 9) As Integer Dim i1 As Integer Dim i2 As Integer tryCnt = 0 Erase SuAry For i1 = 1 To 9 For i2 = 1 To 9 If Cells(i1, i2) = "" Then Cells(i1, i2).Font.Color = vbBlue Else SuAry(i1, i2) = Cells(i1, i2) End If Next Next Call trySu(SuAry) Range("A1:I9").Value = SuAry Debug.Print Timer If getBlank(SuAry(), i1, i2) = False Then MsgBox "解読成功" & vbLf & tryCnt Else MsgBox "あれれ・・・" End If End Sub Function trySu(ByRef SuAry() As Integer) As Boolean Dim i1 As Integer Dim i2 As Integer Dim su As Integer If getBlank(SuAry(), i1, i2) = False Then trySu = True Exit Function End If For su = 1 To 9 If chkSu(SuAry(), i1, i2, su) = True Then SuAry(i1, i2) = su tryCnt = tryCnt + 1 Cells(i1, i2) = su If trySu(SuAry) = True Then trySu = True Exit Function End If End If Next SuAry(i1, i2) = 0 Cells(i1, i2) = "" DoEvents trySu = False End Function Function getBlank(ByRef SuAry() As Integer, ByRef i1 As Integer, ByRef i2 As Integer) As Boolean For i1 = 1 To 9 For i2 = 1 To 9 If SuAry(i1, i2) = 0 Then getBlank = True Exit Function End If Next Next getBlank = False End Function Function chkSu(ByRef SuAry() As Integer, ByVal i1 As Integer, ByVal i2 As Integer, ByVal su As Integer) As Boolean Dim ix1 As Integer Dim ix2 As Integer Dim i1S As Integer Dim i2S As Integer chkSu = False '横をチェック For ix2 = 1 To 9 If ix2 <> i2 Then If SuAry(i1, ix2) = su Then chkSu = False Exit Function End If End If Next '縦をチェック For ix1 = 1 To 9 If ix1 <> i1 Then If SuAry(ix1, i2) = su Then chkSu = False Exit Function End If End If Next '枠内をチェック i1S = (Int((i1 + 2) / 3) - 1) * 3 + 1 i2S = (Int((i2 + 2) / 3) - 1) * 3 + 1 For ix1 = i1S To i1S + 2 For ix2 = i2S To i2S + 2 If ix1 <> i1 Or ix2 <> i2 Then If SuAry(ix1, ix2) = su Then chkSu = False Exit Function End If End If Next Next chkSu = True End Function |
Sub sample()
Dim i As Long Dim j As Long Dim ws As Worksheet Dim FileName As Variant Dim dblScal As Double FileName = Application.GetOpenFilename( _ FileFilter:="画像ファイル,*.bmp;*.jpg;*.gif", _ MultiSelect:=True) If Not IsArray(FileName) Then Exit Sub End If Application.ScreenUpdating = False Set ws = Activesheet j = 1 For i = LBound(FileName) To UBound(FileName) 'ファイル名にハイパーリンク ws.Hyperlinks.Add Anchor:=ws.Cells(j, 1), _ Address:=FileName(i), _ TextToDisplay:=FileName(i) '画像の取り込み With ws.Shapes.AddPicture( _ FileName:=FileName(i), _ LinkToFile:=False, _ SaveWithDocument:=True, _ Left:=Selection.Left, _ Top:=Selection.Top, _ Width:=0, _ Height:=0) '一旦、元のサイズに戻す .ScaleHeight 1, msoTrue .ScaleWidth 1, msoTrue 'サイズ調整、セル内に収める If ws.Cells(j, 2).Width / .Width < ws.Cells(j, 2).Height / .Height Then dblScal = WorksheetFunction.RoundDown(ws.Cells(j, 2).Width / .Width, 2) Else dblScal = WorksheetFunction.RoundDown(ws.Cells(j, 2).Height / .Height, 2) End If .Width = .Width * dblScal .Height = .Height * dblScal .Cut End With ' サムネイルの作成 ws. PasteSpecia l Format:="図 (JPEG)", Link:=False, DisplayAsIcon:=False With ws.Shapes(ws.Shapes.Count) .Top = ws.Cells(j, 2).Top .Left = ws.Cells(j, 2).Left ws.Hyperlinks.Add Anchor:=ws.Shapes(ws.Shapes.Count), Address:=FileName(i) End With j = j + 1 Next ws.Select ws.Range("A1").Activate Application.CutCopyMode = False Application.ScreenUpdating = True End Sub |
Sub sample()
Dim i As Long Dim aryDir() As String Dim aryFile() As String Dim strName As String i = 0 ReDim aryDir(i) aryDir(i) = ThisWorkbook.Path 'フォルダをここで指定 'まずは、指定フォルダ以下の全サブフォルダを取得し、配列aryDirに入れます。 Do strName = Dir(aryDir(i) & "\", vbDirectory) Do While strName <> "" If GetAttr(aryDir(i) & "\" & strName) And vbDirectory Then If strName <> "." And strName <> ".." Then ReDim Preserve aryDir(UBound(aryDir) + 1) aryDir(UBound(aryDir)) = aryDir(i) & "\" & strName End If End If strName = Dir() Loop i = i + 1 If i > UBound(aryDir) Then Exit Do End If Loop '配列aryDirの全フォルダについて、ファイルを取得し、配列aryFileに入れます。 ReDim aryFile(0) For i = 0 To UBound(aryDir) strName = Dir(aryDir(i) & "\", vbNormal + vbHidden + vbReadOnly + vbSystem) Do While strName <> "" If aryFile(0) <> "" Then ReDim Preserve aryFile(UBound(aryFile) + 1) End If aryFile(UBound(aryFile)) = aryDir(i) & "\" & strName '実行結果が分かりやすいように、テスト的にセルに書き出す場合 'Cells(UBound(aryFile) + 1, 1) = aryFile(UBound(aryFile)) strName = Dir() Loop Next End Sub |
Private Sub Workbook_Open()
Application.Goto Sheets(1).Range("A1"), True End Sub |
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.Saved = False Then ActiveWorkbook.Save End If End Sub |
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.Saved = False Then Cancel = True End If End Sub |
Private Sub
Worksheet_Activate()
Application.Goto Range("A1"), True End Sub |
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target .Columns.AutoFit .Rows.AutoFit End With Cancel = True End Sub |
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range Application.EnableEvents = False For Each myRng In Target If Not Intersect(myRng, Columns("A")) Is Nothing Then myRng.Value = UCase(myRng.Value) End If Next Application.EnableEvents = True End Sub |
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRng As Range Set myRng = Range("A2:D100") myRng.Interior.ColorIndex = xlNone If Not Intersect(myRng, Target(1)) Is Nothing Then Cells(Target(1).Row, myRng(1).Column).Resize(, 5).Interior.Color = RGB(150, 200, 255) End If End Sub |
Sub sample()
Dim i As Long Dim arySht() As String For i = 1 To Sheets.Count If i = 1 Then ReDim arySht(0) Else ReDim Preserve arySht(UBound(arySht) + 1) End If arySht(UBound(arySht)) = Sheets(i).Name Next Call SheetSort(arySht) For i = UBound(arySht) To LBound(arySht) Step -1 Sheets(arySht(i)).Move Before:=Sheets(1) Next End Sub Sub SheetSort(ByRef argAry() As String) Dim sSwap As String Dim i As Integer Dim j As Integer For i = LBound(argAry) To UBound(argAry) For j = UBound(argAry) To i Step -1 If argAry(i) > argAry(j) Then sSwap = argAry(i) argAry(i) = argAry(j) argAry(j) = sSwap End If Next j Next i End Sub |
Sub sample()
Dim TitleHas As Boolean Dim TitleFormula As String Dim 基準値 As Double Dim vTemp, v1, v2 Dim i As Long 'シートの先頭グラフ Dim obj As Chart Set obj = ActiveSheet.ChartObjects(1).Chart With obj '基準値決定:平均値を求める For i = 1 To .SeriesCollection.Count 'シート名に,カンマは含んでいない前提です vTemp = Split(.SeriesCollection(i).Formula, ",") vTemp = vTemp(UBound(vTemp) - 1) v1 = v1 + WorksheetFunction.Sum(Range(vTemp)) v2 = v2 + WorksheetFunction.Count(Range(vTemp)) Next If v2 <> 0 Then 基準値 = v1 / v2 '系列追加でタイトルが消える事への対応 TitleHas = .HasTitle If TitleHas Then TitleFormula = .ChartTitle.Formula '基準線を新規系列として追加 With .SeriesCollection.NewSeries .Name = "=""規準""" '特に必要はない '.Values = "={基準値,基準値}" .Values = "={" & Join(Array(基準値, 基準値), ",") & "}" .ChartType = xlLine '折れ線 .AxisGroup = 2 '第2軸 .Format.Line.ForeColor.RGB = vbRed 'RGB(255, 0, 0) End With '系列追加で消えたタイトルの復元 .HasTitle = TitleHas If TitleHas Then .ChartTitle.Formula = TitleFormula '第2縦軸のScaleを第1縦軸に合わせる .Axes(xlValue, 2).MinimumScale = .Axes(xlValue, 1).MinimumScale .Axes(xlValue, 2).MaximumScale = .Axes(xlValue, 1).MaximumScale .Axes(xlValue, 2).Delete '第2軸を消す 'グラフ内左右余白を消す:基準線を第2横軸に設定 .HasAxis(xlCategory, 2) = True '第2横軸表示 .Axes(xlCategory, 2).AxisBetweenCategories = False '軸位置を目盛 .Axes(xlCategory, 2).TickLabelPosition = xlNone '目盛ラベルなし '基準線の凡例を消す On Error Resume Next '凡例が無いときの対応 .Legend.LegendEntries(.SeriesCollection.Count).Delete End With End Sub |
Sub sample1()
Dim i As Long Dim strDir As String Dim strFile As String Debug.Print Timer Application.ScreenUpdating = False strDir = ThisWorkbook.Path & "\test\" strFile = Dir(strDir) i = 1 Do While strFile <> "" Cells(i, 1) = ExecuteExcel4Macro("'" & strDir & "[" & strFile & "]Sheet1'!R1C1") strFile = Dir() i = i + 1 Loop Application.ScreenUpdating = True Debug.Print Timer End Sub |
Sub sample2()
Dim i As Long Dim strDir As String Dim strFile As String Dim xls As New Excel.Application Dim wb As Workbook Debug.Print Timer Application.ScreenUpdating = False strDir = ThisWorkbook.Path & "\test\" strFile = Dir(strDir) i = 1 Do While strFile <> "" Set wb = xls.Workbooks.Open(strDir & strFile) Cells(i, 1) = wb.Worksheets("Sheet1").Range("A1") wb.Close Savechanges:=False strFile = Dir() i = i + 1 Loop Set xls = Nothing Application.ScreenUpdating = True Debug.Print Timer End Sub |
Sub sample1_2()
Dim i As Long Dim j As Long Dim strDir As String Dim strFile As String Debug.Print Timer Application.ScreenUpdating = False strDir = ThisWorkbook.Path & "\test\" strFile = Dir(strDir) i = 1 Do While strFile <> "" For j = 1 To 100 Cells(i, j) = ExecuteExcel4Macro("'" & strDir & "[" & strFile & "]Sheet1'!R1C" & j) Next strFile = Dir() i = i + 1 Loop Application.ScreenUpdating = True Debug.Print Timer End Sub |
Sub sample2_2()
Dim i As Long Dim j As Long Dim strDir As String Dim strFile As String Dim xls As New Excel.Application Dim wb As Workbook Debug.Print Timer Application.ScreenUpdating = False strDir = ThisWorkbook.Path & "\test\" strFile = Dir(strDir) i = 1 Do While strFile <> "" Set wb = xls.Workbooks.Open(strDir & strFile) For j = 1 To 100 Cells(i, 1) = wb.Worksheets("Sheet1").Cells(1, j) Next wb.Close Savechanges:=False strFile = Dir() i = i + 1 Loop Set xls = Nothing Application.ScreenUpdating = True Debug.Print Timer End Sub |
Sub sample2_3()
Dim i As Long Dim strDir As String Dim strFile As String Dim xls As New Excel.Application Dim wb As Workbook Dim ws As Worksheet Debug.Print Timer Application.ScreenUpdating = False strDir = ThisWorkbook.Path & "\test\" strFile = Dir(strDir) i = 1 Do While strFile <> "" Set wb = xls.Workbooks.Open(strDir & strFile) Set ws = wb.Worksheets("Sheet1") Range(Cells(i, 1), Cells(i, 100)).Value = ws.Range(ws.Cells(1, 1), ws.Cells(1, 100)).Value wb.Close Savechanges:=False strFile = Dir() i = i + 1 Loop Set xls = Nothing Application.ScreenUpdating = True Debug.Print Timer End Sub |
Sub 素数を求める()
Dim i As Long Dim j As Long Dim m As Long Dim p As Long i = 1 j = 1 p = 2 Do If 素数判定(p) Then Cells(i, j) = p i = i + 1 If i > Rows.Count Then i = 1 j = j + 1 End If End If p = p + 1 Loop End Sub Function 素数判定(ByVal p As Long) As Boolean 素数判定 = False Dim m As Long If p = 2 Then Exit Function For m = 2 To Int(Sqr(p)) If p Mod m = 0 Then Exit Function End If Next 素数判定 = True End Function |
Sub sample()
Dim rng As Range For Each rng In Selection If isValidationOk(rng) = False Then MsgBox rng.Address & ":入力値不正" End If Next End Sub Function isValidationOk(ByRef rng As Range) As Boolean Dim i As Integer Dim strList As String Dim strSplit() As String On Error Resume Next With rng '入力規則のリストかどうか If .Validation.Type = xlValidateList Then '入力規則がない場合はエラーが発生するので If Err.Number > 0 Then Err.Clear isValidationOk = True Exit Function End If Else '入力規則のリスト以外はチェックしない isValidationOk = True Exit Function End If '入力規則のリストのデータを配列に取得 If Left(.Validation.Formula1, 1) = "=" Then 'リストがセル範囲指定の場合 With Range(Mid(.Validation.Formula1, 2)) ReDim strSplit(.Count - 1) For i = 1 To .Count strSplit(i - 1) = .Item(i).Value Next End With Else 'リストデータが直接指定の場合 strSplit = Split(.Validation.Formula1, ",") End If '入力規則のリスト配列にあるかの判定 For i = LBound(strSplit) To UBound(strSplit) If strSplit(i) = .Value Then isValidationOk = True Exit Function End If Next '入力規則のリスト配列にないのでエラー isValidationOk = False Exit Function End With End Function |
Sub sample()
Dim ws As Worksheet Dim rng As Range Dim gc As ChartObject Dim sc As Series Set ws = ActiveSheet 'セルの塗りつぶしを再設定 For Each rng In ws.UsedRange If rng.Interior.ColorIndex <> xlColorIndexNone Then rng.Interior.Color = rng.Interior.Color End If Next 'グラフの色を再設定 For Each gc In ws.ChartObjects 'グラフのコレクション For Each sc In gc.Chart.SeriesCollection '系列のコレクション With sc Select Case sc.ChartType Case XlChartType.xlColumnClustered '棒グラフ If .Interior.ColorIndex <> xlColorIndexNone Then .Interior.Color = .Interior.Color End If If .Border.ColorIndex <> xlColorIndexNone And _ .Border.ColorIndex <> xlColorIndexAutomatic Then .Border.Color = .Border.Color End If Case XlChartType.xlLine '折れ線グラフ If .Interior.ColorIndex <> xlColorIndexNone Then .Interior.Color = .Interior.Color End If If .Border.ColorIndex <> xlColorIndexNone Then .Border.Color = .Border.Color End If Case XlChartType.xlLineMarkers 'マーカー付き折れ線グラフ If .Interior.ColorIndex <> xlColorIndexNone Then .Interior.Color = .Interior.Color End If If .Border.ColorIndex <> xlColorIndexNone Then .Border.Color = .Border.Color End If If .MarkerForegroundColor >= 0 Then If .MarkerForegroundColorIndex <> xlColorIndexNone Then .MarkerForegroundColor = .MarkerForegroundColor End If Else .MarkerForegroundColor = .Border.Color End If If .MarkerBackgroundColor >= 0 Then If .MarkerBackgroundColorIndex <> xlColorIndexNone Then .MarkerBackgroundColor = .MarkerBackgroundColor End If Else .MarkerBackgroundColor = .Border.Color End If End Select End With Next Next End Sub |
Sub sample1()
Dim i As Long, F As String, lngMax As Long lngMax = Cells(Rows.Count, 1).End(xlUp).Row With ActiveSheet.ChartObjects(1).Chart For i = 1 To .SeriesCollection.Count .SeriesCollection(i).Formula = _ "=SERIES(" & _ Cells(1, i + 1).Address(External:=True) & "," & _ Cells(2, 1).Resize(lngMax - 1, 1).Address(External:=True) & "," & _ Cells(2, i + 1).Resize(lngMax - 1, 1).Address(External:=True) & "," & _ i & ")" Next i End With End Sub |
Sub sample2()
Dim i As Long '系列のFor~Nextで使用 Dim rowMin As Long 'グラフデータ範囲の開始行 Dim rowMax As Long 'グラフデータ範囲の最終行 Dim strFormula As String 'グラフデータ範囲の設定文字列 Dim strExternal() As String 'グラフのSERIES関数の引数毎に分割した文字列 Dim strAddress() As String 'ADDRESS文字列をシートとRANGE指定に分割した文字列 Dim newAddress1 As String 'SERIES関数の新しい系列名のADDRESS Dim newAddress2 As String 'SERIES関数の新しい系列値のADDRESS Dim curRng As Range 'グラフデータのセル範囲 'Chartオブジェクトに対する処理 With ActiveSheet.ChartObjects(1).Chart '全系列に対する処理 For i = 1 To .SeriesCollection.Count 'グラフデータ範囲の設定文字列 strFormula = .SeriesCollection(i).Formula '=SERIES(引数・・・)を引数だけにする strFormula = Replace(Replace(strFormula, "=SERIES(", ""), ")", "") 'SERIES関数の引数毎に分割 strExternal = Split(strFormula, ",") '系列名(SERIES関数の第2引数)の処理 'ADDRESS文字列をシートとRANGE指定に分割、ADDRESSにブック名が入っている場合は消去 strAddress = Split(Replace(strExternal(1), "[" & ThisWorkbook.Name & "]", ""), "!") strAddress(0) = Replace(strAddress(0), "'", "") 'シートの前後の'を削除 '系列名の開始行 rowMin = Worksheets(strAddress(0)).Range(strAddress(1)).Item(1).Row '系列名の最終行 Set curRng = Worksheets(strAddress(0)).Range(strAddress(1)).CurrentRegion rowMax = curRng.Item(curRng.Count).Row 'SERIES関数の新しい系列名のADDRESS newAddress1 = Worksheets(strAddress(0)).Range(strAddress(1)).Resize(rowMax - rowMin + 1, 1).Address(External:=True) '系列値(SERIES関数の第3引数)の処理 'ADDRESS文字列をシートとRANGE指定に分割、ADDRESSにブック名が入っている場合は消去 strAddress = Split(Replace(strExternal(2), "[" & ThisWorkbook.Name & "]", ""), "!") strAddress(0) = Replace(strAddress(0), "'", "") 'シートの前後の'を削除 'SERIES関数の新しい系列値のADDRESS newAddress2 = Worksheets(strAddress(0)).Range(strAddress(1)).Resize(rowMax - rowMin + 1, 1).Address(External:=True) 'グラフデータ範囲の再設定 .SeriesCollection(i).Formula = "=SERIES(" & _ strExternal(0) & "," & _ newAddress1 & "," & _ newAddress2 & "," & _ i & ")" Next i End With End Sub |
Sub sample3()
Dim i As Long '系列のFor~Nextで使用 Dim rowMin As Long 'グラフデータ範囲の開始行 Dim rowMax As Long 'グラフデータ範囲の最終行 Dim strFormula As String 'グラフデータ範囲の設定文字列 Dim strExternal() As String 'グラフのSERIES関数の引数毎に分割した文字列 Dim strAddress() As String 'ADDRESS文字列をシートとRANGE指定に分割した文字列 Dim curRng As Range 'グラフデータのセル範囲 'Chartオブジェクトに対する処理 With ActiveSheet.ChartObjects(1).Chart '全系列に対する処理 For i = 1 To .SeriesCollection.Count 'グラフデータ範囲の設定文字列 strFormula = .SeriesCollection(i).Formula '=SERIES(引数・・・)を引数だけにする strFormula = Replace(Replace(strFormula, "=SERIES(", ""), ")", "") 'SERIES関数の引数毎に分割 strExternal = Split(strFormula, ",") '系列名(SERIES関数の第2引数)の処理 'ADDRESS文字列をシートとRANGE指定に分割、ADDRESSにブック名が入っている場合は消去 strAddress = Split(Replace(strExternal(1), "[" & ThisWorkbook.Name & "]", ""), "!") strAddress(0) = Replace(strAddress(0), "'", "") 'シートの前後の'を削除 '系列名の開始行 rowMin = Worksheets(strAddress(0)).Range(strAddress(1)).Item(1).Row '系列名の最終行 Set curRng = Worksheets(strAddress(0)).Range(strAddress(1)).CurrentRegion rowMax = curRng.Item(curRng.Count).Row '新しい系列名のセル範囲をValuesに設定 .SeriesCollection(i).Values = Worksheets(strAddress(0)).Range(strAddress(1)).Resize(rowMax - rowMin + 1, 1) '系列値(SERIES関数の第3引数)の処理 'ADDRESS文字列をシートとRANGE指定に分割、ADDRESSにブック名が入っている場合は消去 strAddress = Split(Replace(strExternal(2), "[" & ThisWorkbook.Name & "]", ""), "!") strAddress(0) = Replace(strAddress(0), "'", "") 'シートの前後の'を削除 '新しい系列値のセル範囲をXValuesに設定 .SeriesCollection(i).XValues = Worksheets(strAddress(0)).Range(strAddress(1)).Resize(rowMax - rowMin + 1, 1) Next i End With End Sub |
Sub sample11()
Dim ws As Worksheet Dim ch As ChartObject For Each ws In Worksheets For Each ch In ws.ChartObjects Call sumple12(ch) Next Next End Sub Sub sumple12(ch As ChartObject) ・・・ With ch.Chart ・・・ End With End Sub |
Sub sample()
Dim rng As Range Set rng = Range("A1") Call sample1(rng) End Sub |
Sub sample1(rng As Range)
Dim nm As Name For Each nm In Names If Not Intersect(rng, Range(nm.RefersTo)) Then nm.Delete End If Next End Sub |
Sub sample()
Dim rng As Range Set rng = Range("A1") Call sample2(rng) End Sub |
Sub sample2(rng As Range)
Dim nm As Name For Each nm In Names If rng.Address = nm.RefersToRange.Address Then nm.Delete End If Next End Sub |
Sub sample()
Dim rng As Range Set rng = Range("A1") Call sample3(rng, "NewName") End Sub |
Sub sample3(rng As Range, strName As String)
Dim nm As Name For Each nm In Names If rng.Address = nm.RefersToRange.Address Then nm.Delete End If Next rng.Parent.Parent.Names.Add Name:=strName, RefersToLocal:="=" & rng.Address(External:=True) End Sub |
Range("E2:G10").Name = "test" |
tryCnt = tryCnt - 1
Call dispCell(True, |
Sub sample()
Dim ws As Worksheet Dim rng As Range For Each ws In Worksheets For Each rng In ws.UsedRange If rng. HasFormula Then rng. Formula = Replace(rng. Formula , ws.Name & "!", "") End If Next Next End Sub |
Sub sample()
Dim i As Long Dim strArray() As String Dim strFile As String Dim strPath As String Dim strBook As String strPath = ThisWorkbook.Path & "\Excelファイル\" strFile = Dir(strPath & "*.xls") i = 0 Do While strFile <> "" If LCase(Right(strFile, 4)) = ".xls" Then ReDim Preserve strArray(i) strArray(i) = strFile i = i + 1 End If strFile = Dir() Loop For i = 0 To UBound(strArray) With Workbooks.Open(strPath & strArray(i)) strBook = Left(strArray(i), InStrRev(strArray(i), ".") - 1) If .HasVBProject Then If Dir(strPath & strBook & ".xlsm") = "" Then .SaveAs filename:=strPath & strBook & ".xlsm", _ fileformat:=xlOpenXMLWorkbookMacroEnabled Else .SaveAs filename:=strPath & strBook & "_" & Format(Now(), "yyyymmddhhmmss") & ".xlsm", _ fileformat:=xlOpenXMLWorkbookMacroEnabled End If Else If Dir(strPath & strBook & ".xlsx") = "" Then .SaveAs filename:=strPath & strBook & ".xlsx", _ fileformat:=xlWorkbookDefault Else .SaveAs filename:=strPath & strBook & "_" & Format(Now(), "yyyymmddhhmmss") & ".xlsx", _ fileformat:=xlWorkbookDefault End If End If .Close savechanges:=False End With Next End Sub |
Sub sample()
Dim rng As Range Dim FileName As String Dim fso As New FileSystemObject ChDir ThisWorkbook.Path For Each rng In ActiveSheet.UsedRange If rng.Hyperlinks.Count > 0 Then FileName = fso. GetAbsolutePathName (rng.Hyperlinks(1).Address) If fso.FileExists(FileName) Then rng.Offset(0, 1) = fso.GetFile(FileName). DateLastModified End If End If Next End Sub |
Sub sample2()
Dim rng As Range Dim FileName As String For Each rng In ActiveSheet.UsedRange If rng.Hyperlinks.Count > 0 Then FileName = rng.Hyperlinks(1).Address If Dir (FileName) <> "" Then rng.Offset(0, 1) = FileDateTime (FileName) End If End If Next End Sub |
Sub sample()
Sheets(ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text).Select End Sub |
Sub sample()
Dim ppApp As New PowerPoint.Application Dim ppPt As Presentation Dim ppSlide As Slide Dim ppShape As PowerPoint.Shape Dim ws As Worksheet 'ppApp.Visible = True ' PowerPoint2007以前の場合は有効にしてください。 Set ppPt = ppApp.Presentations.Open(ThisWorkbook.Path & "\sample.pptx") Set ws = ThisWorkbook.Worksheets("Sheet1") With ws .Range("A1").CurrentRegion.Copy ' PasteSpeciaでエラーが出るときは、ここに待ちを作ります。 'スライド番号を指定 Set ppSlide = ppPt.Slides(1) ppSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile, Link:=msoFalse Set ppShape = ppSlide.Shapes(ppSlide.Shapes.Count) '上位置 ppShape.Top = Application.CentimetersToPoints(1) '左位置 ppShape.Left = Application.CentimetersToPoints(1) '縦横比を固定 ppShape.LockAspectRatio = msoTrue '横幅 ppShape.Width = Application.CentimetersToPoints(30) Application.CutCopyMode = False End With ppPt.Save ppApp.Quit Set ppPt = Nothing Set ppApp = Nothing End Sub |
DataType | PpPasteDataType | クリップボードの内容をドキュメントに挿入するときの形式です。
既定値は、クリップボードの内容によって異なります。 クリップボードの内容が引数 DataType で指定したデータ型がサポートされていない場合、エラーが発生します。 DataTypeには以下が指定可能です。 ppPasteBitmap ppPasteDefault ppPasteEnhancedMetafile ppPasteHTML ppPasteGIF ppPasteJPG ppPasteMetafilePicture ppPastePNG ppPasteShape |
DisplayAsIcon | MsoTriState | 埋め込みオブジェクトまたはリンクをアイコンで表示するには、MsoTrue?を設定します。 |
IconFileName | 文字列型 (String) | 引数 DisplayAsIcon が定数?msoTrue?に設定されている場合、この引数は表示するアイコンが保存されているファイルのパスとファイル名となります。
引数 DisplayAsIcon が定数?msoFalse?に設定されている場合、この引数は無視されます。 |
IconIndex | 長整数型 (Long) | DisplayAsIcon がmsoTrueに設定されている場合この引数は IconFilename で指定されるプログラム ファイルで使用するアイコンに対応する番号です。
たとえば、0 (ゼロ) は最初のアイコンに対応して 1、2 番目のアイコンに対応します。この引数を省略すると、最初の (既定の) アイコンが使用されます。 DisplayAsIcon がmsoFalseに設定されている場合、この引数は無視されます。IconIndex が有効な範囲外にある場合は、既定のアイコン (インデックス 0) が使用されます。 |
IconLabel | 文字列型 (String) | 引数 DisplayAsIcon が定数?msoTrue?に設定されている場合、この引数はアイコンの下に表示されるテキストとなります。
このラベルがない場合、クリップボードの内容に基づいてアイコン ラベルが作成されます。 引数 DisplayAsIcon が定数?msoFalse?に設定されている場合、この引数は無視されます。 |
Link | MsoTriState | クリップボード内容のソース ファイルへのリンクを作成するかどうかを指定します。クリップボードの内容がリンクをサポートしていない場合、エラーが発生します |
Option Explicit
'Changeイベント Private Sub Worksheet_Change(ByVal Target As Range) Dim sRng As Range '開始セル Dim cRng As Range '処理中セル Dim nRng As Range '次のセル Dim strIn As String '入力文字 '入力された先頭セルに限定 Set sRng = Target.Item(1) '空欄なら無視 If IsEmpty(sRng) Then Exit Sub End If '結合範囲を取得 Set cRng = sRng.MergeArea '入力文字 strIn = sRng.Value 'イベント停止 Application.EnableEvents = False '文字を全て配置したか、方眼紙の枠がなくなるまで Do '文字を全て配置が終わった If Len(strIn) = 0 Then Exit Do End If '次の方眼紙の枠を取得、無ければ残りの文字を入れる Set nRng = getNext(cRng) If nRng Is Nothing Then cRng.Value = strIn Exit Do End If '左の1文字を入れる cRng.Value = Left(strIn, 1) '文字を2文字目からに strIn = Mid(strIn, 2) '次の方眼紙の枠のセルを次のセルに Set cRng = nRng Loop 'イベント再開 Application.EnableEvents = True End Sub '方眼紙の枠のセルか判定 Private Function isGraph(ByVal rng As Range) As Boolean '四隅に罫線が引かれている場合に、方眼紙の枠と判定 If rng.Borders(xlEdgeTop).LineStyle <> xlNone And _ rng.Borders(xlEdgeBottom).LineStyle <> xlNone And _ rng.Borders(xlEdgeLeft).LineStyle <> xlNone And _ rng.Borders(xlEdgeRight).LineStyle <> xlNone Then isGraph = True Else isGraph = False End If End Function '次の方眼紙の枠を取得 Private Function getNext(ByVal cRng As Range) As Range Dim rng As Range Dim nRng As Range '右のセルが方眼紙の枠なら、それを設定 If isGraph(cRng.Offset(, 1).MergeArea) = True Then Set getNext = cRng.Offset(, 1) Exit Function End If '右のセルが方眼紙の枠以外の時は、下のセルを判定 Set rng = cRng.Offset(1).MergeArea '下のセルが方眼紙の枠でなければ終わり If isGraph(rng) = False Then Set getNext = Nothing Exit Function End If '左に向かって先頭列の方眼紙の枠を探す Do 'A列なら終わり If rng.Column = 1 Then Set getNext = rng Exit Function End If '左が方眼紙の枠でなければ終わり If isGraph(rng.Offset(, -1).MergeArea) = False Then Set getNext = rng Exit Function End If '次の行の先頭の方眼紙の枠セル Set rng = rng.Offset(, -1).MergeArea Loop End Function |
Sub TrimPicture()
Const OffTop As Double = 100 '上の切り取り Const OffLeft As Double = 150 '左の切り取り Const OffBottom As Double = 400 '下の切り取り Const OffRight As Double = 600 '右の切り取り Dim ws As Worksheet Dim sp1 As Shape Set ws = ActiveSheet Set sp1 = ws.Shapes(1) With sp1 .PictureFormat.CropTop = OffTop .PictureFormat.CropLeft = OffLeft .PictureFormat.CropBottom = OffBottom .PictureFormat.CropRight = OffRight End With End Sub |
Sub UndoPicture()
Dim ws As Worksheet Dim sp1 As Shape Set ws = ActiveSheet Set sp1 = ws.Shapes(1) With sp1.PictureFormat .CropLeft = 0 .CropRight = 0 .CropTop = 0 .CropBottom = 0 End With End Sub |
Sub TrimPictureAndPaste()
Const OffTop As Double = 56 Const OffLeft As Double = 133 Const OffBottm As Double = 464 Const OffRight As Double = 569 Dim ws As Worksheet Dim sp1 As Shape Dim sp2 As Shape Set ws = ActiveSheet Set sp1 = ws.Shapes(1) With sp1 .PictureFormat.CropTop = OffTop .PictureFormat.CropLeft = OffLeft .PictureFormat.CropBottom = OffBottm .PictureFormat.CropRight = OffRight .Copy ws.PasteSpecial Format:="図 (JPEG)", Link:=False, DisplayAsIcon:=False Set sp2 = ws.Shapes(ws.Shapes.Count) sp2.Top = .Top sp2.Left = .Left .Delete End With End Sub |
Option Explicit
'使用例 Sub sample1() Dim ws As Worksheet Dim sFile As String 'csvファイルを指定 sFile = "csvのフルパス" '出力シート Set ws = ActiveSheet ws.Cells.Clear '以下では全列を文字に設定 '数値も文字としてセルに入ります '文字設定にしなければ数値は数値として入ります。 ws.Cells.NumberFormatLocal = "@" Application.ScreenUpdating = False 'CSV取込 Call CsvInText(ws, sFile) 'utf-8決め打ちで読み込む場合は以下で 'Call CsvInText(ws, sFile, "utf-8") Application.ScreenUpdating = True End Sub Sub CsvInText(ByVal ws As Worksheet, _ ByVal strFile As String, _ Optional ByVal CharSet As String = "Auto") Dim strRec As String Dim i As Long, j As Long, k As Long Dim lngQuate As Long Dim strCell As String Dim blnCrLf As Boolean '文字コードを自動判別し、全行をCrLf区切りに統一してStringに入れる strRec = readCsv(strFile, CharSet) i = 1 'シートの1行目から出力 j = 0 '列位置はPutCellでカウントアップ lngQuate = 0 'ダブルクォーテーションの数 strCell = "" For k = 1 To Len(strRec) Select Case Mid(strRec, k, 1) Case vbLf, vbCr '「"」が偶数なら改行、奇数ならただの文字 If lngQuate Mod 2 = 0 Then blnCrLf = False If k > 1 Then '改行のCrLfはCrで改行判定済なので無視する If Mid(strRec, k - 1, 2) = vbCrLf Then blnCrLf = True End If End If If blnCrLf = False Then Call PutCell(ws, i, j, strCell, lngQuate) i = i + 1 j = 0 lngQuate = 0 strCell = "" End If Else strCell = strCell & Mid(strRec, k, 1) End If Case ",", vbTab '「"」が偶数なら区切り、奇数ならただの文字 If lngQuate Mod 2 = 0 Then Call PutCell(ws, i, j, strCell, lngQuate) Else strCell = strCell & Mid(strRec, k, 1) End If Case """" '「"」のカウントをとる lngQuate = lngQuate + 1 strCell = strCell & Mid(strRec, k, 1) Case Else strCell = strCell & Mid(strRec, k, 1) End Select Next '最終列の処理 If j > 0 And strCell <> "" Then Call PutCell(ws, i, j, strCell, lngQuate) End If End Sub '1フィールドごとにセルに出力 Sub PutCell(ByVal ws As Worksheet, _ ByRef i As Long, ByRef j As Long, _ ByRef strCell As String, _ ByRef lngQuate As Long) j = j + 1 '「""」を「"」で置換 strCell = Replace(strCell, """""", """") '前後の「"」を削除 If Left(strCell, 1) = """" And Right(strCell, 1) = """" Then If Len(strCell) <= 2 Then strCell = "" Else strCell = Mid(strCell, 2, Len(strCell) - 2) End If End If ws.Cells(i, j) = strCell strCell = "" lngQuate = 0 End Sub '文字コードを自動判別し、全行をCrLf区切りに統一してStringに入れる Function readCsv(ByVal strFile As String, _ ByVal CharSet As String) As String Dim objFSO As New FileSystemObject Dim inTS As TextStream Dim adoSt As New ADODB.Stream Dim strRec As String Dim i As Long Dim aryRec() As String If CharSet = "Auto" Then CharSet = getCharSet(CStr(strFile)) Select Case LCase(CharSet) Case "unicode", "unicodefeff" 'TristateTrueで読込 Set inTS = objFSO.OpenTextFile(CStr(strFile), ForReading, , TristateTrue) strRec = inTS.ReadAll Case "utf-8", "utf-8n" 'ADOを使って読込、その後の処理を統一するため全レコードをCrLfで結合 With adoSt .Type = adTypeText .CharSet = "UTF-8" .Open .LoadFromFile strFile i = 0 Do While Not (.EOS) ReDim Preserve aryRec(i) aryRec(i) = .ReadText(adReadLine) i = i + 1 Loop .Close strRec = Join(aryRec, vbCrLf) End With Case Else Set inTS = objFSO.OpenTextFile(CStr(strFile), ForReading) strRec = inTS.ReadAll End Select Set inTS = Nothing Set objFSO = Nothing readCsv = strRec End Function '文字コードの自動判別 'UTF-8のBOMなしは文字コードの判別に対応できていません。 Function getCharSet(ByVal sFile As String) As String Dim objHtml As MSHTML.HTMLDocument Dim strRec As String 'GetObjectでHTMLDocumentを生成し、文字コードを判定する Set objHtml = GetObject(sFile, "htmlfile") Do While objHtml.readyState <> "complete" DoEvents Loop getCharSet = objHtml.CharSet Set objHtml = Nothing End Function |
Sub sample2()
Dim ws As Worksheet Dim sFile As String sFile = "パス\test.csv" Set ws = Worksheets("Sheet1") ws.Cells.Clear ws.Cells.NumberFormatLocal = "@" Call CsvInQuery(ws, sFile) End Sub Sub CsvInQuery(ByVal ws As Worksheet, ByVal sFile As String) Dim cArray() As Integer Dim i As Long ReDim cArray(255) For i = 0 To 255 cArray(i) = XlColumnDataType.xlTextFormat Next With ws.QueryTables.Add(Connection:="TEXT;" & sFile, Destination:=ws.Range("$A$1")) .TextFileTabDelimiter = True .TextFileCommaDelimiter = True .TextFileColumnDataTypes = cArray Select Case LCase(getCharSet(CStr(sFile))) Case "utf-8", "unicode", "unicodefeff" .TextFilePlatform = 65001 Case Else .TextFilePlatform = 932 End Select .Refresh BackgroundQuery:=False .Delete End With End Sub Function getCharSet(ByVal sFile As String) As String Dim objHtml As MSHTML.HTMLDocument Set objHtml = GetObject(sFile, "htmlfile") Do While objHtml.readyState <> "complete" DoEvents Loop getCharSet = objHtml.charSet Set objHtml = Nothing End Function |
Function getFormulaRange(ByVal argRange As Range) As Range()
Dim sFormula As String Dim aryRange() As Range Dim tRange As Range Dim ix As Long Dim i As Long Dim flgS As Boolean 'シングルクオートが奇数の時True Dim flgD As Boolean 'ダブルクオートが奇数の時True Dim sSplit() As String Dim sTemp As String '=以降の計算式 sFormula = Mid(argRange.FormulaLocal, 2) '計算式の中の改行や余分な空白を除去 sFormula = Replace(sFormula, vbCrLf, "") sFormula = Replace(sFormula, vbLf, "") sFormula = Trim(sFormula) flgS = False flgD = False For i = 1 To Len(sFormula) 'シングル・ダブルのTrue,Falseを反転 Select Case Mid(sFormula, i, 1) Case "'" flgS = Not flgS Case """" 'シングルの中ならシート名 If Not flgS Then flgD = Not flgD End If End Select Select Case Mid(sFormula, i, 1) '各種演算子の判定 Case "+", "-", "*", "/", "^", ">", "<", "=", "(", ")", "&", ",", " " Select Case True Case flgS 'シングルの中ならシート名 sTemp = sTemp & Mid(sFormula, i, 1) Case flgD 'ダブルの中なら無視 Case Else '各種演算子をvbLfに置換 sTemp = sTemp & vbLf End Select Case Else 'ダブルの中なら無視、ただしシングルの中はシート名 If Not flgD Or flgS Then sTemp = sTemp & Mid(sFormula, i, 1) End If End Select Next On Error Resume Next 'vbLfで区切って配列化 sSplit = Split(sTemp, vbLf) ix = 0 For i = 0 To UBound(sSplit) If sSplit(i) <> "" Then Err.Clear 'Application.Evaluateメソッドを使ってRangeに変換 If InStr(sSplit(i), "!") > 0 Then Set tRange = Evaluate(Trim(sSplit(i))) Else 'シート名を含まない場合は、元セルのシート名を付加 Set tRange = Evaluate("'" & argRange.Parent.Name & "'!" & Trim(sSplit(i))) End If 'Rangeオブジェクト化が成功すれば配列へ入れる If Err.Number = 0 Then ReDim Preserve aryRange(ix) Set aryRange(ix) = tRange ix = ix + 1 End If End If Next On Error GoTo 0 getFormulaRange = aryRange End Function |
Sub sample()
Dim aryRange() As Range Dim var As Variant aryRange = getFormulaRange(Range("A1")) For Each var In aryRange Debug.Print var.Address(External:=True) Next End Sub |
Sub sample2()
Dim objFSO As New FileSystemObject objFSO.DeleteFolder "フォルダのフルパス" Set objFSO = Nothing End Sub |
'**********************************************************************
' sDir : 対象フォルダ ' sMsg : エラー発生時のメッセージ保存 ' isOnlyFile : ファイルのみ削除する場合にTrue '********************************************************************** Function DelDir(ByVal sDir As String, _ ByRef sMsg As String, _ Optional ByVal isOnlyFile As Boolean = False) _ As Boolean Dim objFSO As New FileSystemObject Dim objFolder As Folder sMsg = "" If Not objFSO.FolderExists(sDir) Then sMsg = "指定のフォルダは存在しません。" DelDir = False Exit Function End If Set objFolder = objFSO.GetFolder(sDir) Call DelDirectorys(objFolder, isOnlyFile, sMsg) If sMsg = "" Then DelDir = True Else DelDir = False End If End Function Sub DelDirectorys(ByVal objFolder As Folder, _ ByVal isOnlyFile As Boolean, _ ByRef sMsg As String) Dim objFolderSub As Folder Dim objFile As File On Error Resume Next For Each objFolderSub In objFolder.SubFolders Call DelDirectorys(objFolderSub, isOnlyFile, sMsg) Next For Each objFile In objFolder.Files objFile.Delete If Err.Number <> 0 Then sMsg = sMsg & "ファイル「" & objFile.Path & "」が削除できませんでした" & vbLf Err.Clear End If Next If Not isOnlyFile Then objFolder.Delete If Err.Number <> 0 Then sMsg = sMsg & "フォルダ「" & objFolder.Path & "」が削除できませんでした" & vbLf Err.Clear End If End If Set objFolderSub = Nothing Set objFile = Nothing On Error GoTo 0 End Sub |
Sub sample()
Dim sMsg As String If DelDir(ThisWorkbook.Path & "\test", sMsg) Then MsgBox "削除終了" Else MsgBox sMsg End If End Sub |
Option Explicit
Private blnStop As Boolean Private blnStart As Boolean Private dblTimer As Double Private bLap As Double Private cntLap As Long Sub StartStop() If blnStart = True Then blnStop = True Exit Sub End If bLap = 0 cntLap = 0 blnStart = True blnStop = False dblTimer = Timer Do Until blnStop = True Cells(2, 2) = Int((Timer - dblTimer) * 100) / 100 DoEvents Loop blnStart = False blnStop = False End Sub Sub LapSplit() cntLap = cntLap + 1 Cells(cntLap + 4, 2) = cntLap Cells(cntLap + 4, 3) = Int((Timer - dblTimer) * 100) / 100 - bLap Cells(cntLap + 4, 4) = Int((Timer - dblTimer) * 100) / 100 bLap = Cells(cntLap + 4, 4) End Sub Sub LapSplitClear() Range("B4").CurrentRegion.Offset(1).ClearContents bLap = 0 cntLap = 0 End Sub |
Sub sample1()
With Worksheets("Sheet1").Range("A1:A10") .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="90%" .FormatConditions(1).Interior.Color = vbRed .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="100%" .FormatConditions(2).Interior.Color = vbYellow End With End Sub |
Sub sample2()
Dim ws As Worksheet Dim fObj As Object Dim i As Long Set ws = ActiveSheet ws.Range("3:1000").FormatConditions.Delete For i = ws.Rows(2).FormatConditions.Count To 1 Step -1 Set fObj = ws.Rows(2).FormatConditions(i) fObj.ModifyAppliesToRange fObj.AppliesTo.Resize(999) Next End Sub |
名前 | 必須 | 説明 |
Formula | 必須 | 変換する数式を含む文字列を指定します。
必ず有効な数式を指定し、数式の先頭には等号 (=) を付けてください。 |
FromReferenceStyle | 必須 | 変換前の参照形式を、XlReferenceStyleの定数で指定します。 |
ToReferenceStyle | 省略可 | 取得する参照スタイルを指定するXlReferenceStyleの定数です。
この引数を省略すると参照形式は変更されず、引数FromReferenceStyleで指定された形式が使用されます。 |
ToAbsolute | 省略可 | 変換された参照型を指定するXlReferenceTypeの定数です。
この引数を省略すると、参照の種類は変更されません。 |
RelativeTo | 省略可 | 1 つのセルを含むRangeオブジェクトを指定します。
このセルは、相対参照の基点となります。 |
Option Explicit
'条件付き書式を格納する構造体 Type tFormat AppliesTo As String '適用範囲 Formula1 As String '数式1 Formula2 As String '数式2 Operator As String '演算子 NumberFormat As String '表示形式 FontBold As String '太字 FontColor As String '文字色 InteriorColor As String '塗りつぶし色 '追加判定したいプロパティはここに追加 End Type Public Sub UnionFormatConditions(ByVal ws As Worksheet, _ Optional ByVal NewName As String = "") '条件付き書式を格納する構造体配列 Dim fAry() As tFormat '条件付き書式が無い場合は終了 If ws.Cells.FormatConditions.Count = 0 Then Exit Sub 'オプションにより元シートをコピー If NewName <> "" Then ws.Copy After:=ws Set ws = ActiveSheet ws.Name = NewName 'シート名のチェックは省略しています。 End If '条件付き書式を構造体配列へ格納 Call SetFormatToType(fAry, ws) '同一条件付き書式の結合:配列内でセル範囲指定文字列を結合 Call JoinAppliesTo(fAry, ws) '条件付き書式の統合:配列内のAppliesをFormatConditionに適用 Call ModifyApplies(fAry, ws) End Sub '条件付き書式を構造体配列へ格納 Private Sub SetFormatToType(ByRef fAry() As tFormat, _ ByVal ws As Worksheet) Dim i As Long Dim fObj As FormatCondition On Error Resume Next '.Formula2が取得できない場合の対処 ReDim fAry(ws.Cells.FormatConditions.Count) For i = 1 To ws.Cells.FormatConditions.Count Set fObj = ws.Cells.FormatConditions(i) fAry(i).AppliesTo = fObj.AppliesTo.Address fAry(i).Formula1 = fObj.Formula1 fAry(i).Formula2 = fObj.Formula2 fAry(i).Operator = fObj.Operator fAry(i).NumberFormat = fObj.NumberFormat fAry(i).FontBold = fObj.Font.Bold fAry(i).FontColor = fObj.Font.Color fAry(i).InteriorColor = fObj.Interior.Color '追加判定したいプロパティはここに追加 '数式エラーの条件付き書式は削除をする If isErrorFormula(fAry(i).Formula1) Or _ isErrorFormula(fAry(i).Formula1) Then fAry(i).AppliesTo = "" End If Next End Sub '条件付き書式の数式エラー判定 Private Function isErrorFormula(ByVal sFormula As String) As Boolean If IsError(Evaluate(sFormula)) Then isErrorFormula = True Else isErrorFormula = False End If End Function '同一条件付き書式の結合:配列内でセル範囲指定文字列を結合 Private Sub JoinAppliesTo(ByRef fAry() As tFormat, _ ByVal ws As Worksheet) Dim i1 As Long, i2 As Long For i1 = 1 To UBound(fAry) For i2 = 1 To i1 - 1 '計算式1,2、文字色、塗りつぶしの一致判定 If isMatchFormat(fAry(i1), fAry(i2), ws) Then fAry(i2).AppliesTo = Union(Range(fAry(i2).AppliesTo), _ Range(fAry(i1).AppliesTo)).Address fAry(i1).AppliesTo = "" Exit For End If Next Next End Sub '計算式1,2、演算子、文字色、塗りつぶしの一致判定 Private Function isMatchFormat(ByRef fAry1 As tFormat, _ ByRef fAry2 As tFormat, _ ByVal ws As Worksheet) As Boolean If fAry1.AppliesTo = "" Or _ fAry2.AppliesTo = "" Then Exit Function End If Dim sFormula1 As String, sFormula2 As String isMatchFormat = True '計算式1 sFormula1 = ToR1C1(fAry1.Formula1, fAry1.AppliesTo) sFormula2 = ToR1C1(fAry2.Formula1, fAry2.AppliesTo) If sFormula1 <> sFormula2 Then isMatchFormat = False '計算式2 sFormula1 = ToR1C1(fAry1.Formula2, fAry1.AppliesTo) sFormula2 = ToR1C1(fAry2.Formula2, fAry2.AppliesTo) If sFormula1 <> sFormula2 Then isMatchFormat = False '演算子 If fAry1.Operator <> fAry2.Operator Then isMatchFormat = False '表示形式 If fAry1.NumberFormat <> fAry2.NumberFormat Then isMatchFormat = False '太字 If fAry1.FontBold <> fAry2.FontBold Then isMatchFormat = False '文字色 If fAry1.FontColor <> fAry2.FontColor Then isMatchFormat = False '塗りつぶし If fAry1.InteriorColor <> fAry2.InteriorColor Then isMatchFormat = False '追加判定したいプロパティはここに追加 End Function 'A1形式をR1C1形式に変換 Private Function ToR1C1(ByVal sFormula As String, _ ByVal sAppliesTo As String) If sFormula = "" Then Exit Function Dim rng As Range Set rng = Range(sAppliesTo) ToR1C1 = Application.ConvertFormula(sFormula, xlA1, xlR1C1, , rng.Item(1)) End Function '条件付き書式の統合:配列内のAppliesをFormatConditionに適用 Private Sub ModifyApplies(ByRef fAry() As tFormat, _ ByVal ws As Worksheet) Dim i As Long Dim fObj As Object For i = ws.Cells.FormatConditions.Count To 1 Step -1 Set fObj = ws.Cells.FormatConditions(i) If fAry(i).AppliesTo = "" Then fObj.Delete Else If fObj.AppliesTo.Address <> ws.Range(fAry(i).AppliesTo).Address Then fObj.ModifyAppliesToRange ws.Range(fAry(i).AppliesTo) End If End If Next End Sub |
Sub sample1()
Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim ws As Worksheet Set ws = ActiveSheet Call UnionFormatConditions(ws, ws.Name & "_test") Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
Sub sample2()
Dim FileName As Variant Dim wb As Workbook Dim ws As Worksheet FileName = Application.GetOpenFilename(FileFilter:="Excelファイル, *.xls*") If FileName = False Then Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wb = Workbooks.Open(FileName:=FileName, UpdateLinks:=0, ReadOnly:=True) For Each ws In wb.Worksheets Call UnionFormatConditions(ws) Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True FileName = Application.GetSaveAsFilename(InitialFileName:=wb.Name, _ FileFilter:="Excelファイル,*.xls*") If FileName = False Then Exit Sub End If wb.SaveAs FileName wb.Close SaveChanges:=True End Sub |
名前 | 説明 |
AddIndent | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトについて、セル内の文字列の配置で縦または横位置を均等に割り付けるときに、文字列を自動的にインデントするかどうかを示す値を返します。値の取得のみ可能です。 |
Application | オブジェクト修飾子を指定せずに使用した場合、Microsoft Excel アプリケーションを表す Application オブジェクトを返します。オブジェクト修飾子を指定した場合、指定したオブジェクトを作成した Application オブジェクトを返します。値の取得のみ可能です。 |
Borders | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトの境界線を表す Borders オブジェクトを返します。値の取得のみ可能です。 |
Characters | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトのテキスト内の文字範囲を表す Characters オブジェクトを返します。値の取得のみ可能です。 |
Creator | 現在のオブジェクトが作成されたアプリケーションを示す 32 ビットの整数を取得します。値の取得のみ可能です。長整数型 (Long) の値を使用します。 |
Font | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトのフォントを表す Font オブジェクトを返します。値の取得のみ可能です。 |
FormulaHidden | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトについて、ワークシートが保護されているときに数式を非表示にするかどうかを示す値を返します。値の取得のみ可能です。 |
HorizontalAlignment | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトの水平方向の配置を表す値を返します。値の取得のみ可能です。 |
IndentLevel | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトのインデント レベルを表す値を返します。値の取得のみ可能です。 |
Interior | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトの内部を表す Interior オブジェクトを返します。値の取得のみ可能です。 |
Locked | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトがロックされているかどうかを示す値を返します。値の取得のみ可能です。 |
MergeCells | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトに、結合されたセルが含まれているかどうかを示す値を返します。値の取得のみ可能です。 |
NumberFormat | 現在のユーザー インターフェイスに表示されるている、関連付けられた Range オブジェクトの表示形式を表す値を返します。値の取得のみ可能です。
これが正しく取得できませんでした。 |
NumberFormatLocal | 現在のユーザー インターフェイスに表示されるている、関連付けられた Range オブジェクトの表示形式を、ユーザーの言語の文字列で表す値を返します。値の取得のみ可能です。 |
Orientation | 現在のユーザー インターフェイスに表示されるている、関連付けられた Range オブジェクトの文字列の向きを表す値を返します。値の取得のみ可能です。 |
Parent | 指定されたオブジェクトの親オブジェクトを取得します。値の取得のみ可能です。 |
ReadingOrder | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトの読み取り順序を返します。値の取得のみ可能です。 |
ShrinkToFit | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトについて、使用可能な列幅に収まるように自動的に文字列を縮小するかどうかを示す値を返します。値の取得のみ可能です。 |
Style | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトのスタイルを表す、Style オブジェクトを含む値を返します。 |
VerticalAlignment | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトの垂直方向の配置を表す値を返します。値の取得のみ可能です。 |
WrapText | 現在のユーザー インターフェイスに表示されている、関連付けられた Range オブジェクトについて、文字列を折り返すかどうかを示す値を返します。値の取得のみ可能です。 |
Option Explicit
Public Function CopySheet(ByVal ws As Worksheet) As Workbook Dim wsNew As Worksheet Dim wsW As Worksheet Dim myRange As Range Dim fObj As Object Dim fRange As Range Dim aryDiagona As Variant Dim sFormat As String Dim i As Long '事前設定 aryDiagona = Array(xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, xlInsideVertical) '指定シートをコピーし新規シートを作成 ws.Copy After:=ws Set wsNew = ActiveSheet Set wsW = Worksheets.Add '表示形式の確認で使うワークシート 'シート全体を値貼り付け 'Valueの代入ではエラーになる場合があるので普通に貼り付け wsNew.Cells.Copy wsNew.Cells.PasteSpecial Paste:=xlPasteValues '条件付き書式で表示されている書式を通常書式として設定 '条件付き書式を使っていない場合はエラーとなる On Error Resume Next Set fRange = wsNew.Cells.SpecialCells(xlCellTypeAllFormatConditions) If Err Then Err.Clear Else On Error GoTo 0 For Each myRange In fRange With myRange.DisplayFormat 'フォント myRange.Font.Color = .Font.Color myRange.Font.Bold = .Font.Bold myRange.Font.Italic = .Font.Italic myRange.Font.Strikethrough = .Font.Strikethrough '塗りつぶし myRange.Interior.Color = .Interior.Color '罫線 For i = LBound(aryDiagona) To UBound(aryDiagona) If .Borders(aryDiagona(i)).LineStyle <> XlLineStyle.xlLineStyleNone Then myRange.Borders(aryDiagona(i)).LineStyle = .Borders(aryDiagona(i)).LineStyle myRange.Borders(aryDiagona(i)).Weight = .Borders(aryDiagona(i)).Weight myRange.Borders(aryDiagona(i)).Color = .Borders(aryDiagona(i)).Color End If Next '表示形式、これは取得できないようです myRange.NumberFormatLocal = .NumberFormatLocal 'そこで条件付き書式を順に確認 wsW.Range("A1") = myRange wsW.Range("A1").NumberFormatLocal = myRange.NumberFormatLocal If myRange.Text <> wsW.Range("A1").Text Then For i = myRange.FormatConditions.Count To 1 Step -1 Set fObj = myRange.FormatConditions(i) If TypeName(fObj) = "FormatCondition" And _ Not IsEmpty(fObj.NumberFormat) Then wsW.Range("A1").NumberFormatLocal = CStr(fObj.NumberFormat) If myRange.Text = wsW.Range("A1").Text Then myRange.NumberFormatLocal = fObj.NumberFormat End If End If Next End If End With Next End If On Error GoTo 0 '条件付き書式を削除 For i = wsNew.Cells.FormatConditions.Count To 1 Step -1 Set fObj = wsNew.Cells.FormatConditions(i) Select Case fObj.Type Case xlIconSets 'アイコンセット Case xlDatabar 'データバーは条件付き書式を変更 Stop fObj.BarBorder.Color.Color = fObj.BarBorder.Color.Color fObj.BarColor.Color = fObj.BarColor.Color Case Else wsNew.Cells.FormatConditions(i).Delete End Select Next 'フォント、塗りつぶし、罫線の色を固定色に変更 For Each myRange In wsNew.UsedRange 'フォント myRange.Font.ThemeFont = xlThemeFontNone myRange.Font.Name = myRange.Font.Name If myRange.Font.ColorIndex <> xlColorIndexNone Then myRange.Font.Color = myRange.Font.Color End If '塗りつぶし If myRange.Interior.ColorIndex <> xlColorIndexNone Then myRange.Interior.Color = myRange.Interior.Color End If '罫線 For i = LBound(aryDiagona) To UBound(aryDiagona) If myRange.Borders(aryDiagona(i)).ColorIndex <> xlColorIndexNone Then myRange.Borders(aryDiagona(i)).Color = myRange.Borders(aryDiagona(i)).Color End If Next Next '元のシートを選択 ws.Select '新規作成シートを新規ブックへ移動 wsNew.Move Set CopySheet = ActiveWorkbook '表示形式の確認で使ったワークシートの削除 Application.DisplayAlerts = False wsW.Delete Application.DisplayAlerts = True End Function |
Sub sample()
Dim wb As Workbook Set wb = CopySheet (ActiveSheet) End Sub |
Public Sub permutation(ByRef aryIn, ByRef aryOut, Optional ByVal i As Long = 0)
Dim j As Long Dim ix As Long Dim sTemp Dim ary If i < UBound(aryIn) Then For j = i To UBound(aryIn) '配列を入れ替える ary = aryIn sTemp = aryIn(i) aryIn(i) = aryIn(j) aryIn(j) = sTemp '再帰処理、開始位置を+1 Call permutation(aryIn, aryOut, i + 1) aryIn = ary '配列を元に戻す Next Else '配列の最後まで行ったので出力 If IsEmpty(aryOut) Or Not IsArray(aryOut) Then ix = 0 ReDim aryOut(UBound(aryIn), ix) Else ix = UBound(aryOut, 2) + 1 ReDim Preserve aryOut(UBound(aryIn), ix) End If For j = LBound(aryIn) To UBound(aryIn) aryOut(j, ix) = aryIn(j) Next j End If End Sub |
Sub sample1()
Dim aryIn Dim aryOut '入力配列 aryIn = Array(1, 2, 3, 4, 5) '順列作成 Call permutation(aryIn, aryOut) 'シートへ出力 Cells.ClearContents Range("A1").Resize(UBound(aryOut, 2) + 1, UBound(aryOut, 1) + 1) = _ WorksheetFunction.Transpose (aryOut) End Sub |
Sub sample2()
Dim aryIn Dim aryOut Dim sryRtn Dim i As Long Dim i1 As Long Dim i2 As Long Dim sString As String Dim sDelimiter As String '入力配列 sString = "A B C D E" sDelimiter = " " '区切り文字 aryIn = Split(sString, sDelimiter) '順列作成 Call permutation(aryIn, aryOut) 'シートへ出力 ReDim aryRtn(UBound(aryOut, 2)) For i2 = LBound(aryOut, 2) To UBound(aryOut, 2) For i1 = LBound(aryOut, 1) To UBound(aryOut, 1) If i1 = LBound(aryOut, 1) Then aryRtn(i2) = aryOut(i1, i2) Else aryRtn(i2) = aryRtn(i2) & sDelimiter & aryOut(i1, i2) End If Next Next Cells.ClearContents For i = LBound(aryRtn) To UBound(aryRtn) Cells(i + 1, 1) = aryRtn(i) Next End Sub |
Sub sample3()
Dim aryIn Dim aryOut Dim aryNum1 Dim aryNum2 Dim aryTemp Dim pCnt As Integer Dim i1 As Long Dim i2 As Long Dim ix As Long Dim sTemp1 As String Dim sTemp2 As String Dim flg As Boolean '入力配列 aryIn = Array("A", "B", "C", "D", "E") pCnt = 3 '取り出す数 '入力配列から指定数を取り出す 'まずは、1,2,3,4,5を作成 ReDim aryTemp(UBound(aryIn)) For i1 = 0 To UBound(aryIn) aryTemp(i1) = i1 Next '1,2,3,4,5の順列作成 Call permutation(aryTemp, aryNum1) '1,2,3,4,5の順列から先頭の指定数を取り出す 'ここは組み合わせを作りたいので順序違いも省く ix = 0 ReDim aryNum2(pCnt - 1, ix) For i2 = 0 To UBound(aryNum1, 2) sTemp1 = "" sTemp2 = "" flg = True If i2 = 0 Then sTemp1 = "1" sTemp2 = "2" Else For i1 = 0 To pCnt - 1 sTemp1 = sTemp1 & "_" & aryNum1(i1, i2 - 1) sTemp2 = sTemp2 & "_" & aryNum1(i1, i2) If i1 > 0 Then If aryNum1(i1 - 1, i2) > aryNum1(i1, i2) Then flg = False End If End If Next End If If sTemp1 <> sTemp2 And flg = True Then ReDim Preserve aryNum2(pCnt - 1, ix) For i1 = 0 To pCnt - 1 aryNum2(i1, ix) = aryNum1(i1, i2) Next ix = ix + 1 End If Next '順列作成しつつシートへ出力 Cells.ClearContents ix = 1 '組み合わせ数の繰り返し For i2 = 0 To UBound(aryNum2, 2) '入力配列から指定数の組み合わせの配列作成 aryOut = "" ReDim aryTemp(pCnt - 1) For i1 = 0 To pCnt - 1 aryTemp(i1) = aryIn(aryNum2(i1, i2)) Next '順列作成 Call permutation(aryTemp, aryOut) 'シートへ出力 Cells(ix, 1).Resize(UBound(aryOut, 2) + 1, UBound(aryOut, 1) + 1) = _ WorksheetFunction.Transpose(aryOut) ix = ix + UBound(aryOut, 2) + 1 Next End Sub |
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Call AnalyzeFormula(Target) Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Call AnalyzeHighlight(Target) End Sub |
Option Explicit
'元数式のセル Private Const oRange As String = "B1" 'ネストレベル管理列 Private Const ctlCol As Integer = 1 '関数構文シート Private Const shFunc As String = "関数構文" 'ハイライトカラー Const HightLight1 As Long = XlRgbColor.rgbRed Const HightLight2 As Long = XlRgbColor.rgbBrown '数式解析結果をクリア Public Sub AnalyzeClear() Application.ScreenUpdating = False Dim ws As Worksheet Set ws = ActiveSheet '初期化:途中に空白行があるときも考慮 Dim i1 As Long, i2 As Long i1 = ws.Range(oRange).Offset(1).Row i2 = ws.UsedRange(ws.UsedRange.Count).Row If i2 >= i1 Then ws.Range(ws.Rows(i1), ws.Rows(i2)).Delete End If '元数式のセルの初期設定もここでやっておく '先頭の=と改行があると邪魔なので消しておく Dim strCalc As String strCalc = ws.Range(oRange).Value If Left(strCalc, 1) = "=" Then strCalc = Mid(strCalc, 2) strCalc = Replace(Replace(strCalc, vbLf, ""), vbCr, "") ws.Range(oRange).Value = strCalc '元数式のセルは式全体が見えるように行高調整 Call ColumnFit(ws, ws.Range(oRange)) 'Tagに初期値を入れておく:ControlのTagプロパティの代用 Call setTag(ws.Range(oRange), 1) Application.ScreenUpdating = True End Sub '数式を一気に全解析 Public Sub AnalyzeAllFormula() Application.ScreenUpdating = False Dim ws As Worksheet Set ws = ActiveSheet '元数式の解析だけ最初に行う Call AnalyzeFormula(ws.Range(oRange), False) '解析結果に変化がなくなるまで Dim iLast1 As Long Dim iLast2 As Long Dim myRng1 As Range Dim myRng2 As Range iLast2 = ws.Cells(ws.Rows.Count, ctlCol).End(xlUp).Row Do Until iLast1 = iLast2 iLast1 = iLast2 Set myRng1 = Intersect(ws.UsedRange, _ ws.Range(ws.Rows(ws.Range(oRange).Offset(1).Row), _ ws.Rows(iLast1))) For Each myRng2 In myRng1 '速度アップのためここではじく If InStr(myRng2.Value, "(") > 0 Then Call AnalyzeFormula(myRng2, False) End If Next iLast2 = ws.Cells(ws.Rows.Count, ctlCol).End(xlUp).Row Loop Application.ScreenUpdating = True End Sub '数式解析メイン処理:一番外側の関数のみ解析 Public Sub AnalyzeFormula(ByVal Target As Range, _ Optional ByVal IsExpComp As Boolean = True) '元計算式の場合のみ初期処理 If Target.Item(1).Address = "$B$1" Then Call AnalyzeClear End If '既に展開済み If ExpComp(Target, IsExpComp) Then Exit Sub End If '関数が無ければ無視 Dim strCalc As String strCalc = Target.Item(1).Value If InStr(strCalc, "(") = 0 Then '数式以外の列をクリックしたときの対応 With Target.Worksheet Set Target = .Cells(Target.Row, .Columns.Count).End(xlToLeft) strCalc = Target.Item(1).Value End With End If If InStr(strCalc, "(") = 0 Then 'それでも関数がないときは抜ける Exit Sub End If '入力数式から改行を削除 strCalc = Replace(Replace(strCalc, vbLf, ""), vbCr, "") '分解して配列に Dim i As Integer, j As Integer Dim ix As Integer Dim CntParen As Integer '始まり括弧の数 Dim StartPos As Integer '処理文字位置 '引数を入れる配列:(0)関数名、(1)~引数 'ixが最後の引数のインデックスになります。 'ix+1に関数以降(+○)の数式を入れます。 Dim OutAray(255) As String Dim OutAray2(255) As Integer '文字位置保存用 Dim sTag As String sTag = getTag(Target) StartPos = 1 i = 1 ix = 0 Do Select Case Mid(strCalc, i, 1) Case """" '次の"まで読み飛ばす i = i + 1 Do Until Mid(strCalc, i, 1) = """" i = i + 1 If i >= Len(strCalc) Then MsgBox "数式不正": Exit Sub Loop i = i + 1 Case "'" '次の'まで読み飛ばす i = i + 1 Do Until Mid(strCalc, i, 1) = "'" i = i + 1 If i >= Len(strCalc) Then MsgBox "数式不正": Exit Sub Loop i = i + 1 Case "(" '関数の引数始まり CntParen = CntParen + 1 If CntParen = 1 Then '一番外の関数 '(0)固定で入れる OutAray(0) = Mid(strCalc, StartPos, i - StartPos) StartPos = i + 1 End If i = i + 1 Case "," If CntParen = 1 Then '一番外の関数内の引数 ix = ix + 1 OutAray(ix) = Mid(strCalc, StartPos, i - StartPos) OutAray2(ix) = sTag + StartPos - 1 StartPos = i + 1 End If i = i + 1 Case ")" CntParen = CntParen - 1 If CntParen = 0 Then '一番外の関数が括弧で閉じられた ix = ix + 1 OutAray(ix) = Mid(strCalc, StartPos, i - StartPos) OutAray2(ix) = getTag(Target) + StartPos - 1 StartPos = i + 1 If i < Len(strCalc) Then '関数()の後ろの文字列:ixカウントアップせず OutAray(ix + 1) = Mid(strCalc, StartPos) OutAray2(ix + 1) = getTag(Target) + StartPos - 1 End If '関数が終わったので出力 Call PutArray(Target, OutAray, OutAray2, ix) Exit Do End If i = i + 1 Case Else i = i + 1 End Select '文字列長を超えたら終了 If i > Len(strCalc) Then Exit Do Loop '数値が文字の「エラーを無視する」 Call IgnoreNumberAsText(Target) End Sub '元数式の、選択セルと同一数式部分をハイライト Public Sub AnalyzeHighlight(ByVal Target As Range) Application.ScreenUpdating = False 'シート取得しておく Dim sh As Worksheet Set sh = Target.Worksheet '元数式の文字色を自動に戻す With sh.Range(oRange).Item(1) .Font.ColorIndex = xlAutomatic End With '元数式セルまたはセル値ブランクは無視 On Error Resume Next If Target.Item(1).Address = "$B$1" Or _ Target.Item(1).Value = "" Or _ Target.Count > 1 Then '行・列選択回避 Application.ScreenUpdating = True Exit Sub End If '元数式の当該数式部分をハイライト: Dim strValue As String Dim iPos As Integer Dim i As Integer strValue = Target.Item(1).Value '同じ文字列があれば全てハイライト2:作業セル作りの参考になるので i = 1 Do iPos = InStr(i, sh.Range(oRange).Value, strValue) If iPos = 0 Then Exit Do With sh.Range(oRange).Item(1) .Characters(Start:=iPos, Length:=Len(strValue)).Font.Color = HightLight2 End With i = iPos + 1 Loop '当該数式をハイライト1 i = getTag(Target) iPos = InStr(i, sh.Range(oRange).Value, strValue) If iPos = 0 Then Exit Sub With sh.Range(oRange).Item(1) .Characters(Start:=iPos, Length:=Len(strValue)).Font.Color = HightLight1 End With Application.ScreenUpdating = True End Sub '関数の引数出力 Private Sub PutArray(ByVal Target As Range, _ ByRef OutAray() As String, _ ByRef OutAray2() As Integer, _ ByVal ix As Integer) 'シート取得しておく Dim sh As Worksheet Set sh = Target.Worksheet '配列に入っていない場合は抜ける If ix = 0 Then Exit Sub '括弧で始まっている場合は、括弧をとるだけ If OutAray(0) = "" Then Target = OutAray(1) Exit Sub End If '必要行の追加 sh.Rows(Target.Row + 1).Resize(ix).Insert '挿入行の書式クリア With sh.Rows(Target.Row + 1).Resize(ix) .Clear .NumberFormatLocal = "@" .EntireRow.AutoFit End With Dim i As Integer, j As Integer Dim strFunc As String '「関数構文」より引数名を取得 On Error Resume Next j = WorksheetFunction.Match(GetFuncName(OutAray(0)), _ Worksheets(shFunc).Columns(1), 0) If Err Then j = 1 On Error GoTo 0 '配列(0)は関数名、(1)の引数から出力 For i = 1 To ix With Target.Offset(i) .Value = Worksheets(shFunc).Cells(j, i + 1) If .Value = "" Then .Value = Worksheets(shFunc).Cells(1, i + 1) .Offset(, 1) = OutAray(i) sh.Cells(.Row, ctlCol) = Space(Target.Column) & Target.Column Call setTag(sh.Cells(.Row, ctlCol), OutAray2(i)) End With Next '関数外の数式がある場合 If OutAray(ix + 1) <> "" Then sh.Rows(Target.Offset(ix + 1).Row).Insert With Target.Offset(ix + 1) .Value = "その他" .Offset(, 1) = OutAray(ix + 1) sh.Cells(.Row, ctlCol) = Space(Target.Column) & Target.Column Call setTag(sh.Cells(.Row, ctlCol), OutAray2(ix + 1)) End With End If '処理したセルはBold Target.Font.Bold = True '配列を初期化 ix = 0 ReDim OutArray(100) End Sub '解析結果の表示行の表示・非表示 Private Function ExpComp(ByVal Target As Range, _ Optional ByVal IsExpComp As Boolean = True) As Boolean 'シート取得しておく Dim sh As Worksheet Set sh = Target.Worksheet '行の折り畳み&展開 Dim sRow As Long Dim eRow As Long Dim iLevel As Integer Dim i As Long '自身のネストレベル以上の連続範囲を取得 iLevel = sh.Cells(Target.Row, ctlCol) sRow = Target.Row + 1 i = sRow Do Until sh.Cells(i, ctlCol) <= iLevel Or sh.Cells(i, ctlCol) = "" eRow = i i = i + 1 Loop '自身のネストレベル以上があれば表示・非表示を切り替え If eRow >= sRow Then If Not IsExpComp Then ExpComp = True Exit Function End If sh.Range(sh.Rows(sRow), sh.Rows(eRow)).Hidden = _ Not sh.Range(sh.Rows(sRow), sh.Rows(eRow)).Hidden ExpComp = True Else ExpComp = False End If End Function '演算子等を除いて関数名の抜き出し Private Function GetFuncName(ByVal strFunc As String) As String Dim i As Integer For i = Len(strFunc) To 1 Step -1 '関数は"[A-Z]|." If (Mid(UCase(strFunc), i, 1) >= "A" And _ Mid(UCase(strFunc), i, 1) <= "Z") Or _ Mid(strFunc, i, 1) = "." Then Else strFunc = Mid(strFunc, i + 1) Exit For End If Next GetFuncName = UCase(strFunc) End Function '結合セルの行高の自動調整 Private Sub ColumnFit(ByVal sh As Worksheet, _ ByVal Target As Range) Dim cWidth As Double '列幅:元 Dim tWidth As Double '列幅:結合セル合計 Dim rHeight As Double '行高 Dim mCount As Integer '結合セル数 Dim myRng As Range '現在の状態を保存 mCount = Target.Item(1).MergeArea.Count cWidth = sh.Columns(Target.Column).ColumnWidth For Each myRng In Target.Item(1).MergeArea tWidth = tWidth + sh.Columns(myRng.Column).ColumnWidth Next '結合解除 Target.MergeCells = False '先頭セル幅を結合合計に sh.Columns(Target.Item(1).Column).ColumnWidth = tWidth '行高自動調整 sh.Rows(Target.Item(1).Row).AutoFit '自動調整後の行高保存 rHeight = sh.Rows(Target.Item(1).Row).rowHeight '先頭セル幅を元に戻す sh.Columns(Target.Item(1).Column).ColumnWidth = cWidth '再結合 Target.Item(1).Resize(, mCount).MergeCells = True '調整後の行高に設定 sh.Rows(Target.Item(1).Row).rowHeight = rHeight End Sub '数値が文字の「エラーを無視する」 Private Sub IgnoreNumberAsText(ByVal Target As Range) Dim rng As Range For Each rng In Target.Worksheet.UsedRange If rng.Errors.Item(xlNumberAsText).Value Then rng.Errors(xlNumberAsText).Ignore = True End If Next End Sub 'ControlのTagプロパティの代用として Private Sub setTag(ByVal argRng As Range, ByVal argStr As String) With argRng.Item(1).Offset(, ctlCol - argRng.Column).Validation .Delete .Add xlValidateInputOnly .ErrorMessage = argStr End With End Sub Private Function getTag(ByVal argRng As Range) As Integer Dim strTag As String With argRng.Item(1).Offset(, ctlCol - argRng.Column).Validation strTag = .ErrorMessage End With If strTag = "" Or strTag = "0" Then strTag = 1 getTag = CInt(strTag) End Function |
Option Explicit
Public objFSO As FileSystemObject Public objFolderSub As Folder Public objFile As File Sub SJIStoUTF8() 'フォルダ選択 Dim strDir As String With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = ThisWorkbook.Path .AllowMultiSelect = False .Title = "フォルダの選択" If .Show = False Then Exit Sub strDir = .SelectedItems(1) End With '再帰処理のコール Set objFSO = New FileSystemObject Call GetSubDirFiles(objFSO.GetFolder(strDir)) 'オブジェクトの解放 Set objFSO = Nothing Set objFolderSub = Nothing Set objFile = Nothing 'ステータスバーを消去 Application.StatusBar = False MsgBox "変換完了" End Sub Sub GetSubDirFiles(ByVal objFolder As Folder) Dim objFolderSub As Folder Dim objFile As File 'ステータスバーに処理中のフォルダを表示 Application.StatusBar = objFolder.Path DoEvents 'ファイルの取得 For Each objFile In objFolder.Files Application.StatusBar = objFile.Path DoEvents If objFSO.GetExtensionName(objFile) = " html " Then Call ReadSjisWiteUtf8(objFile.Path) End If Next 'サブフォルダの取得 For Each objFolderSub In objFolder.SubFolders Call GetSubDirFiles(objFolderSub) Next End Sub Sub ReadSjisWiteUtf8(ByVal strFile As String) Dim txtBuf As String Dim byteData() As Byte Dim adoSt As New ADODB.Stream With adoSt 'Shift_JISで読込 .Type = adTypeText .Charset = "Shift_JIS" .Open .LoadFromFile strFile txtBuf = .ReadText .Close 'UTF-8で保存 .Charset = "UTF-8" .Open .WriteText txtBuf, adWriteChar 'BOM削除 .Position = 0 .Type = adTypeBinary .Position = 3 byteData = .Read .Close .Open .Write byteData .SaveToFile strFile, adSaveCreateOverWrite .Close End With Set adoSt = Nothing End Sub |
Option Explicit
'使用例 Sub sample1() Dim ws As Worksheet Dim sFile As String sFile = "csvのフルパス" '出力シート Set ws = ActiveSheet ws.Cells.Clear '以下では全列を文字に設定 '数値も文字としてセルに入ります '文字設定にしなければ数値は数値として入ります。 ws.Cells.NumberFormatLocal = "@" Application.ScreenUpdating = False 'CSV取込 Call CsvToSheet(ws, sFile) 'utf-8決め打ちで読み込む場合は以下で 'Call CsvToSheet(ws, sFile, "utf-8") Application.ScreenUpdating = True End Sub Public Sub CsvToSheet(ByVal ws As Worksheet, _ ByVal strFile As String, _ Optional ByVal CharSet As String = "Auto") Dim myArray() As Variant 'readCsvでCSVを読み込み Dim strRec As String strRec = readCsv(strFile, CharSet) 'CsvToJaggedで行・フィールドに分割してジャグ配列に Dim jagArray() As Variant jagArray = CsvToJagged(strRec) 'JaggedTo2Dでジャグ配列を2次元配列に変換 Call JaggedTo2D(jagArray, myArray) '上記を全てネストすれば以下で書けますが、お勧めはしません。 'Call JaggedTo2D(CsvToJagged(readCsv(strFile, CharSet)), myArray) '2次元配列→シート ws.Range("A1").Resize(UBound(myArray, 1), UBound(myArray, 2)) = myArray End Sub Private Sub JaggedTo2D(ByRef jagArray() As Variant, _ ByRef twoDArray As Variant) 'ジャグ配列の最大列数取得 Dim maxCol As Long, v As Variant maxCol = 0 For Each v In jagArray If UBound(v) > maxCol Then maxCol = UBound(v) End If Next 'ジャグ配列→2次元配列 Dim i1 As Long, i2 As Long ReDim twoDArray(1 To UBound(jagArray), 1 To maxCol) For i1 = 1 To UBound(jagArray) For i2 = 1 To UBound(jagArray(i1)) twoDArray(i1, i2) = jagArray(i1)(i2) Next Next End Sub Private Function CsvToJagged(ByVal strRec As String) As Variant() Dim childArray() As Variant 'ジャグ配列の子配列 Dim lngQuate As Long 'ダブルクォーテーション数 Dim strCell As String '1フィールド文字列 Dim blnCrLf As Boolean '改行判定 Dim i As Long '行位置 Dim j As Long '列位置 Dim k As Long ReDim CsvToJagged(1 To 1) 'ジャグ配列の初期化 ReDim childArray(1 To 1) 'ジャグ配列の子配列の初期化 i = 1 'シートの1行目から出力 j = 0 '列位置はputChildArrayでカウントアップ lngQuate = 0 'ダブルクォーテーションの数 strCell = "" For k = 1 To Len(strRec) Select Case Mid(strRec, k, 1) Case vbLf, vbCr '「"」が偶数なら改行、奇数ならただの文字 If lngQuate Mod 2 = 0 Then blnCrLf = False If k > 1 Then '改行のCrLfはCrで改行判定済なので無視する If Mid(strRec, k - 1, 2) = vbCrLf Then blnCrLf = True End If End If If blnCrLf = False Then Call putChildArray(childArray, j, strCell, lngQuate) 'これが改行となる Call putjagArray(CsvToJagged, childArray, _ i, j, lngQuate, strCell) End If Else strCell = strCell & Mid(strRec, k, 1) End If Case ",", vbTab '「"」が偶数なら区切り、奇数ならただの文字 If lngQuate Mod 2 = 0 Then Call putChildArray(childArray, j, strCell, lngQuate) Else strCell = strCell & Mid(strRec, k, 1) End If Case """" '「"」のカウントをとる lngQuate = lngQuate + 1 strCell = strCell & Mid(strRec, k, 1) Case Else strCell = strCell & Mid(strRec, k, 1) End Select Next '最終行の最終列の処理 If j > 0 And strCell <> "" Then Call putChildArray(childArray, j, strCell, lngQuate) Call putjagArray(CsvToJagged, childArray, _ i, j, lngQuate, strCell) End If End Function Private Sub putjagArray(ByRef jagArray() As Variant, _ ByRef childArray() As Variant, _ ByRef i As Long, _ ByRef j As Long, _ ByRef lngQuate As Long, _ ByRef strCell As String) If i > UBound(jagArray) Then '常に成立するが一応記述 ReDim Preserve jagArray(1 To i) End If jagArray(i) = childArray '子配列をジャグ配列に入れる ReDim childArray(1 To 1) '子配列の初期化 i = i + 1 '列位置 j = 0 '列位置 lngQuate = 0 'ダブルクォーテーション数 strCell = "" '1フィールド文字列 End Sub '1フィールドごとにセルに出力 Private Sub putChildArray(ByRef childArray() As Variant, _ ByRef j As Long, _ ByRef strCell As String, _ ByRef lngQuate As Long) j = j + 1 '「""」を「"」で置換 strCell = Replace(strCell, """""", """") '前後の「"」を削除 If Left(strCell, 1) = """" And Right(strCell, 1) = """" Then If Len(strCell) <= 2 Then strCell = "" Else strCell = Mid(strCell, 2, Len(strCell) - 2) End If End If If j > UBound(childArray) Then ReDim Preserve childArray(1 To j) End If childArray(j) = strCell strCell = "" lngQuate = 0 End Sub '文字コードを自動判別し、全行をCrLf区切りに統一してStringに入れる Private Function readCsv(ByVal strFile As String, _ ByVal CharSet As String) As String Dim objFSO As New FileSystemObject Dim inTS As TextStream Dim adoSt As New ADODB.Stream Dim strRec As String Dim i As Long Dim aryRec() As String If CharSet = "Auto" Then CharSet = getCharSet(strFile) Select Case LCase(CharSet) Case "unicode", "unicodefeff" 'TristateTrueで読込 Set inTS = objFSO.OpenTextFile(strFile, ForReading, , TristateTrue) strRec = inTS.ReadAll inTS.Close Case "utf-8" 'ADOを使って読込、その後の処理を統一するため全レコードをCrLfで結合 Set inTS = objFSO.OpenTextFile(strFile, ForAppending) i = inTS.Line - 1 inTS.Close ReDim aryRec(i) With adoSt .Type = adTypeText .CharSet = "UTF-8" .Open .LoadFromFile strFile i = 0 Do While Not (.EOS) aryRec(i) = .ReadText(adReadLine) i = i + 1 Loop .Close strRec = Join(aryRec, vbCrLf) End With Case Else Set inTS = objFSO.OpenTextFile(strFile, ForReading) strRec = inTS.ReadAll inTS.Close End Select Set inTS = Nothing Set objFSO = Nothing readCsv = strRec End Function '文字コードの自動判別 'UTF-8のBOMなしは文字コードの判別に対応できていません。 Private Function getCharSet(ByVal sFile As String) As String Dim objHtml As MSHTML.HTMLDocument Dim strRec As String 'GetObjectでHTMLDocumentを生成し、文字コードを判定する Set objHtml = GetObject(sFile, "htmlfile") Do While objHtml.readyState <> "complete" DoEvents Loop getCharSet = objHtml.CharSet Set objHtml = Nothing End Function |
'Dictionaryにプロシージャー・プロパティ情報を格納
Public Sub getCodeModule(ByRef dicProcInfo As Dictionary, _ ByVal wb As Workbook, _ ByVal sMod As String) Dim cProcInfo As clsProcInfo Dim sProcName As String Dim sProcKey As String Dim iProcKind As Long Dim i As Long Dim VVC As Object Set VVC = wb.VBProject.VBComponents(sMod).CodeModule i = 1 Do While i <= VVC.CountOfLines sProcName = VVC.ProcOfLine(i, iProcKind) sProcKey = sMod & "." & sProcName If sProcName <> "" Then If isProcLine(VVC.Lines(i, 1), sProcName) Then If Not dicProcInfo.Exists(sProcKey) Then Set cProcInfo = New clsProcInfo cProcInfo.ModName = sMod cProcInfo.ProcName = sProcName cProcInfo.ProcKind = iProcKind cProcInfo.LineNo = i cProcInfo.Comment = getProcComment(i, VVC) cProcInfo.Source = getProcSource(i, VVC) dicProcInfo.Add sProcKey, cProcInfo End If End If End If i = i + 1 Loop End Sub 'プロシージャー・プロパティ定義行かの判定 Private Function isProcLine(ByVal strLine As String, _ ByVal ProcName As String) As Boolean strLine = " " & Trim(strLine) Select Case True Case Left(strLine, 1) = " '" isProcLine = False Case strLine Like "* Sub " & ProcName & "(*" isProcLine = True Case strLine Like "* Sub " & ProcName & " _" isProcLine = True Case strLine Like "* Function " & ProcName & "(*" isProcLine = True Case strLine Like "* Function " & ProcName & " _" isProcLine = True Case strLine Like "* Property * " & ProcName & "(*" isProcLine = True Case strLine Like "* Property * " & ProcName & " _" isProcLine = True Case Else isProcLine = False End Select End Function '継続行( _)全てを連結した文字列で返す Private Function getProcSource(ByRef i As Long, _ ByVal aCodeModule As Object) As String getProcSource = "" Dim sTemp As String Do sTemp = Trim(aCodeModule.Lines(i, 1)) If Right(aCodeModule.Lines(i, 1), 2) = " _" Then sTemp = Left(sTemp, Len(sTemp) - 1) End If getProcSource = getProcSource & sTemp If Right(aCodeModule.Lines(i, 1), 2) <> " _" Then Exit Do i = i + 1 Loop End Function 'プロシージャーの直前のコメントを取得 Private Function getProcComment(ByVal i As Long, _ ByVal aCodeModule As Object) As String getProcComment = "" i = i - 1 Do While Left(aCodeModule.Lines(i, 1), 1) = "'" If getProcComment <> "" Then getProcComment = vbLf & getProcComment getProcComment = aCodeModule.Lines(i, 1) & getProcComment i = i - 1 Loop End Function |
Option Explicit
Public ModName As String Public ProcName As String Public ProcKind As String Public LineNo As Long Public Source As String Public Comment As String Public Property Get ProcKindName() As String Select Case ProcKind Case 0 ProcKindName = "Sub Function" Case 1 ProcKindName = "Property Let" Case 2 ProcKindName = "Property Set" Case 3 ProcKindName = "Property Get" End Select End Property Public Property Get Scope() As String Select Case True Case Trim(Source) Like "Private *" Scope = "Private" Case Trim(Source) Like "Friend *" Scope = "Friend" Case Trim(Source) Like "Static *" Scope = "Static" Case Else Scope = "Public" End Select End Property |
Private Sub sample()
Dim dicProcInfo As New Dictionary Dim wb As Workbook Set wb = ThisWorkbook Dim i As Long 'ブックの全モジュールを処理 With wb.VBProject For i = 1 To .VBComponents.Count Call getCodeModule(dicProcInfo, wb, .VBComponents(i).Name) Next End With 'Dictionaryよりシートに出力 Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("プロシーシャー一覧") Dim v With ws .Cells.Clear .Range("A1:G1").Value = Array("モジュール", "プロシージャー", "スコープ", "種別", "行位置", "ソース", "コメント") i = 2 For Each v In dicProcInfo.Items .Cells(i, 1) = v.ModName .Cells(i, 2) = v.ProcName .Cells(i, 3) = v.Scope .Cells(i, 4) = v.ProcKindName .Cells(i, 5) = v.LineNo .Cells(i, 6) = v.Source .Cells(i, 7) = "'" & v.Comment i = i + 1 Next End With Set dicProcInfo = Nothing End Sub |
'CSVファイルを指定シートに出力
Public Sub CsvToSheet(ByVal ws As Worksheet, _ ByVal strFile As String, _ Optional ByVal CharSet As String = "Auto") Dim myArray() As Variant 'readCsvでCSVを読み込み Dim strRec As String strRec = readCsv(strFile, CharSet) 'CsvToJaggedで行・フィールドに分割してジャグ配列に Dim jagArray() As Variant jagArray = CsvToJagged(strRec) 'JaggedTo2Dでジャグ配列を2次元配列に変換 Call JaggedTo2D(jagArray, myArray) '上記を全てネストすれば以下で書けますが、お勧めはしません。 'Call JaggedTo2D(CsvToJagged(readCsv(strFile, CharSet)), myArray) '2次元配列→シート ws.Range("A1").Resize(UBound(myArray, 1), UBound(myArray, 2)) = myArray End Sub 'ジャグ配列を2次元配列に変換 Private Sub JaggedTo2D(ByRef jagArray() As Variant, _ ByRef twoDArray As Variant) 'ジャグ配列の最大列数取得 Dim maxCol As Long, v As Variant maxCol = 0 For Each v In jagArray If UBound(v) > maxCol Then maxCol = UBound(v) End If Next 'ジャグ配列→2次元配列 Dim i1 As Long, i2 As Long ReDim twoDArray(1 To UBound(jagArray), 1 To maxCol) For i1 = 1 To UBound(jagArray) For i2 = 1 To UBound(jagArray(i1)) twoDArray(i1, i2) = jagArray(i1)(i2) Next Next End Sub Private Function CsvToJagged(ByVal strRec As String) As Variant() Dim childArray() As Variant 'ジャグ配列の子配列 Dim lngQuate As Long 'ダブルクォーテーション数 Dim strCell As String '1フィールド文字列 Dim blnCrLf As Boolean '改行判定 Dim i As Long '行位置 Dim j As Long '列位置 Dim k As Long ReDim CsvToJagged(1 To 1) 'ジャグ配列の初期化 ReDim childArray(1 To 1) 'ジャグ配列の子配列の初期化 i = 1 'シートの1行目から出力 j = 0 '列位置はputChildArrayでカウントアップ lngQuate = 0 'ダブルクォーテーションの数 strCell = "" For k = 1 To Len(strRec) Select Case Mid(strRec, k, 1) Case vbLf, vbCr '「"」が偶数なら改行、奇数ならただの文字 If lngQuate Mod 2 = 0 Then blnCrLf = False If k > 1 Then '改行のCrLfはCrで改行判定済なので無視する If Mid(strRec, k - 1, 2) = vbCrLf Then blnCrLf = True End If End If If blnCrLf = False Then Call putChildArray(childArray, j, strCell, lngQuate) 'これが改行となる Call putjagArray(CsvToJagged, childArray, _ i, j, lngQuate, strCell) End If Else strCell = strCell & Mid(strRec, k, 1) End If Case ",", vbTab '「"」が偶数なら区切り、奇数ならただの文字 If lngQuate Mod 2 = 0 Then Call putChildArray(childArray, j, strCell, lngQuate) Else strCell = strCell & Mid(strRec, k, 1) End If Case """" '「"」のカウントをとる lngQuate = lngQuate + 1 strCell = strCell & Mid(strRec, k, 1) Case Else strCell = strCell & Mid(strRec, k, 1) End Select Next '最終行の最終列の処理 If j > 0 And strCell <> "" Then Call putChildArray(childArray, j, strCell, lngQuate) Call putjagArray(CsvToJagged, childArray, _ i, j, lngQuate, strCell) End If End Function Private Sub putjagArray(ByRef jagArray() As Variant, _ ByRef childArray() As Variant, _ ByRef i As Long, _ ByRef j As Long, _ ByRef lngQuate As Long, _ ByRef strCell As String) If i > UBound(jagArray) Then '常に成立するが一応記述 ReDim Preserve jagArray(1 To i) End If jagArray(i) = childArray '子配列をジャグ配列に入れる ReDim childArray(1 To 1) '子配列の初期化 i = i + 1 '列位置 j = 0 '列位置 lngQuate = 0 'ダブルクォーテーション数 strCell = "" '1フィールド文字列 End Sub '1フィールドごとにセルに出力 Private Sub putChildArray(ByRef childArray() As Variant, _ ByRef j As Long, _ ByRef strCell As String, _ ByRef lngQuate As Long) j = j + 1 '「""」を「"」で置換 strCell = Replace(strCell, """""", """") '前後の「"」を削除 If Left(strCell, 1) = """" And Right(strCell, 1) = """" Then If Len(strCell) <= 2 Then strCell = "" Else strCell = Mid(strCell, 2, Len(strCell) - 2) End If End If If j > UBound(childArray) Then ReDim Preserve childArray(1 To j) End If childArray(j) = strCell strCell = "" lngQuate = 0 End Sub '文字コードを自動判別し、全行をCrLf区切りに統一してStringに入れる Private Function readCsv(ByVal strFile As String, _ ByVal CharSet As String) As String ' Dim objFSO As New FileSystemObject ' Dim inTS As TextStream ' Dim adoSt As New ADODB.Stream Dim objFSO As Object Set objFSO = CreateObject("Scripting.FileSystemObject") Dim inTS As Object Dim adoSt As Object Set adoSt = CreateObject("ADODB.Stream") Dim strRec As String Dim i As Long Dim aryRec() As String If CharSet = "Auto" Then CharSet = getCharSet(strFile) Select Case UCase(CharSet) Case "UTF-8", "UTF-8N" 'ADOを使って読込、その後の処理を統一するため全レコードをCrLfで結合 'Set inTS = objFSO.OpenTextFile(strFile, ForAppending) Set inTS = objFSO.OpenTextFile(strFile, 8) i = inTS.Line - 1 inTS.Close ReDim aryRec(i) With adoSt '.Type = adTypeText .Type = 2 .CharSet = "UTF-8" .Open .LoadFromFile strFile i = 0 Do While Not (.EOS) 'aryRec(i) = .ReadText(adReadLine) aryRec(i) = .ReadText(-2) i = i + 1 Loop .Close strRec = Join(aryRec, vbCrLf) End With Case "UTF-16 LE", "UTF-16 BE" 'Set inTS = objFSO.OpenTextFile(strFile, , , TristateTrue) Set inTS = objFSO.OpenTextFile(strFile, , , -1) strRec = inTS.ReadAll inTS.Close Case "SHIFT_JIS" Set inTS = objFSO.OpenTextFile(strFile) strRec = inTS.ReadAll inTS.Close Case Else 'EUC-JP、UTF-32については未テスト MsgBox "文字コードを確認してください。" & vbLf & CharSet Stop End Select Set inTS = Nothing Set objFSO = Nothing readCsv = strRec End Function '文字コードの自動判別 Private Function getCharSet(strFileName As String) As String Dim bytes() As Byte Dim intFileNo As Integer ReDim bytes(FileLen(strFileName)) intFileNo = FreeFile Open strFileName For Binary As #intFileNo Get #intFileNo, , bytes Close intFileNo 'BOMによる判断 getCharSet = getCharFromBOM(bytes) 'BOMなしをデータの文字コードで判別 If getCharSet = "" Then getCharSet = getCharFromCode(bytes) End If Debug.Print strFileName & " : " & getCharSet End Function 'BOMによる判断 Private Function getCharFromBOM(ByRef bytes() As Byte) As String getCharFromBOM = "" If UBound(bytes) < 3 Then Exit Function Select Case True Case bytes(0) = &HEF And _ bytes(1) = &HBB And _ bytes(2) = &HBF getCharFromBOM = "UTF-8" Exit Function Case bytes(0) = &HFF And _ bytes(1) = &HFE If bytes(2) = &H0 And _ bytes(3) = &H0 Then getCharFromBOM = "UTF-32 LE" Exit Function End If getCharFromBOM = "UTF-16 LE" Exit Function Case bytes(0) = &HFE And _ bytes(1) = &HFF getCharFromBOM = "UTF-16 BE" Exit Function Case bytes(0) = &H0 And _ bytes(1) = &H0 And _ bytes(2) = &HFE And _ bytes(3) = &HFF getCharFromBOM = "UTF-32 BE" Exit Function End Select End Function '以下は下記サイトのコードをVBAに移植 'https://dobon.net/vb/dotnet/string/detectcode.html 'BOMなしをデータの文字コードで判別 Private Function getCharFromCode(ByRef bytes() As Byte) As String Const bEscape As Byte = &H1B Const bAt As Byte = &H40 Const bDollar As Byte = &H24 Const bAnd As Byte = &H26 Const bOpen As Byte = &H28 Const bB As Byte = &H42 Const bD As Byte = &H44 Const bJ As Byte = &H4A Const bI As Byte = &H49 Dim bLen As Long: bLen = UBound(bytes) Dim b1 As Byte, b2 As Byte, b3 As Byte, b4 As Byte Dim isBinary As Boolean: isBinary = False Dim i As Integer For i = 0 To bLen - 1 b1 = bytes(i) If b1 <= &H6 Or b1 = &H7F Or b1 = &HFF Then isBinary = True If b1 = &H0 And i < bLen - 1 And bytes(i + 1) <= &H7F Then getCharFromCode = "Shift_JIS" Exit Function End If End If Next If isBinary Then getCharFromCode = "" Exit Function End If For i = 0 To bLen - 3 b1 = bytes(i) b2 = bytes(i + 1) b3 = bytes(i + 2) If b1 = bEscape Then If b2 = bDollar And b3 = bAt Then getCharFromCode = "Shift_JIS" Exit Function ElseIf b2 = bDollar And b3 = bB Then getCharFromCode = "Shift_JIS" Exit Function ElseIf b2 = bOpen And (b3 = bB Or b3 = bJ) Then getCharFromCode = "Shift_JIS" Exit Function ElseIf b2 = bOpen And b3 = bI Then getCharFromCode = "Shift_JIS" Exit Function End If If i < bLen - 3 Then b4 = bytes(i + 3) If b2 = bDollar And b3 = bOpen And b4 = bD Then getCharFromCode = "Shift_JIS" Exit Function End If If i < bLen - 5 And _ b2 = bAnd And b3 = bAt And b4 = bEscape And _ bytes(i + 4) = bDollar And bytes(i + 5) = bB Then getCharFromCode = "Shift_JIS" Exit Function End If End If End If Next Dim sjis As Integer: sjis = 0 Dim euc As Integer: euc = 0 Dim utf8 As Integer: utf8 = 0 For i = 0 To bLen - 2 b1 = bytes(i) b2 = bytes(i + 1) If ((&H81 <= b1 And b1 <= &H9F) Or (&HE0 <= b1 And b1 <= &HFC)) And _ ((&H40 <= b2 And b2 <= &H7E) Or (&H80 <= b2 And b2 <= &HFC)) Then sjis = sjis + 2 i = i + 1 End If Next For i = 0 To bLen - 2 b1 = bytes(i) b2 = bytes(i + 1) If ((&HA1 <= b1 And b1 <= &HFE) And _ (&HA1 <= b2 And b2 <= &HFE)) Or _ (b1 = &H8E And (&HA1 <= b2 And b2 <= &HDF)) Then euc = euc + 2 i = i + 1 ElseIf i < bLen - 2 Then b3 = bytes(i + 2) If b1 = &H8F And (&HA1 <= b2 And b2 <= &HFE) And _ (&HA1 <= b3 And b3 <= &HFE) Then euc = euc + 3 i = i + 2 End If End If Next For i = 0 To bLen - 2 b1 = bytes(i) b2 = bytes(i + 1) If (&HC0 <= b1 And b1 <= &HDF) And _ (&H80 <= b2 And b2 <= &HBF) Then utf8 = utf8 + 2 i = i + 1 ElseIf i < bLen - 2 Then b3 = bytes(i + 2) If (&HE0 <= b1 And b1 <= &HEF) And _ (&H80 <= b2 And b2 <= &HBF) And _ (&H80 <= b3 And b3 <= &HBF) Then utf8 = utf8 + 3 i = i + 2 End If End If Next Select Case True Case euc > sjis And euc > utf8 getCharFromCode = "EUC-JP" Case utf8 > euc And utf8 > sjis getCharFromCode = "UTF-8N" Case sjis > euc And sjis > utf8 getCharFromCode = "SHIFT-JIS" Case Else '判定できず getCharFromCode = "" End Select End Function |
Sub sample()
Dim ws As Worksheet Dim vFile As Variant vFile = Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv", _ Title:="CSVファイルの選択") If vFile = False Then Exit Sub '出力シート Set ws = ActiveSheet ws.Cells.Clear '全列を文字に設定、数値も文字としてセルに入ります '文字設定にしなければ数値は数値として入ります。 ws.Cells.NumberFormatLocal = "@" 'CSV取込、文字コード自動判別 Application.ScreenUpdating = False Call CsvToSheet(ws, vFile) Application.ScreenUpdating = True End Sub |
'指定セル範囲をセル結合
Sub MergeRange(ByVal aRange As Range) If IsNull(aRange.MergeCells) Or aRange.MergeCells Then If MsgBox("結合セルが含まれています。" & vbLf & _ "続行しますか?" & vbLf & vbLf & _ "続行し場合、結合されているセルの値は失われます。", _ vbYesNo + vbDefaultButton2, "確認") = vbNo Then Exit Sub End If End If Call StoreRange(aRange) Dim isDisplayAlerts As Boolean isDisplayAlerts = Application.DisplayAlerts Application.DisplayAlerts = False aRange.Merge Application.DisplayAlerts = isDisplayAlerts End Sub '指定セル範囲の先頭セルの結合範囲を解除 Sub UnMergeRange(ByVal aRange As Range) Set aRange = aRange.Item(1).MergeArea '先頭セル aRange.UnMerge Call RestoreRange(aRange) Dim wb As Workbook Set wb = aRange.Worksheet.Parent Dim myRange As Range For Each myRange In aRange Call DelCustomDocumentProperties(wb, myRange) Next End Sub '指定セル範囲をCustomDocumentPropertiesに退避 Sub StoreRange(ByVal aRange As Range) Dim myRange As Range For Each myRange In aRange Call AddCustomDocumentProperties(myRange) Next End Sub '指定セル範囲のValueとNumberFormatLocalをCustomDocumentPropertiesから復元 Sub RestoreRange(ByVal aRange As Range) Dim wb As Workbook Set wb = aRange.Worksheet.Parent Dim myRange As Range Dim sAddress As String For Each myRange In aRange If myRange.Address <> aRange.Item(1).Address Then '先頭セルは変更しない sAddress = aRange.Worksheet.Name & "!" & myRange.Address(False, False) myRange.Value = getCustomDocumentProperties(wb, sAddress & "_Value") myRange.NumberFormatLocal = _ getCustomDocumentProperties(wb, sAddress & "_NumberFormatLocal") End If Next End Sub '指定文字列のCustomDocumentPropertiesを取得 Function getCustomDocumentProperties(ByVal wb As Workbook, _ ByVal aProperties As String) As String On Error Resume Next getCustomDocumentProperties = wb.CustomDocumentProperties(aProperties) End Function '指定セルのValueとNumberFormatLocalをCustomDocumentPropertiesへ退避 Sub AddCustomDocumentProperties(ByVal aRange As Range) Dim wb As Workbook Set wb = aRange.Worksheet.Parent Dim dps As DocumentProperties Dim sAddress As String Set dps = wb.CustomDocumentProperties sAddress = aRange.Worksheet.Name & "!" & aRange.Address(False, False) 'CustomDocumentPropertiesから削除 Call DelCustomDocumentProperties(wb, sAddress) 'CustomDocumentPropertiesへ追加 dps.Add sAddress & "_Value", False, msoPropertyTypeString, aRange.Value dps.Add sAddress & "_NumberFormatLocal", False, msoPropertyTypeString, aRange.NumberFormatLocal End Sub '指定セルのCustomDocumentPropertiesを削除 Sub DelCustomDocumentProperties(ByVal wb As Workbook, _ ByVal aAddress As String) Dim dps As DocumentProperties Set dps = wb.CustomDocumentProperties Dim dp As DocumentProperty For Each dp In dps If dp.Name Like aAddress & "_*" Then dp.Delete End If Next End Sub |
Sub セル結合する()
Call MergeRange(Range("D2:D4")) End Sub Sub セル結合を解除() Call UnMergeRange(Range("D2")) End Sub |
Sub 使い方サンプル()
Call CustomDocumentProperties2Sheet(ActiveWorkbook, ActiveSheet) Call AllDelCustomDocumentProperties(ActiveWorkbook) End Sub 'CustomDocumentPropertiesを全削除 Sub AllDelCustomDocumentProperties(ByVal wb As Workbook) Dim dp As DocumentProperty For Each dp In wb.CustomDocumentProperties dp.Delete Next End Sub 'CustomDocumentPropertiesの一覧をシート出力 Sub CustomDocumentProperties2Sheet(ByVal wb As Workbook, _ ByVal ws As Worksheet) Dim dps As DocumentProperties Dim dp As DocumentProperty Dim i As Long 'Valueが定義エラーの場合の対応 On Error Resume Next With ws .Cells.Clear .Range("A1") = "インデックス" .Range("B1") = "プロパティ名" .Range("C1") = "型" .Range("D1") = "値" i = 1 Set dps = wb.CustomDocumentProperties For Each dp In dps .Cells(i + 1, 1) = i .Cells(i + 1, 2).Value = dp.Name .Cells(i + 1, 3).Value = dp.Type .Cells(i + 1, 4).Value = dp.Value i = i + 1 Next End With End Sub |
Option Explicit
Public Declare PtrSafe Function SetTimer Lib "USER32" _ (ByVal hwnd As Long, _ ByVal nIDEvent As Long, _ ByVal uElapse As Long, _ ByVal lpTimerFunc As LongPtr) As Long Public Declare PtrSafe Function KillTimer Lib "USER32" _ (ByVal hwnd As Long, _ ByVal nIDEvent As Long) As Long Public mTimerID As Long Sub TimerStart () If mTimerID <> 0 Then MsgBox "起動済です。" Exit Sub End If mTimerID = SetTimer(0&, 1&, 100&, AddressOf TimerProc) End Sub Sub TimerProc() If mTimerID = 0 Then End '終了できない時の対策 On Error Resume Next 'デバッグ出すとExcelが固まるので If IsNull(Cells.MergeCells) Then Cells.MergeCells = False End If End Sub Sub TimerSto p() Call KillTimer(0&, mTimerID) mTimerID = 0 End Sub |
'ネスト色分けした数式コメント作成
Public Sub setNestColorComment(ByVal argRange As Range) Dim objComment As Comment Set objComment = addFormulaComment(argRange) Call setFontColor(objComment) objComment.Visible = True End Sub '数式コメント作成 Private Function addFormulaComment(ByVal argRange As Range) As Comment Dim objComment As Comment If Not argRange.Comment Is Nothing Then argRange.Comment.Delete End If Set objComment = argRange.AddComment With objComment .Text Text:=argRange.Formula .Shape.TextFrame.Characters.Font.Size = 14 .Shape.TextFrame.Characters.Font.Bold = True .Shape.Top = argRange.Top + 8 .Shape.Left = argRange.Offset(, 1).Left + 8 .Shape.TextFrame.AutoSize = True End With Set addFormulaComment = objComment End Function '数式ネストに段階的に色を設定 Private Sub setFontColor(ByVal obj As Comment) Dim i As Long, ix As Long, lv As Long Dim dQuot As Boolean, sQuot As Boolean lv = 0 For i = 1 To Len(obj.Text) Select Case Mid(obj.Text, i, 1) Case """" 'リテラル"対応 dQuot = Not dQuot Case "'" 'シート名'対応 If Not dQuot Then sQuot = Not sQuot Case "(" If Not (dQuot Or sQuot) Then '閉じ括弧の位置を取得 ix = getPairIndex(obj.Text, i) '閉じ括弧があり、引数が入っている場合 If ix > 0 And ix > i + 1 Then lv = lv + 1 obj.Shape.TextFrame.Characters( _ Start:=i + 1, _ Length:=ix - i - 1).Font.Color _ = getNestColor(lv) End If End If Case ")" If Not (dQuot Or sQuot) Then lv = lv - 1 End Select Next End Sub '開き(に対する閉じ)の文字位置を返す Private Function getPairIndex(ByVal aString As String, _ ByVal aStart As Long) As Long Dim i As Long, cnt As Long Dim dQuot As Boolean, sQuot As Boolean For i = aStart + 1 To Len(aString) Select Case Mid(aString, i, 1) Case """" 'リテラル"対応 dQuot = Not dQuot Case "'" 'シート名'対応 If Not dQuot Then sQuot = Not sQuot Case "(" If Not (dQuot Or sQuot) Then cnt = cnt + 1 Case ")" If Not (dQuot Or sQuot) Then If cnt = 0 Then getPairIndex = i Exit Function End If cnt = cnt - 1 End If End Select Next End Function '数式のネストの深さにより色を返す Private Function getNestColor(ByVal lv As Long) As Long Dim aryColor aryColor = Array(vbBlack, vbBlue, vbMagenta, vbGreen, rgbBrown, vbCyan, vbRed) '7進にして色を繰り返すrgbDarkKhaki getNestColor = aryColor(lv Mod 7) End Function |
Sub SampleMain()
'アクティブシートの数式が入っているセルを取得 Dim formulaRange As Range On Error Resume Next '数式のセル全部、もちろん特定セルでも良い Set formulaRange = Cells.SpecialCells(xlCellTypeFormulas) If Err Then Exit Sub On Error GoTo 0 Application.ScreenUpdating = False '最小化されているとCommentが正しく取得できないので Dim winState As Long winState = Application.WindowState Application.WindowState = xlNormal 'xlMaximizedでも良い '数式が入っている全セルに数式コメントを作成 Dim myRange As Range For Each myRange In formulaRange Call setNestColorComment(myRange) Next Application.WindowState = winState Application.ScreenUpdating = True End Sub |