ExcelVBA Excel VBA Microsoft Excel VBA Visual Basic for Applications Excel Excel VBA ExcelVBA Excel ExcelVBA Excel VBA Excel ExcelVBA ExcelVBA VBA Ran

Size: px

Start display at page:

Download "ExcelVBA Excel VBA Microsoft Excel VBA Visual Basic for Applications Excel Excel VBA ExcelVBA Excel ExcelVBA Excel VBA Excel ExcelVBA ExcelVBA VBA Ran"

ともあきあわび
5 years ago
Views:

1 Excel VBA Microsoft Excel VBA Visual Basic for ApplicationsExcel Excel VBA Excel ExcelVBA Excel VBA Excel ExcelVBA VBA Range Excel

2 174 VBA VisualBasicforApplications Microsoft ExcelWordAccessPowerPoint VBA VBA VBEVisual BasicEditor VBE ExcelVBA Excel VBA MSDN MicrosoftDeveloperNetwork VBA Microsoft VBE MSDN ExcelVBAMSDN Range Excel VBAMSDN

3 175 VBA VBA ObjectPropertyProperty ObjectProperty ObjectMethod ObjectExcel PropertyMethod. ObjectPropertyProperty RangeAValue A RangeAValue Range ApplicationActiveWorkbookActiveSheetRangeAValue Excel VBA OfficeTANAKAhttp://officetanaka.net/ VBA VBAExcelVBAVBA VBAWorksheet ExcelVBA RangeA

4 176 Object Application Excel Excel ActiveWorkbook Workbook ActiveSheet WorkSheet Range Range Value ApplicationActiveWorkbook WorkbookActiveSheet WorkSheetRangeA RangeAValue ObjectProperty xrangeavalue Application Excel Application Application Application MSDN

5 177 Ax VBA Excel Application ObjectMethod Worksheets.Add RangeAValue Worksheets WorksheetsWorksheetsSheet WorksheetsDelete WorksheetsSelect

6 178 Add Worksheet WorkSheetsAddAfterWorksheets Add Worksheet NWs SetNWsWorksheetsAddAfterWorksheets ExcelVBA VBE FApplication ExcelActiveWorkbook PropertyActiveWorkbookAsWorkbook ActiveWorkbook Workbook Set Add VBA MsgBox

7 179 Excel ApplicationExcel WorkbookWorksheetRange Application Application MSDNMicrosoft Developer NetworkVBA VBA ExcelVBA MSDN MSDN

8 180 InputBox Application Application InputBox WorksheetFunction ExcelVBA Excel Range Cells InputBox Workbooks Workbooks Workbook Worksheets Add Worksheet Worksheets Worksheet Excel Range MSDN Excel VBA Range Application VBA InputBox InputBox InputBox Sum AAB RangeBValueWorksheetFunctionSumRangeAA ActiveSheet Object Activesheet Worksheet Chart Chart Charts Charts Worksheets Sheets Sheets InputBox Variant VBA

9 181 OffsetResize Excel Borders RangeACBordersLineStylexlContinuous AC BorderAround RangeACBorderAroundWeightxlMedium AC MSDNMicrosoft Developer Network Dim OptionExplicit Dim Sample VBE

10 182 Byte Boolean True False Integer Long Single Double Currency Date String Variant Object Object Dim As DimiAsInteger i VBA Range ij Dim ij AsInteger ij ji Dim i AsIntegerj AsInteger Dim As

11 183 SampleRange r ACr r Sub Sample Dim rasrange Set rrangeac MsgBox raddress EndSub Range Set OfficeVBA MSDN Variant Variant Integer Variant Variant Integer OfficeOfficeVBA MSDN

184 Sub Sample Dim abcdef Variant a b Long c Single d Double etest Range frangeac MsgBox a &TypeNamea&vbCrLf _ b &TypeNameb&vbCrLf&_ c &TypeNamec&vbCrLf&_ d

12 184 Sub Sample Dim abcdef Variant a b Long c Single d Double etest Range frangeac MsgBox a &TypeNamea&vbCrLf _ b &TypeNameb&vbCrLf&_ c &TypeNamec&vbCrLf&_ d &TypeNamed&vbCrLf&_ e &TypeNamee&vbCrLf&_ f &TypeNamef& &VarTypef EndSub SampleSample Range Office VBA Range VarType Variant MSDN VarType TypeName Variant Sample Range VariantMSDN InputBox

13 185 Excel ExcelVBA Range Cells InputBox InputBox InputBox ExcelVBA InputBox ApplicationExcel VBA InputBox Excel InputBox InputBox Type Type InputBox Excel Application

