Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

10 Aralık 2005 Cumartesi

Add Method


'1) As it applies to the AddIns object.

'This example inserts the add-in Myaddin.xla from drive A. When you run this example, Microsoft Excel copies the file A:\Myaddin.xla to the Library folder on your hard disk and adds the add-in title to the list in the Add-Ins dialog box.
Sub UseAddIn()


Set myAddIn = AddIns.Add(Filename:="A:\MYADDIN.XLA", CopyFile:=True)
MsgBox myAddIn.Title & " has been added to the list"

End Sub

'2) As it applies to the AllowEditRanges object.

'This example allows edits to range "A1:A4" on the active worksheet, notifies the user, then changes the password for this specified range and notifies the user of this change.

Sub UseChangePassword()

Dim wksOne As Worksheet
Set wksOne = Application.ActiveSheet
' Protect the worksheet.

wksOne.Protect
'
Establish a range that can allow edits
' on the protected worksheet.
wksOne.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("A1:A4"), Password:="secret"
MsgBox "Cells A1 to A4 can be edited on the protected worksheet."
'
Change the password.

wksOne.Protection.AllowEditRanges(1).ChangePassword Password:="moresecret"
MsgBox "The password for these cells has been changed."

End Sub

'3) As it applies to the CalculatedFields object.

'This example adds a calculated field to the first PivotTable report on worksheet one.
Worksheets(1).PivotTables(1).CalculatedFields.Add "PxS", "= Product * Sales"


'4) As it applies to the CalculatedMembers object.

'The following example adds a set to a PivotTable, assuming a PivotTable exists on the active worksheet.

Sub UseAddSet()

Dim pvtOne As PivotTable
Dim strAdd As String
Dim strFormula As String
Dim cbfOne As CubeField
Set pvtOne = ActiveSheet.PivotTables(1)
strAdd = "[MySet]"
strFormula = "'{[Product].[All Products].[Food].children}'"
' Establish connection with data source if necessary.
If Not pvtOne.PivotCache.IsConnected Then pvtOne.PivotCache.MakeConnection
' Add a calculated member titled "[MySet]"
pvtOne.CalculatedMembers.Add Name:=strAdd, Formula:=strFormula, Type:=xlCalculatedSet
' Add a set to the CubeField object.
Set cbfOne = pvtOne.CubeFields.AddSet(Name:="[MySet]", Caption:="My Set")

End Sub

'5) As it applies to the ChartObjects object.

'This example creates a new embedded chart..

Set co = Sheets("Sheet1").ChartObjects.Add(50, 40, 200, 100)
co.Chart.ChartWizard Source:=Worksheets("Sheet1").Range("A1:B2"), Gallery:=xlColumn, Format:=6, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=0, HasLegend:=1


'6) As it applies to the Charts object.

'This example creates an empty chart sheet and inserts it before the last worksheet.

ActiveWorkbook.Charts.Add Before:=Worksheets(Worksheets.Count)

'7) As it applies to the CustomProperties object.

'This example adds identifier information to the active worksheet and returns the name and value to the user.

Sub CheckCustomProperties()

Dim wksSheet1 As Worksheet
Set wksSheet1 = Application.ActiveSheet
' Add metadata to worksheet.
wksSheet1.CustomProperties.Add Name:="Market", Value:="Nasdaq"
' Display metadata.
With wksSheet1.CustomProperties.Item(1)

MsgBox .Name & vbTab & .Value

End With

End Sub

'8) As it applies to the CustomViews object.

'This example creates a new custom view named "Summary" in the active workbook.

ActiveWorkbook.CustomViews.Add "Summary", True, True

'9) As it applies to the FormatConditions object.

'This example adds a conditional format to cells E1:E10.

With Worksheets(1)

.Range("e1:e10").FormatConditions.Add(xlCellValue, xlGreater, "=$a$1")

With .Borders

.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 6

End With
With .Font


.Bold = True
.ColorIndex = 3

End With

End With

'10) As it applies to the HPageBreaks object.

'This example adds a horizontal page break above cell F25 and adds a vertical page break to the left of this cell.

With Worksheets(1)

.HPageBreaks.Add .Range("F25")
.VPageBreaks.Add .Range("F25")

End With

'11) As it applies to the Hyperlinks object.

'This example adds a hyperlink to cell A5.

With Worksheets(1)

.Hyperlinks.Add Anchor:=.Range("a5"), Address:="http://example.microsoft.com", ScreenTip:="Microsoft Web Site", TextToDisplay:="Microsoft"

End With
'This example adds an email hyperlink to cell A5.
With Worksheets(1)


.Hyperlinks.Add Anchor:=.Range("a5"), Address:="mailto:someone@microsoft.com?subject=hello", ScreenTip:="Write us today", TextToDisplay:="Support"

End With

'12) As it applies to the ListColumns collection object.

'The following example adds a new column to the default ListObject object in the first worksheet of the workbook. Because no position is specified, a new rightmost column is added.
Set myNewColumn = ActiveWorkbook.Worksheets(1).ListObjects(1).ListColumns.Add
Note A name for the column is automatically generated. You can choose to change the name after the column has been added.


