|
楼主 |
发表于 2018-1-1 22:13
|
显示全部楼层
展示一下所有代码:
工作表Sheet1的事件代码:
- Option Explicit
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address = Range("b1").Address Then
- Application.EnableEvents = False
- Dim i%, dian$(), r%, js%
- Union(Range("a19:e" & Rows.Count), Range("o2:u" & Rows.Count)).ClearContents
- n = Target.Value
- ReDim dian$(1 To n, 1 To 1)
- js = 1
- If n > 26 Then '生成点集V
- For i = 1 To n
- r = (i - 1) Mod 26 + 1
- If r = 26 Then js = js + 1
- dian(i, 1) = Chr(96 + r) & js
- Next i
- Else
- For i = 1 To n
- dian(i, 1) = Chr(96 + i)
- Next i
- End If
- Range("a19").Resize(n, 1).Value = dian
- Range("b2").Value = 1
- pd = 0
- Range("b14").Value = "否"
- Application.EnableEvents = True
- End If
- If Not (Target.Address = Range("b2").Address Or Target.Address = Range("b4").Address Or Target.Address = Range("b9").Address _
- Or Target.Address = Range("b14").Address Or Target.Address = Range("d11").Address Or Target.Address = Range("d12").Address) Then
- If pd = 2 Then pd = 0 '为保证第⑤步读入数据正确,如产生除设置有填充色单元格值的其他修改时,将强制从头再来
- End If
- End Sub
复制代码
“成图”模块代码:
- Option Explicit
- Public n%, m%, pd%
- Sub dian() '生成点坐标
- Sheet1.Activate
- Dim i%, lx$
- If n = 0 Then MsgBox "请先输入图的阶。": Range("b1").Select: Exit Sub
- Application.EnableEvents = False
- ReDim zb_dian#(1 To n, 1 To 2)
- Union(Range("b19:d" & Rows.Count), Range("o2:u" & Rows.Count)).ClearContents
- lx = Range("b4").Value
- Randomize
- For i = 1 To n
- If lx = "随机坐标" Then
- zb_dian(i, 1) = -10 + Rnd() * 20: zb_dian(i, 2) = -10 + Rnd() * 20
- Else
- zb_dian(i, 1) = 9 * Cos(2 * WorksheetFunction.Pi() / n * (i - 1) + 3 * WorksheetFunction.Pi() / 4)
- zb_dian(i, 2) = 9 * Sin(2 * WorksheetFunction.Pi() / n * (i - 1) + 3 * WorksheetFunction.Pi() / 4)
- End If
- Next i
- Range("b19").Resize(n, 2).Value = zb_dian
- pd = 1
- Range("b14").Value = "否"
- Application.EnableEvents = True
- End Sub
- Sub bian1() '随机生成有向边
- Sheet1.Activate
- m = Range("b2").Value
- If n = 0 Then MsgBox "请先输入图的阶。": Range("b1").Select: Exit Sub
- If pd = 0 Then MsgBox "请先生成点的坐标。": Range("b4").Select: Exit Sub
- Application.EnableEvents = False
- Union(Range("d19:e" & Rows.Count), Range("o2:u" & Rows.Count)).ClearContents
- Dim name_dian, name_bian$(), sc_bian(), i%, j%, js&, r&, gd, d_x As Object, d_y As Object, c&
- ReDim name_bian$(1 To n * (n - 1) / 2, 1 To 3), sc_bian(1 To m, 1 To 2)
- name_dian = Range("a19:c" & 18 + n).Value
- c = Range("b9").Value '容量最大值
- js = 0
- Set d_x = CreateObject("scripting.dictionary")
- Set d_y = CreateObject("scripting.dictionary")
- For i = 1 To n - 1 '生成所有边
- d_x(name_dian(i, 1)) = name_dian(i, 2): d_y(name_dian(i, 1)) = name_dian(i, 3) '点名称及坐标信息读入字典
- For j = i + 1 To n
- js = js + 1
- name_bian(js, 1) = name_dian(i, 1)
- name_bian(js, 2) = name_dian(j, 1)
- Next j
- Next i
- d_x(name_dian(n, 1)) = name_dian(n, 2): d_y(name_dian(n, 1)) = name_dian(i, 3)
- Randomize
- For i = 1 To js '随机乱序所有边
- r = Int(Rnd() * (js - i + 1)) + i
- gd = name_bian(r, 1): name_bian(r, 1) = name_bian(i, 1): name_bian(i, 1) = gd
- gd = name_bian(r, 2): name_bian(r, 2) = name_bian(i, 2): name_bian(i, 2) = gd
- Next i
- js = 0
- For i = 1 To m '抽取前m条边并随机指定方向
- name_bian(i, 3) = Int(Rnd() + 0.5) '1为→,0为←
- sc_bian(i, 1) = name_bian(i, 1) & IIf(name_bian(i, 3), "→", "←") & name_bian(i, 2)
- sc_bian(i, 2) = Int(Rnd() * c) + 1
- If name_bian(i, 3) Then '写入边
- Cells(i + 1 + js, "o").Value = name_bian(i, 1): Cells(i + 1 + js, "p").Value = d_x(name_bian(i, 1)): Cells(i + 1 + js, "q").Value = d_y(name_bian(i, 1))
- Cells(i + 2 + js, "o").Value = name_bian(i, 2): Cells(i + 2 + js, "p").Value = d_x(name_bian(i, 2)): Cells(i + 2 + js, "q").Value = d_y(name_bian(i, 2))
- Cells(i + 2 + js, "s").Value = sc_bian(i, 2)
- Cells(i + 1 + js, "t").Value = d_x(name_bian(i, 1)): Cells(i + 1 + js, "u").Value = d_y(name_bian(i, 1))
- Cells(i + 2 + js, "t").Value = (d_x(name_bian(i, 1)) + d_x(name_bian(i, 2))) / 2: Cells(i + 2 + js, "u").Value = (d_y(name_bian(i, 1)) + d_y(name_bian(i, 2))) / 2
- Else
- Cells(i + 1 + js, "o").Value = name_bian(i, 2): Cells(i + 1 + js, "p").Value = d_x(name_bian(i, 2)): Cells(i + 1 + js, "q").Value = d_y(name_bian(i, 2))
- Cells(i + 2 + js, "o").Value = name_bian(i, 1): Cells(i + 2 + js, "p").Value = d_x(name_bian(i, 1)): Cells(i + 2 + js, "q").Value = d_y(name_bian(i, 1))
- Cells(i + 2 + js, "s").Value = sc_bian(i, 2)
- Cells(i + 1 + js, "t").Value = d_x(name_bian(i, 2)): Cells(i + 1 + js, "u").Value = d_y(name_bian(i, 2))
- Cells(i + 2 + js, "t").Value = (d_x(name_bian(i, 2)) + d_x(name_bian(i, 1))) / 2: Cells(i + 2 + js, "u").Value = (d_y(name_bian(i, 2)) + d_y(name_bian(i, 1))) / 2
- End If
- js = js + 2
- Next i
- Range("d19").Resize(m, 2).Value = sc_bian
- pd = 2
- Range("b14").Value = "否"
- Application.EnableEvents = True
- End Sub
- Sub bian2() '添加手动调整有向边
- Sheet1.Activate
- m = Range("b2").Value
- If n = 0 Then MsgBox "请先输入图的阶。": Range("b1").Select: Exit Sub
- If pd = 0 Then MsgBox "请先生成点的坐标。": Range("b4").Select: Exit Sub
- Application.EnableEvents = False
- Union(Range("d19:d" & Rows.Count), Range("o2:u" & Rows.Count)).ClearContents
- Dim name_dian, name_bian(), sc_bian(), i%, j%, js&, r&, gd, d_x As Object, d_y As Object
- ReDim sc_bian(1 To m, 1 To 1)
- name_dian = Range("a19:c" & 18 + n).Value
- name_bian = Range("e19:h" & 18 + m).Value
- Set d_x = CreateObject("scripting.dictionary")
- Set d_y = CreateObject("scripting.dictionary")
- For i = 1 To n
- d_x(name_dian(i, 1)) = name_dian(i, 2): d_y(name_dian(i, 1)) = name_dian(i, 3) '点名称及坐标信息读入字典
- Next i
- For i = 1 To m '判断手动输入是否有空值,输入点是否存在,起点终点是否相同,边是否重复
- For j = 1 To 4
- If name_bian(i, j) = "" Then MsgBox "单元格:" & Cells(16 + i, 4 + j).Address & "数据未录入!": Cells(16 + i, 4 + j).Select: Exit Sub
- Next j
- If Not (d_x.exists(name_bian(i, 2))) Then MsgBox name_bian(i, 2) & "点不存在!": Cells(16 + i, 6).Select: Exit Sub
- If Not (d_x.exists(name_bian(i, 4))) Then MsgBox name_bian(i, 4) & "点不存在!": Cells(16 + i, 8).Select: Exit Sub
- If name_bian(i, 2) = name_bian(i, 4) Then MsgBox "起点与终点相同!": Union(Cells(16 + i, 6), Cells(16 + i, 8)).Select: Exit Sub
- For j = i + 1 To m
- If name_bian(i, 2) = name_bian(j, 2) And name_bian(i, 4) = name_bian(j, 4) Then
- MsgBox "两边重复!": Union(Cells(16 + i, 6), Cells(16 + i, 8), Cells(16 + j, 6), Cells(16 + j, 8)).Select: Exit Sub
- End If
- Next j
- Next i
- js = 0
- For i = 1 To m
- sc_bian(i, 1) = name_bian(i, 2) & name_bian(i, 3) & name_bian(i, 4)
- If name_bian(i, 3) = "→" Then '写入边
- Cells(i + 1 + js, "o").Value = name_bian(i, 2): Cells(i + 1 + js, "p").Value = d_x(name_bian(i, 2)): Cells(i + 1 + js, "q").Value = d_y(name_bian(i, 2))
- Cells(i + 2 + js, "o").Value = name_bian(i, 4): Cells(i + 2 + js, "p").Value = d_x(name_bian(i, 4)): Cells(i + 2 + js, "q").Value = d_y(name_bian(i, 4))
- Cells(i + 2 + js, "s").Value = name_bian(i, 1) '写入容量显示数据标签
- Cells(i + 1 + js, "t").Value = d_x(name_bian(i, 2)): Cells(i + 1 + js, "u").Value = d_y(name_bian(i, 2))
- Cells(i + 2 + js, "t").Value = (d_x(name_bian(i, 2)) + d_x(name_bian(i, 4))) / 2: Cells(i + 2 + js, "u").Value = (d_y(name_bian(i, 2)) + d_y(name_bian(i, 4))) / 2
- Else
- Cells(i + 1 + js, "o").Value = name_bian(i, 4): Cells(i + 1 + js, "p").Value = d_x(name_bian(i, 4)): Cells(i + 1 + js, "q").Value = d_y(name_bian(i, 4))
- Cells(i + 2 + js, "o").Value = name_bian(i, 2): Cells(i + 2 + js, "p").Value = d_x(name_bian(i, 2)): Cells(i + 2 + js, "q").Value = d_y(name_bian(i, 2))
- Cells(i + 2 + js, "s").Value = name_bian(i, 1) '写入容量显示数据标签
- Cells(i + 1 + js, "t").Value = d_x(name_bian(i, 4)): Cells(i + 1 + js, "u").Value = d_y(name_bian(i, 4))
- Cells(i + 2 + js, "t").Value = (d_x(name_bian(i, 4)) + d_x(name_bian(i, 2))) / 2: Cells(i + 2 + js, "u").Value = (d_y(name_bian(i, 4)) + d_y(name_bian(i, 2))) / 2
- End If
- js = js + 2
- Next i
- Range("d19").Resize(m, 1).Value = sc_bian
- pd = 2
- Range("b14").Value = "否"
- Application.EnableEvents = True
- End Sub
复制代码
装不下,下楼继续。
|
|