14 186 ExcelVBA MSDNInputBox ApplicationInputBox Prompt Title Type ApplicationInputBoxPromptTitle...Type Type Prompt Title Variant Type Variant String ExcelVBA MSDN Type InputBox SampleInputBox OK InputBox r Type OKTypeType Type

15 187 Sub Sample Dim rasrange DimmyTitleAsStringmyMsg AsString Dim casrange Dim i AsInteger mytitletest mymsg SetrApplicationInputBoxmyMsgmyTitleType i For Each cin r cvaluei ii Next c EndSub Range Sample ACEE r ACEESample r index ForEach Next

16 188 InputBox Type TypeType InputBox Variant ExcelVBA MSDNOK OKInputBox InputBox False Sub Sample Dim aasvariant or aapplicationinputboxtesttype IfTypeNameaBooleanThenExitSub MsgBox TypeNamea EndSub Boolean VBA Type adouble Type String Application afalse TypeFalse

17 Type 189 SampleType a Variant Range Set Sub Sample Dim aasrange Set aapplicationinputboxtesttype IfTypeNameaBooleanThenExitSub MsgBox TypeNamea End Sub arange Input aa Type VBA Sample Moug

18 190 Sub Sample Dim aasrange On Error ResumeNext Set aapplicationinputboxtesttype On ErrorGoTo IfTypeNameaBooleanThenExitSub EndSub OnErrorResumeNext Array VBA Variant VBA Array Range Samplea TestRangeACBoolean False arange On ErrorGoTo On Error ResumeNext Moug Array VBA. OptionBase Moug Sample

191 Sub Sample Dim aasvariant avbaarraytestrangeacfalse MsgBox a&typenamea&vbcrlf&_ a&typenamea&vbcrlf&_ a&typenamea EndSub TypeArray Input Sample Sub Sample Dim aasvariant Dim

19 191 Sub Sample Dim aasvariant avbaarraytestrangeacfalse MsgBox a&typenamea&vbcrlf&_ a&typenamea&vbcrlf&_ a&typenamea EndSub TypeArray Input Sample Sub Sample Dim aasvariant Dim DataAsRange avbaarrayapplicationinputboxtesttype IfTypeNameaBooleanThenExitSub Set Dataa MsgBox DataValue EndSub SampleInputBox a ainputbox arange aboolean False arange Range Data

20 192 Range BCSampleInputBox BC CBCRange Data Data Dataindexindex Range Cells Range Cells Range Cells RangeC Cells RangeAC RangeCellsCells indexindexcells Range ACRange RangeACCells RangeCellsCells Cells

21 193 indexindex Cells CellsClear Range Offset Resize Offset Range Range RangeBOffset RangeBOffset RangeFGOffset Offset With End With Resize Range RangeAResize RangeDEResize

22 194 Resize Range InputBox Resize mymsg avbaarrayapplicationinputboxmymsgtype IfTypeNameaBooleanThenExitSub SetTableaResize Range Table Function Function F_Check SetTableaOffset Function Function~End Function Function Sub Function OfficeVBA MSDN

195 Function F_CheckmyTitleAsStringTargetAsRangeAsInteger F_CheckvbYes F_CheckvbNo Dim k AsLong DimiAsLong Dim mymsg AsString F_CheckvbYes TargetWorksheetSelect TargetSelect k ForiToTargetCount

23 195 Function F_CheckmyTitleAsStringTargetAsRangeAsInteger F_CheckvbYes F_CheckvbNo Dim k AsLong DimiAsLong Dim mymsg AsString F_CheckvbYes TargetWorksheetSelect TargetSelect k ForiToTargetCount IfTargetiThenkk Nexti IfkTargetCountThen mymsg F_CheckMsgBoxmyMsgvbYesNomyTitle EndIf EndFunction mytitlestring TargetRange Integer F_Check Target vbyes vbno F_CheckvbYes VBA MsgBox F_Check Targetk VBA MsgBox YvbYes NvbNo

24 196 VBA A ExcelVBA WorksheetsRangeAvalue Select A WorksheetsSelect RangeASlelect WorksheetsRangeASlelect MsgBox MsgBox Range SpecialCells SpecialCells SpecialCells Select Activate Range Activate

25 197 Target.Worksheet Range Worksheet Target Range Target Targetindex index Targetindex index Function F_Check InputBox Range r Sub Sample Dim rasrange Dim mymsg AsString DimmyTitleAsString mytitlesample mymsg Set rapplicationinputboxmymsgmytitletype IfF_CheckmyTitlervbNo ThenExitSub MsgBox rclear EndSub Worksheet InputBox

