VBA,单元格处理,数据复制,格式设置,折线图,图表属性设置

首先说一下.xlsm文件和.xlsx文件的区别:

       .xlsx文件只能存储数据,不能存储对数据进行处理的VB代码,而.xlsm文件既可以存储数据,又可以存储代码。

新建一个.xlsm文件(打开.xlsx源数据文件并另存为.xlsm也可以),输入数据源(源数据和VB代码可以从以下链接下载):

                    原始数据以及 VBA 代码

打开.xlsm文件后在当前Sheet的名字上右击,选“View Code”(或者直接快捷键Alt+F11打开VB编辑窗口),出现下图:

图中红圈1表示当前工作簿的第一个工作表,系统命名为Sheet,我们重命名为RAW_DATA。写VB代码的时候通过这两个名称都可以引用到相应工作表,不过代码稍微会有一点不一样,我的代码使用的是系统命名,即Sheet1。

双击图中的Sheet1,Sheet2,Sheet3以及红圈2中的Module1都会出现一个代码编辑区域,我的代码是写在Module1中的。(个人猜想在Sheet1中写的代码只能控制Sheet1中的对象,当要调用其他工资表数据时可能会有约束;而Module1是对整个工作簿的对象都有控制权,因为我的代码不是太复杂,但每个工资表都要用到,为了方便,我的代码就写在了Module1中)

图中红圈3中三个按钮分别是运行,暂停,停止的作用,当代码写完后按绿色三角形运行,Debug时按蓝色方框结束Debug模式。

接下来就没甚好说的了,Let me show you my code...

