EXCEL VBA初步

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图表事件