26 198 rfunction F_Check vbno vbyes Function Sub Dataij InputBox Range Tablekl InputBox Range Resize DataijTablekl Function Function Sub Sample Function F_InputF_TableF_ Check

27 199 Main Data F_Input Table F_Table Data Table F_InputmyMsg AsStringmyTitleAsStringAsRange F_TablemyTitleAsStringNrAsIntegerNcAsIntegerAsRange Range NrNc F_Input F_CheckmyTitleAsStringTargetAsRange F_CheckmyTitleAsStringTargetAsRangeAsInteger Application

28 200 Range Range ExcelVBA TypeInput SampleRange Resize Sample ExcelVBA ExcelVBA Excel VBA http: // OfficeTANAKA Moug MSDNMicrosoftDeveloperNetworkhttps://msdn.microsoft.com/ja-jp/

29 201 VBE SubF xlsm Sub EndSub Sub EndSub Sub SubRange VBE VisualBasicEditor Excel VisualBasic AltF VBE VBE OptionExplicit VBE F CtrlG

30 202 MSDN Application Excel MSDN ActiveCell Range Range ActiveSheet Object Nothing ActiveWorkbook Workbook Workbook Nothing Cells Range Range Range Range Range Workbooks Workbooks Workbooks WorksheetFunction WorksheetFunction WorksheetFunction Application Sheets Workbook Worksheets Sheets Sheets Sheets MSDN InputBox Variant MSDN Worksheets Worksheet Worksheet MSDN Count Long Long Add MSDN Worksheet

31 203 MSDN Worksheet MSDN Cells Range Range Columns Range Range Name String String Range Range Range Rows Range Range Range Shapes Shapes Shapes MSDN Activate Copy Delete Paste PasteSpecial Select

32 204 MSDN Range MSDN Address Borders String Borders String Borders Cells Range Range Columns Range Range ColumnWidth Count CurrentRegion End EntireColumn EntireRow Font Formula FormulaRC Hidden Interior Name NumberFormat Offset Variant Variant Long Long Range Range EndRange Range Range Font A Variant RC Variant Variant Interior Variant Variant Range Range Range Range Range Font Variant Variant Variant Interior Variant Variant Range Range Range Range Resize RowHeiget Rows Value Range Variant Range Range Variant Range Variant Range Variant

33 205 MSDN Activate Select AutoFill Autofit ColorLineStyle BorderAround Weight Variant Clear ClearCountents ClearFormats Copy Cut Delete Insert PasteSpecial Range Select SpecialCells Range Range Range MSDN RangeBorders Range Border MSDN LineStyle Variant Weight XlBorderWeight Variant

34 SubSample DimDataAsRange DimTableAsRange DimmyTitleAsString DimmyMsg AsString mytitle mymsg SetDataF_InputmyMsgmyTitle IfDataIsNothingThenExitSub SetTableF_TablemyTitle IfTableIsNothingThenExitSub MsgBox &DataAddress&vbCrLf&_ &TableAddress EndSub FunctionF_InputmyMsgAsStringmyTitleAsStringAsRange DimaAsVariant avbaarrayapplicationinputboxmymsgmytitletype IfTypeNameaBooleanThenExitFunction SetF_Inputa EndFunction FunctionF_TablemyTitleAsStringNrAsLongNcAsLongAsRange Nr Nc DimrAsRange DimmyMsg AsString s mymsg SetrF_InputmyMsgmyTitle 206

35 IfrIsNothingThenExitFunction SetF_TablerResizeNrNc IfF_CheckmyTitleF_TablevbNoThenGoTos F_TableClear EndFunction FunctionF_CheckmyTitleAsStringTargetAsRangeAsInteger F_CheckvbYes F_CheckvbNo DimkAsLong DimiAsLong DimmyMsg AsString F_CheckvbYes TargetWorksheetSelect TargetSelect k ForiToTargetCount IfTargeti Thenkk Nexti IfkTargetCountThen mymsg F_CheckMsgBoxmyMsgvbYesNomyTitle EndIf EndFunction 207

94 expression True False expression FalseMSDN IsNumber WorksheetFunctionIsNumberexpression expression True Office support.office.com/ja-jp/ S

94 expression True False expression FalseMSDN IsNumber WorksheetFunctionIsNumberexpression expression True Office support.office.com/ja-jp/ S Excel VBA a Excel VBA VBA IsNumeric IsNumber SpecialCells SpecialCells MSDNMicrosoft Developer NetworkIsNumeric IsNumber SpecialCells IsNumeric VBA IsNumericexpression SpecialCells 94 expression True False