(VB代码的注释用英文单引号“'”开始,而且只能单行注释。View-Toolbars-Edit工具栏里有一个注释代码块的按钮,很方便)

以下代码段是针对代码的功能进行注释的。相对小白的来说,首先要理解每行代码的意思才能理解整个程序。(其实是为了防止我自己健忘)

' 由'开始的行是注释,直到本行结束
' Range()	--用于指定某一个或者某一些单元格区域
' Rows()	--指定某一行或者多行
' Columns()	--指定某一列或者多列

Sub MyCode()
    
    Sheet2.UsedRange.ClearContents      	' 删除Sheet2工作表的所有单元格内容
    
    If Sheet3.ChartObjects.Count > 0 Then   	' 删除Sheet3工作表的所有图表。当对一个没有图表的工作表进行删除操作时会报错,
        Sheet3.ChartObjects.Delete          	' 所有要先进行判断,如果图表数大于0才执行删除操作
    End If
    
    Sheet1.Columns(3).Copy Destination:=Sheet2.Columns(1)   	' 将Sheet1第3列的数据拷贝到Sheet2的第一列
    Sheet1.Columns(4).Copy Destination:=Sheet2.Columns(3)
    Sheet1.Columns(5).Copy Destination:=Sheet2.Columns(6)
    Sheet1.Columns(6).Copy Destination:=Sheet2.Columns(4)
    
    Sheet2.Rows(1).Delete					' 删除Sheet2的第一行
    Sheet2.Range("A1") = "       TIME       "			' 设置Sheet2的A1单元格内容,注意这里有多余的空格
    Sheet2.Range("B1") = " Delta time(min) "
    Sheet2.Range("C1") = " Temp setting(deg) "
    Sheet2.Range("D1") = " Temp test(deg) "
    Sheet2.Range("E1") = " PCB Output(V) "
    Sheet2.Range("F1") = " MCU Output(12bit DAC) "
    Sheet2.Range("A1:F1").Font.Bold = True			' 把Sheet2的A1:F1区域字体加粗
    Sheet2.Range("A1:F1").Columns.AutoFit			
	' 设置Sheet2的A到F列根据其第一行的内容而自动调节单元格宽度,上面设置内容时多余的空格是为了占位,使整列数据都可以完整显示
    'Sheet2.Columns("A:F").Select          			' 选择Sheet2的A到F列
    'Selection.HorizontalAlignment = Excel.xlCenter		' 对选择的区域设置为居中对齐
    Sheet2.Columns("A:F").HorizontalAlignment = Excel.xlCenter  ' 这一行和上面两行的结果一样,可相互替换
    'Sheet2.Columns("E").Select					' 选择Sheet2的E列
    'Selection.NumberFormatLocal = "0.00"			' 对选择的单元格区域进行格式化。0.00表示数字保留两位小数,0.00%表示以百分制表示。
    Sheet2.Columns("E").NumberFormatLocal = "0.00"    		' 这一行和上面两行的结果一样,可相互替换
    Sheet2.Columns("B").NumberFormatLocal = "0"			' 设置Sheet2的B列数学不显示小数部分
    
    countRows = Sheet2.UsedRange.Rows.Count            		' 计算Sheet2工作表所有数据的总行数
    
    Sheet2.Range("E2").Formula = "=IF(F2>824,(F2-824)/(4095-824)*5,(F2-824)/824*1.6)"		' 在Sheet2的E2单元格写入公式
    Sheet2.Range("E2:E" & countRows).FillDown			' 对E2单元格的内容向下填充到最后一行。countRows是前面定义的记录总行数的变量
    
    fragState = "FindStandbyStart"			' 设置变量并赋值
    startRows = 2
    endRows = 0
    countLoop = 0
    
    For i = 2 To countRows				' for循环,从2循环到countRows(表示总行数的变量),即从第2行遍历到最后一行

	If i = countRows Then			
            endRows = countRows		
            fragState = "DrawChart"	
        End If

        If fragState = "FindStandbyStart" Then		' 判断是否相等
            If Sheet2.Range("C" & i) = 25 Then          ' 判断Sheet2当前行(i)的C列是否为25
                fragState = "FindLoopEnd"		' 变量值改变(状态改变)
            End If					' 判断语句结束
        ElseIf fragState = "FindLoopEnd" Then		' Else if分支
            If Sheet2.Range("C" & i) = 95 Then          ' 判断Sheet2当前行(i)的C列是否为95
                endRows = i - 1
                fragState = "DrawChart"
                'MsgBox endRows				' 调试的时候用了,就是把endRows的值打印出来看看是否正确
            End If
	End If
        If fragState = "DrawChart" Then         	
            'MsgBox startRows
            'MsgBox endRows
            Sheet2.Range("B" & startRows).Formula = "=(A" & startRows & "-$A$" & startRows & ")*24*60"	
		' 为了便于理解,这里可以把startRows当成2,即Sheet2.Range("B2").Formula = "=(A2-$A$2)*24*60",但实际上startRows不是固定的。
		' 这样应该很容易理解了,就是在B2中写了个公式而已。注意这里的处理方式,变量是不需要写在双引号里的,然后用&连接起来。
            Sheet2.Range("B" & startRows, "B" & endRows).FillDown	' 对Sheet2的B列 从startRows行到endRows行 按startRow单元格的内容 进行填充
            
            x = Sheet3.Range("A" & countLoop * 15 + 1, "A" & countLoop * 15 + 15).Left	' 设置图表容器的左边缘
            y = Sheet3.Range("A" & countLoop * 15 + 1, "S" & countLoop * 15 + 1).Top	' 设置图表容器的右边缘
	    w = Sheet3.Range("A1:S1").Width		' 设置图表容器的宽度为A列到S列
            h = Sheet3.Range("A1:A15").Height - 1	' 设置图表容器的高度为15行

            Set Ch1 = Sheet3.ChartObjects.Add(x, y, w, h)     	' Ch1相对于一个图表容器,后面要画的折线图的所有对象都在这个容器内,这里是设置容器大小
            
            If countLoop < 10 Then				' 设置图表容器的名字,为了控制名字的长度以致所有加了这个判断
                Ch1.Name = "Result-0" & countLoop + 1		' 容器名字为格式为“Result-xx”
            Else
                Ch1.Name = "Result-" & countLoop + 1
            End If

            With Ch1.Chart				' With是一个代码块,由End With结束。表示这个with块中的所有代码都是对Ch1.Chart的属性进行设置。
                .HasTitle = True			' 如果没有With块,这句完全能用"Ch1.Chart.HasTitle = True"代替
                .ChartTitle.Text = Ch1.Name		' 把图表容器的名字作为图表的标题
                .ChartTitle.Left = 415			' 设置图表标题的位置
                .ChartTitle.Top = -5

                .PlotArea.Width = 885       		' 设置图表容器中 画图区域 相对于图表容器的位置
                .PlotArea.Left = 10			' 图表容器中除了画图区域,还有标题,图列,以及坐标轴名字等其他对象
                .PlotArea.Top = 15			' 设置合适的 画图区域,是为了其他对象更好的显示
                .PlotArea.Height = 175

                .Legend.Position = xlLegendPositionTop	' 将图例放在画图区域的上方。图例就是在一个有多个折线的图中,对每个折线含义的说明
                .Legend.Left = 275			' 因为按上面的方法设置的图例位置稍微有点不太理想,所有这里对图例位置进行微调。
                .Legend.Top = 20			' 注意这里的微调值是相对于图表容器的左上角,而不是 画图区域
                .ChartType = xlLine			' 设置图表类型为折线图

                .SeriesCollection.NewSeries		' 为折线图添加数据
                .SeriesCollection(1).Values = Sheet2.Range("C" & startRows, "C" & endRows)	' 选择数据区域
                .SeriesCollection(1).XValues = Sheet2.Range("B" & startRows, "B" & endRows)	' 选择X轴数据区域
                .SeriesCollection(1).Name = Sheet2.Range("C1")					' 设置这一系列数据的名称,即图例的名称
                .SeriesCollection(1).AxisGroup = 1	' 这里是指明这些数据用Y主坐标轴(图表中可以有左右两个表示不同意义的Y轴,左边是1,右边是2)

                .SeriesCollection.NewSeries		' 添加新数据
                .SeriesCollection(2).Values = Sheet2.Range("D" & startRows, "D" & endRows)
                .SeriesCollection(2).XValues = Sheet2.Range("B" & startRows, "B" & endRows)
                .SeriesCollection(2).Name = Sheet2.Range("D1")
                .SeriesCollection(2).AxisGroup = 1

                .SeriesCollection.NewSeries
                .SeriesCollection(3).Values = Sheet2.Range("E" & startRows, "E" & endRows)
                .SeriesCollection(3).XValues = Sheet2.Range("B" & startRows, "B" & endRows)
                .SeriesCollection(3).Name = Sheet2.Range("E1")
                .SeriesCollection(3).AxisGroup = 2	' 这一系列数据用的是Y副轴(右边)
                
                With .Axes(xlValue, xlPrimary)		' 对Y主坐标轴的属性进行设置
                    .MinimumScale = 0			' 设置Y主坐标轴的最小值
                    .MaximumScale = 115			' 最大值
                    .HasTitle = True			' 显示这个坐标轴的标题
                    .AxisTitle.Text = "Temp(Degree)"	' 设置标题为"Temp(Degree)"
                End With
                
                With .Axes(xlValue, xlSecondary)	' 对Y副坐标轴的属性进行设置
                    .MinimumScale = -2
                    .MaximumScale = 12
                    .HasTitle = True
                    .AxisTitle.Text = "Voltage(V)"
                End With
                
                With .Axes(xlCategory)			' 对X坐标轴的属性进行设置
                    .HasTitle = True
                    .AxisTitle.Text = "Time(min)"
                    .TickLabelSpacing = 200		' X轴坐标刻度太密了,设置每200个数据显示一个刻度。(设置范围是0-255)
                End With
                
            End With		' 注意,每一个With代码块都由With开始,End With结束

            countLoop = countLoop + 1
            startRows = endRows + 1
            fragState = "FindStandbyStart"
        End If
    Next
End Sub

现在对整个程序的功能进行一个简单的说明:原始数据是对一个产品进行工作寿命进行测试而得到的,所以数据总是在一定时间后又从新开始,但是这个一定时间稍微有点差别,这就导致了每次运行产生的数据长度是不太一样的。那如何区分每次运行产生的数据呢?请看Sheet2(DATA)的C列,C列每一次运行都是以95->50->72->95->50->72->...->25的方式结束的,但是每个值都会重复很多次。所以我就从C列开始查找,找到第一个95,表示第一次运行开始了,把行号startRows记录下来,然后接着找25,找的25后再向后查找95,这个95就表示第二次运行开始了,把这个行号减一就得到第一次运行的结束行号了。

既然已经知道了数据范围,就可以根据数据画图了。

下面的代码和上面一样,只是这一份的注释主要偏重于逻辑方面:

' 以'开始的为注释,一直到本行结束有效

Sub MyCode()
    
    Sheet2.UsedRange.ClearContents      	' delete all the data of Sheet2
    
    If Sheet3.ChartObjects.Count > 0 Then   	' there is error if delete the sheet without chart
        Sheet3.ChartObjects.Delete          	' delete all the chart
    End If
    
    Sheet1.Columns(3).Copy Destination:=Sheet2.Columns(1)   ' 数据拷贝
    Sheet1.Columns(4).Copy Destination:=Sheet2.Columns(3)
    Sheet1.Columns(5).Copy Destination:=Sheet2.Columns(6)
    Sheet1.Columns(6).Copy Destination:=Sheet2.Columns(4)
    
    Sheet2.Rows(1).Delete
    Sheet2.Range("A1") = "       TIME       "		' 每一列数据的名称
    Sheet2.Range("B1") = " Delta time(min) "
    Sheet2.Range("C1") = " Temp setting(deg) "
    Sheet2.Range("D1") = " Temp test(deg) "
    Sheet2.Range("E1") = " PCB Output(V) "
    Sheet2.Range("F1") = " MCU Output(12bit DAC) "
    Sheet2.Range("A1:F1").Font.Bold = True
    Sheet2.Range("A1:F1").Columns.AutoFit
    'Sheet2.Columns("A:F").Select          		' select columns A to F
    'Selection.HorizontalAlignment = Excel.xlCenter
    Sheet2.Columns("A:F").HorizontalAlignment = Excel.xlCenter    	' same with above 2 rows
    'Sheet2.Columns("E").Select
    'Selection.NumberFormatLocal = "0.00"
    Sheet2.Columns("E").NumberFormatLocal = "0.00"    	' same with above 2 rows
    Sheet2.Columns("B").NumberFormatLocal = "0"
    
    countRows = Sheet2.UsedRange.Rows.Count            	' get the number of rows
    
    Sheet2.Range("E2").Formula = "=IF(F2>824,(F2-824)/(4095-824)*5,(F2-824)/824*1.6)"	' 填充公式,这里是把12位的ADC采样数据转换成电压值
    Sheet2.Range("E2:E" & countRows).FillDown						' 把上面填充的公式下拉到最后一行
    
    fragState = "FindStandbyStart"
    startRows = 2			' 原始数据的第一次循环一定从第二行开始,所以不用找了
    endRows = 0
    countLoop = 0			' 运行次数计数,主要是为了画图的时候决定图表位置,不能所有的图表都重叠画在一起啊
    
    For i = 2 To countRows
    
        If i = countRows Then		' 因为程序可能在任意地方停止,如果没有这个判断,就只能按部就班每次找到25后面的第一个95的前一行才能得到endRows	
            endRows = countRows		' 但如果数据就停在了25呢??那fragState的状态就永远到不了"DrawChart",也就永远无法画出最后一个图了
            fragState = "DrawChart"	' 所有这个If的作用只是为了画最后一个不完整循环的图形。
        End If
        
        If fragState = "FindStandbyStart" Then		' 因为前面已经记录了第一个95的位置,即startRows
            If Sheet2.Range("C" & i) = 25 Then         	' 所以这里是在找25了
                fragState = "FindLoopEnd"		' 如果找到了就进入下一个状态,即找下一个95
            End If
        ElseIf fragState = "FindLoopEnd" Then
            If Sheet2.Range("C" & i) = 95 Then         	' 如果找到了下一个95,即下一个循环的开始
                endRows = i - 1				' 则上一行就是上次循环的结束,即endRows
                fragState = "DrawChart"			' 找到了startRows和endRows,制图的数据源就确定了,也就可以开始画图了
                'MsgBox endRows
            End If
	End If
        If fragState = "DrawChart" Then         	' 开始画图
            'MsgBox startRows
            'MsgBox endRows
            Sheet2.Range("B" & startRows).Formula = "=(A" & startRows & "-$A$" & startRows & ")*24*60"	' 填充公式并下拉
            Sheet2.Range("B" & startRows, "B" & endRows).FillDown					' 这里是计算每一行数据距循环开始的时间,单位为分钟
            
            x = Sheet3.Range("A" & countLoop * 15 + 1, "A" & countLoop * 15 + 15).Left
            y = Sheet3.Range("A" & countLoop * 15 + 1, "S" & countLoop * 15 + 1).Top
            w = Sheet3.Range("A1:S1").Width
            h = Sheet3.Range("A1:A15").Height - 1
            Set Ch1 = Sheet3.ChartObjects.Add(x, y, w, h)     	' 定义图表容器的大小
            
            If countLoop < 10 Then				' 给图表容器命名,并保证名字长度一致	
                Ch1.Name = "Result-0" & countLoop + 1
            Else
                Ch1.Name = "Result-" & countLoop + 1
            End If

            With Ch1.Chart
                .HasTitle = True
                .ChartTitle.Text = Ch1.Name
                .ChartTitle.Left = 415
                .ChartTitle.Top = -5

                .PlotArea.Width = 885       			' 设置作图区大小
                .PlotArea.Left = 10
                .PlotArea.Top = 15
                .PlotArea.Height = 175

                .Legend.Position = xlLegendPositionTop		' 这三行是把图例放置在任意位置的法宝。第一行是为了让图例单行显示
                .Legend.Left = 275				' xlLegendPositionRight可以让图例多行显示,然后再利用这两行调整位置
                .Legend.Top = 20

                .ChartType = xlLine				' 折线图
                .SeriesCollection.NewSeries			' 添加数据
                .SeriesCollection(1).Values = Sheet2.Range("C" & startRows, "C" & endRows)
                .SeriesCollection(1).XValues = Sheet2.Range("B" & startRows, "B" & endRows)
                .SeriesCollection(1).Name = Sheet2.Range("C1")	' 设置图例名称
                .SeriesCollection(1).AxisGroup = 1		' 设置参考坐标
                .SeriesCollection.NewSeries
                .SeriesCollection(2).Values = Sheet2.Range("D" & startRows, "D" & endRows)
                .SeriesCollection(2).XValues = Sheet2.Range("B" & startRows, "B" & endRows)
                .SeriesCollection(2).Name = Sheet2.Range("D1")
                .SeriesCollection(2).AxisGroup = 1
                .SeriesCollection.NewSeries
                .SeriesCollection(3).Values = Sheet2.Range("E" & startRows, "E" & endRows)
                .SeriesCollection(3).XValues = Sheet2.Range("B" & startRows, "B" & endRows)
                .SeriesCollection(3).Name = Sheet2.Range("E1")
                .SeriesCollection(3).AxisGroup = 2
                
                With .Axes(xlValue, xlPrimary)
                    .MinimumScale = 0
                    .MaximumScale = 115
                    .HasTitle = True
                    .AxisTitle.Text = "Temp(Degree)"
                End With
                
                With .Axes(xlValue, xlSecondary)
                    .MinimumScale = -2
                    .MaximumScale = 12
                    .HasTitle = True
                    .AxisTitle.Text = "Voltage(V)"
                End With
                
                With .Axes(xlCategory)
                    .HasTitle = True
                    .AxisTitle.Text = "Time(min)"
                    .TickLabelSpacing = 200
                End With
                
            End With

            countLoop = countLoop + 1		' 循环次数递增,表示一个循环的图表已经制作完成,将开始下一个循环图表的制作
            startRows = endRows + 1             ' 记录好下一次循环开始位置,也可以写成startRows = i
            fragState = "FindStandbyStart"	' 进入开始寻找下一个25的状态
        End If
    Next					' for循环的结束
End Sub						' 函数结尾

OVER

 

 

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页