EXCEL VBA初步(5)

Exit For End If Next n

t = Mid(temp, s, e - s + 1) getnumberplusB = Val(t) End Function

在EXCEL中使用VBA来筛选数据 求解重复行的EXCEL表格 Vba程序

Public Sub seelctdouble() Dim i As Long, j As Long For i = 1 To 7 Step 1 For j = 1 To 7 Step 1 If i <> j Then

If Range(\Range(\End If End If Next j Next i End Sub 使用数组

Public Sub selectdouble2() Dim i As Long, j As Long Dim max As Long

Dim a() As xtring, b() As String max = 10000

ReDim a(max) As String ReDim b(max) As Long For i = 1 To max Step 1

a(i) = Range(\Next i

For i = 1 To max Step 1 For j = 1 To max Step 1 If i <> j Then If a(i) = a(j) Then b(i) = 1 End If End If Next j Next i

For i = 1 To max Step 1

Range(\ Next End Sub

数组赋值

a = Range(\Range(\使用工作表函数

Public Sub selectdouble3() Dim i As Long, j As Long, a, b For i = 2 To 9999 Step 1

a = Application.VLookup(Range(\

b = Application.VLookup(Range(\If IsError(a) And IsError(b) Then Range(\End If Next i End Sub 改进算法

Public Sub selectdouble4() Dim i As Long, max As Long max = 10000 i = 1 Do

If Range(\ Range(\

Range(\ i = i + 2 Else i = i + 1 End If 数据系列 Loop While i < max seriescollection End Sub EXCEL图表 嵌入式图表chartobject 图标工作表chart 图表标题charttitle 100 80 6040200第一季度第三季度绘图区域plotarea 图标区 chartarea 坐标轴 axes 东部西部北部图例legend 创建EXCEL图表 创建图表工作表

Public Sub addchartsheet() Dim objchart As Chart Set objchart = Charts.Add With objchart

.ChartType = xlColumnClustered

.SetSourceData Source:=Sheets(\ .HasTitle = True End With End Sub

创建嵌入式图表

Public Sub addembeddedchart() Dim objchart As Chart

Set objchart = Charts.Add

Set objchart = objchart.Location(where:=xlLocationAsObject, Name:=\

With objchart

.ChartType = xlColumnClustered

.SetSourceData Sheets(\.HasTitle = True

. charttitle.caption = \With .Parent

.Top = Range(\.Left = Range(\.Name = \嵌入式图表\End With End With

End Sub 或者

Public Sub addembeddebchart2() Dim objchart As ChartObject

Set objchart = Sheets(\End Sub

操作EXCEL图表

ActiveSheet.ChartObjects(\

ActiveChart.Deselect

If ActiveChart Is Nothing Then

MsgBox \选择了一个图表\End If

修改图表的数据系列

Charts(1).setsourcedate Source:=Sheets(1).Range(\

Dim objseries As Series

For Each objseries In ActiveChart.SeriesCollection MsgBox objseries.Formula Next

ActiveChart.SeriesCollection .Count

SeriesCollection (SeriesCollection.Count)

Charts(\

Source:=ActiveWorkbook.Worksheets(\Worksheets(\

ActiveChart.SeriesCollection(1).Add Source:=Worksheets(\

ActiveChart.SeriesCollection(1).ChartType = xlXYScatterLinesNoMarkers

With Charts(\.hasdatalables = True

.ApplyDataLabels Type:=xlValue End With

Charts(\Charts(\

Charts(\Charts(\

图表的输出

Chart.export(filename,filtername,interactive)

If not activechart is nothing then

Activechart.export filename:=”test.gif”,filtername:=”gif” En d if

EXCEL图表事件

在使用嵌入式图表的CHART对象事件之前，必须新建一个类模块并声明一个带有事件的CHART类型的对象！ 类模块中;

Private WithEvents eventchart As Chart

Public Function connectchart(objchart) As Boolean If Not objchart Is Nothing Then Set eventchart = objchart connectchart = True Else

connectchart = False End If

End Function

嵌入式图表

APPLICATION,WORKBOOK,WORKSHEET,CHARTOBJECT,CHART 图表工作表APPLICATION，workbook,chart

自定义菜单栏和工具栏

Commandbars,commandbar,commandbarcontrols,commandbarcontrol

commandbarcontrols commandbar commandbarcontrol commandbarbutton commandbarpopup

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库EXCEL VBA初步(5)在线全文阅读。