VBA的三个代码口袋

sheet->module->class module

看上去他们的顺序是这样的,sheet->module->class module
1---------sheet应用过程
2---------过程来源module方便众人使用(公共) module不过是实例化的过程
3---------程序的灵魂正是来自于class module,他是module的神.

举个栗子 不如举个苹果

a	b	c
1	2	3
2	3	4
5	6	7

用他生成个Chart1,目的是让左右键控制图表变化.

我们构件的顺序与调用的顺序正好相反,
(1)首先是
classclsEvent

Public WithEvents xlChart As Chart

Private Sub xlChart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Cancel = True
End Sub

Private Sub xlChart_BeforeRightClick(Cancel As Boolean)
    Cancel = True
End Sub

Private Sub xlChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    If Button = 1 Then
        xlChart.Axes(xlValue).MaximumScale = xlChart.Axes(xlValue).MaximumScale - 50
    End If
   
    If Button = 2 Then
        xlChart.Axes(xlValue).MaximumScale = xlChart.Axes(xlValue).MaximumScale + 50
    End If
End Sub

(2)然后是module
Public myChartEvent As New clsEvents

Sub TrapChartEvent()
    Set myChartEvent.xlChart = Worksheets("Sheet1").ChartObjects(1).Chart
End Sub

(3)最终在界面workbook部分的操作
 Private Sub Workbook_Open()
    TrapChartEvent
End Sub