'13) As it applies to the ListObjects collection object.

'The following example adds a new ListObject object based on data from a Microsoft Windows SharePoint Services site to the default ListObjects collection and places the list in cell A1 in the first worksheet of the workbook.
Note The following code example assumes that you will substitute a valid server name and the list guid in the variables strServerName and strListGUID. Additionally, the server name must be followed by "/_vti_bin" or the sample will not work.


Set objListObject = ActiveWorkbook.Worksheets(1).ListObjects.Add(SourceType:= xlSrcExternal, Source:= Array(strServerName, StrListGUID), TRUE, XlGuess, Destination:= Range("A1")
Note If there is existing data at cell A1, the existing list data will be moved to the right to accommodate the new list.


'14) As it applies to the ListRows collection object.

'The following example adds a new row to the default ListObject object in the first worksheet of the workbook. Because no position is specified, the new row is added to the bottom of the list.

Set myNewColumn = ActiveWorkbook.Worksheets(1).ListObject(1).ListRows.Add

'15) As it applies to the Names object.

'This example defines a new name for the range A1:D3 on Sheet1 in the active workbook. Note Nothing is returned if Sheet1 does not exist.

Sub MakeRange()

ActiveWorkbook.Names.Add Name:="tempRange", RefersTo:="=Sheet1!$A$1:$D$3"

End Sub

'16) As it applies to the OLEObjects object.

'This example creates a new Microsoft Word OLE object on Sheet1.

ActiveWorkbook.Worksheets("Sheet1").OLEObjects.Add ClassType:="Word.Document"
'This example adds a command button to sheet one.


Worksheets(1).OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, Width:=150, Height:=10

'17) As it applies to the Parameters object.

'This example changes the SQL statement for query table one. The clause "(city=?)" indicates that the query is a parameter query, and the value of city is set to the constant "Oakland."

Set qt = Sheets("sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", xlParamTypeVarChar)
param1.SetParam xlConstant, "Oakland"
qt.Refresh


'18) As it applies to the Phonetics object.

'This example adds three phonetic text strings to the active cell. The example then sets the character type to Hiragana, sets the font color to blue, and sets the text to visible.

ActiveCell.FormulaR1C1 = ""
ActiveCell.Phonetics.Add Start:=1, Length:=3, Text:=""
ActiveCell.Phonetics.Add Start:=4, Length:=3, Text:=""
ActiveCell.Phonetics.CharacterType = xlHiragana
ActiveCell.Phonetics.Font.Color = vbBlue
ActiveCell.Phonetics.Visible = True


'19) As it applies to the PivotCaches object.

'This example creates a new PivotTable cache based on an OLAP provider and then it creates a new PivotTable report based on the cache, at cell A3 on the active worksheet.

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
' Open the connection.


Set cnnConn = New ADODB.Connection
With cnnConn


.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\perfdate\record.mdb"

End With
' Set the command text.


Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand


.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute

End With
' Open the recordset.


Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
' Create a PivotTable cache and report.


Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache


.CreatePivotTable TableDestination:=Range("A3"), TableName:="Performance"

End With
With ActiveSheet.PivotTables("Performance")


.SmallGrid = False
With .PivotFields("Pressure")

.Orientation = xlRowField
.Position = 1

End With
With .PivotFields("Speed")

.Orientation = xlColumnField
.Position = 1

End With
With .PivotFields("Time")

.Orientation = xlDataField
.Position = 1

End With

End With
' Close the connections and clean up.


cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing


'20) As it applies to the PivotFormulas object.

'This example creates a new PivotTable formula for the first PivotTable report on worksheet one.

Worksheets(1).PivotTables(1).PivotFormulas.Add "Year['1998'] Apples = (Year['1997'] Apples) * 2"

'21) As it applies to the PivotItems object.

'This example creates a new PivotTable item in the first PivotTable report on worksheet one.

Worksheets(1).PivotTables(1).PivotItems("Year").Add "1998"

'22) As it applies to the PivotTables object.

'This example creates a new PivotTable cache based on an OLAP provider, and then it creates a new PivotTable report based on the cache, at cell A1 on the first worksheet.

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
' Open the connection.


Set cnnConn = New ADODB.Connection
With cnnConn


.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\perfdate\record.mdb"

End With
' Set the command text.Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand


.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute

End With
' Open the recordset.


Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
' Create PivotTable cache and report.


Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
ActiveSheet.PivotTables.Add PivotCache:=objPivotCache, TableDestination:=Range("A3"), TableName:="Performance"
With ActiveSheet


.PivotTables("Performance")

.SmallGrid = False
With .PivotFields("Pressure")

.Orientation = xlRowField
.Position = 1

End With
With .PivotFields("Speed")


.Orientation = xlColumnField
.Position = 1

End With
With .PivotFields("Time")

.Orientation = xlDataField
.Position = 1

End With

End With
' Close the connections and clean up.cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing


'23) As it applies to the PublishObjects object.

'This example saves the range D5:D9 on the First Quarter worksheet in the active workbook to a Web page called Stockreport.htm. You use the Spreadsheet component to add interactivity to the Web page.

ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceRange, Filename:="\\Server2\Q1\Stockreport.htm", Sheet:="First Quarter", Source:="D5:D9", HtmlType:=xlHtmlCalc).Publish

'24) As it applies to the QueryTables object.

'This example creates a query table based on an ADO recordset. The example preserves the existing column sorting and filtering settings and layout information for backward compatibility.

Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Set cnnConnect = New ADODB.Connection
cnnConnect.Open "Provider=SQLOLEDB;" & "Data Source=srvdata;" & "User ID=testac;Password=4me2no;"
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open Source:="Select Name, Quantity, Price From Products", ActiveConnection:=cnnConnect, CursorType:=adOpenDynamic, LockType:=adLockReadOnly, Options:=adCmdText
With ActiveSheet


.QueryTables.Add(Connection:=rstRecordset, Destination:=Range("A1"))

.Name = "Contact List"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With
'This example imports a fixed width text file into a new query table. The first column in the text file is five characters wide and is imported as text. The second column is four characters wide and is skipped. The remainder of the text file is imported into the third column and has the General format applied to it.


Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables.Add(Connection:="TEXT;C:\My Documents\19980331.txt", Destination:=shFirstQtr.Cells(1, 1))
With qtQtrResults


.TextFileParsingType = xlFixedWidth
.TextFileFixedColumnWidths := Array(5,4)
.TextFileColumnDataTypes:= Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
.Refresh

End With
'This example creates a new query table on the active worksheet.

sqlstring = "select 96Sales.totals from 96Sales where profit < connstring = "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales">

.Refresh

End With

'25) As it applies to the RecentFiles object.

'This example adds Oscar.xls to the list of recently used files.

Application.RecentFiles.Add Name:="Oscar.xls"

'26) As it applies to the Scenarios object.

'This example adds a new scenario to Sheet1.

Worksheets("Sheet1").Scenarios.Add Name:="Best Case", ChangingCells:=Worksheets("Sheet1").Range("A1:A4"), Values:=Array(23, 5, 6, 21), Comment:="Most favorable outcome."

'27) As it applies to the SeriesCollection object.

'This example creates a new series in Chart1. The data source for the new series is range B1:B10 on Sheet1.

Charts("Chart1").SeriesCollection.Add Source:=ActiveWorkbook.Worksheets("Sheet1").Range("B1:B10")
'This example creates a new series on the embedded chart on Sheet1.


Worksheets("Sheet1").ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add Source:=Worksheets("Sheet1").Range("B1:B10")


'28) As it applies to the Sheets and WorkSheets objects.

'This example inserts a new worksheet before the last worksheet in the active workbook.ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)

'29) As it applies to the SmartTags object.

'This example adds a smart tag titled MSFT to cell A1, then adds extra metadata called Market with the value of Nasdaq to the smart tag and then returns the value of the property to the user. This example assumes the host system is connected to the Internet.

Sub UseProperties()

Dim strLink As String
Dim strType As String
' Define smart tag variables.strLink = "urn:schemas-microsoft-com:smarttags#stocktickerSymbol"
strType = "stockview"
Range("A1").Formula = "MSFT"
' Add a property for MSFT smart tag and define its value.Range("A1").SmartTags.Add(strLink).Properties.Add Name:="Market", Value:="Nasdaq"
' Notify the user of the smart tag's value.

MsgBox Range("A1").SmartTags.Add(strLink).Properties("Market").Value

End Sub

'30) As it applies to the Styles object.

'This example defines a new style based on cell A1 on Sheet1.

With ActiveWorkbook

.Styles.Add(Name:="theNewStyle")

.IncludeNumber = False
.IncludeFont = True
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
.Font.Name = "Arial"
.Font.Size = 18

End With

'31) As it applies to the Trendlines object.

'This example creates a new linear trendline in Chart1.

ActiveWorkbook.Charts("Chart1").SeriesCollection(1).Trendlines.Add

'32) As it applies to the Validation object.

'This example adds data validation to cell E5.

With Range("e5")

.Validation

.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"

End With

'33) As it applies to the VPageBreaks object.

'This example adds a horizontal page break above cell F25 and adds a vertical page break to the left of this cell.

With Worksheets(1)

.HPageBreaks.Add .Range("F25")
.VPageBreaks.Add .Range("F25")

End With

'34) As it applies to the Watches object.

'This example creates a summation formula in cell A3 and then adds this cell to the watch facility.

Sub AddWatch()
With Application

.Range("A1").Formula = 1
.Range("A2").Formula = 2
.Range("A3").Formula = "=Sum(A1:A2)"
.Range("A3").Select
.Watches.Add Source:=ActiveCell

End With
End Sub

'35) As it applies to the WorkBooks object.

'This example creates a new workbook.

Workbooks.Add

Hiç yorum yok:

Blog Arşivi

Bu gadget'ta bir hata oluştu

Bu Blogda Ara

Contributor

Contributor
Mustafa ULUSARAÇ İstanbul, TÜRKİYE
free counters
T. C. Central Bank Indicative Exchange Rates
Currency Exchange Rate Widget,Currency Converter Widget
Borsa İstanbul