|
楼主 |
发表于 2019-8-25 19:45
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
里面的代码,附件里也有
- Sub TESTvba测试用()
- On Error Resume Next
- Application.DisplayAlerts = False ' 取消了警告提示'
- Application.ScreenUpdating = False '关闭屏幕刷新
- Application.Calculation = xlManual '将自动计算改为手工计算。
- ' Dim oWShell
- ' Set oWShell = CreateObject("WScript.Shell")
- ' Dim sValue As String
- ' Dim sKey As String
- ' Dim sVersion As String
- ' sVersion = Excel.Application.Version
- ' '键的名称
- ' sKey = "HKEY_CURRENT_USER\Software\Microsoft\Office" & sVersion & "\Excel\Security"
- ' '信任对vba工程对象模型的访问
- ' sValue = "AccessVBOM"
- ' With oWShell
- ' '关闭对vba工程对象模型的访问
- ' .RegWrite sKey & "" & sValue, 0, "REG_DWORD"
- ' '开启对vba工程对象模型的访问
- ' .RegWrite sKey & "" & sValue, 1, "REG_DWORD"
- ' End With
-
- Dim f As String
- f = ActiveWorkbook.Path & "" & Format(Now, "yyyy-mm-dd-hhmmss") & ".BAS"
- Open f For Output As #1
- Print #1, " Attribute VB_Name = ""格式复制" & Format(Now, "yyyymmddhhmmss") & """"
- Print #1, " Sub 格式复制" & Format(Now, "yyyymmddhhmmss") & "()"
- Print #1, "Application.DisplayAlerts = False' 取消了警告提示' "
- Print #1, "Application.ScreenUpdating=False'关闭屏幕刷新"
- Print #1, "Application.Calculation = xlManual'将自动计算改为手工计算。"
- Print #1, "On Error Resume Next"
- Dim Sel As Range
- Set Sel = Selection
- Dim MyText As String
- Dim 总行数 As Integer
- Dim 总列数 As Integer
- Dim 原始开始行 As Integer
- Dim 原始开始列 As Integer
- 总行数 = Sel.Rows.Count
- 总列数 = Sel.Columns.Count
- Dim i As Integer
- 原始开始行 = Sel.Row
- 原始开始列 = Sel.Column
- Print #1, " Dim 开始地址 As String"
- Print #1, " 开始地址 = Selection.Cells(1, 1).Address"
- Print #1, " Dim 新设开始行 As Integer"
- Print #1, " Dim 新设开始列 As Integer"
- Print #1, " 新设开始行 = Selection.Row "
- Print #1, " 新设开始列 = Selection.Column "
- For i = 1 To 总行数
- Print #1, "Range(开始地址).Offset(" & i - 1 & " , 0).EntireRow.RowHeight =" & Sel.Cells(1, 1).Offset(i - 1, 0).EntireRow.RowHeight
- Next i
- For i = 1 To 总列数
- Print #1, "Range(开始地址).Offset( 0 ," & i - 1 & " ).EntireColumn.ColumnWidth =" & Cells(Split(Sel.Cells(1, 1).Address, "$")(2), Sel.Cells(1, 1).Column - 1 + i).ColumnWidth
- Next i
- Set d = CreateObject("scripting.dictionary")
- Dim 单元格分组数量 As Integer
- For Each xCell In Sel '列出所有单元格
- If xCell.MergeCells Then
- ad_str = xCell.MergeArea.Address
- If d.Count > 0 And d.Exists(ad_str) Then
- Else
- d.Add ad_str, xCell.FormulaR1C1
- End If
- Else
- d.Add xCell.Address, xCell.FormulaR1C1
- End If
- Next
- 单元格分组数量 = d.Count
- Dim c()
- ReDim c(0 To 单元格分组数量 - 1) ' '重新定义数组的大小 ,新设的地址 组
- a = d.keys
- b = d.items
- For i = 0 To 单元格分组数量 - 1
- If InStr(a(i), ":") > 0 Then
- c(i) = "Cells(新设开始行 +" & _
- (Range(Left(a(i), InStr(a(i), ":") - 1)).Row - 原始开始行) _
- & ", 新设开始列 + " & _
- (Range(Left(a(i), InStr(a(i), ":") - 1)).Column - 原始开始列) _
- & ")" & "," & "Cells(新设开始行 +" & _
- (Range(Right(a(i), Len(a(i)) - InStr(a(i), ":"))).Row - 原始开始行) _
- & ", 新设开始列 + " & _
- (Range(Right(a(i), Len(a(i)) - InStr(a(i), ":"))).Column - 原始开始列) _
- & ")"
- Else
- c(i) = "Cells(新设开始行 +" & (Range(a(i)).Row - 原始开始行) & ", 新设开始列 + " & (Range(a(i)).Column - 原始开始列) & ")"
- End If
- Next i
- For i = 0 To 单元格分组数量 - 1
- If InStr(a(i), ":") > 0 Then
- Print #1, "Range(" & c(i) & ").Merge"
- End If
- Next i
- For i = 0 To 单元格分组数量 - 1
- If InStr(a(i), ":") > 0 Then
- Print #1, "Range(" & c(i) & ").Interior.ColorIndex=" & Range(a(i)).Interior.ColorIndex ''填充颜色。
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeLeft).LineStyle =" & Range(a(i)).Borders(xlEdgeLeft).LineStyle ''//为左边上边框。
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeLeft).Weight =" & Range(a(i)).Borders(xlEdgeLeft).Weight
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeLeft).ColorIndex =" & Range(a(i)).Borders(xlEdgeLeft).ColorIndex
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeTop).LineStyle =" & Range(a(i)).Borders(xlEdgeTop).LineStyle '//为上边上边框。
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeTop).Weight =" & Range(a(i)).Borders(xlEdgeTop).Weight
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeTop).ColorIndex =" & Range(a(i)).Borders(xlEdgeTop).ColorIndex
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeBottom).LineStyle =" & Range(a(i)).Borders(xlEdgeBottom).LineStyle '//为下边上边框。
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeBottom).Weight =" & Range(a(i)).Borders(xlEdgeBottom).Weight
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeBottom).ColorIndex =" & Range(a(i)).Borders(xlEdgeBottom).ColorIndex
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeRight).LineStyle =" & Range(a(i)).Borders(xlEdgeRight).LineStyle '//为右边边上边框。
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeRight).Weight =" & Range(a(i)).Borders(xlEdgeRight).Weight
- Print #1, "Range(" & c(i) & ").Borders(xlEdgeRight).ColorIndex =" & Range(a(i)).Borders(xlEdgeRight).ColorIndex
- Else
- Print #1, c(i) & ".Interior.ColorIndex=" & Range(a(i)).Interior.ColorIndex ''填充颜色。
- Print #1, c(i) & ".Borders(xlEdgeLeft).LineStyle =" & Range(a(i)).Borders(xlEdgeLeft).LineStyle ''//为左边上边框。
- Print #1, c(i) & ".Borders(xlEdgeLeft).Weight =" & Range(a(i)).Borders(xlEdgeLeft).Weight
- Print #1, c(i) & ".Borders(xlEdgeLeft).ColorIndex =" & Range(a(i)).Borders(xlEdgeLeft).ColorIndex
- Print #1, c(i) & ".Borders(xlEdgeTop).LineStyle =" & Range(a(i)).Borders(xlEdgeTop).LineStyle '//为上边上边框。
- Print #1, c(i) & ".Borders(xlEdgeTop).Weight =" & Range(a(i)).Borders(xlEdgeTop).Weight
- Print #1, c(i) & ".Borders(xlEdgeTop).ColorIndex =" & Range(a(i)).Borders(xlEdgeTop).ColorIndex
- Print #1, c(i) & ".Borders(xlEdgeBottom).LineStyle =" & Range(a(i)).Borders(xlEdgeBottom).LineStyle '//为下边上边框。
- Print #1, c(i) & ".Borders(xlEdgeBottom).Weight =" & Range(a(i)).Borders(xlEdgeBottom).Weight
- Print #1, c(i) & ".Borders(xlEdgeBottom).ColorIndex =" & Range(a(i)).Borders(xlEdgeBottom).ColorIndex
- Print #1, c(i) & ".Borders(xlEdgeRight).LineStyle =" & Range(a(i)).Borders(xlEdgeRight).LineStyle '//为右边边上边框。
- Print #1, c(i) & ".Borders(xlEdgeRight).Weight =" & Range(a(i)).Borders(xlEdgeRight).Weight
- Print #1, c(i) & ".Borders(xlEdgeRight).ColorIndex =" & Range(a(i)).Borders(xlEdgeRight).ColorIndex
- End If
- Next i
- For i = 0 To 单元格分组数量 - 1
- If InStr(a(i), ":") > 0 Then
- If Len(b(i)) > 0 Then
- MyText = Replace(b(i), """", """""")
- MyText = Replace(MyText, Chr(10), """ & Chr(10) & """)
- Print #1, "Range(" & c(i) & ").FormulaR1C1=""" & MyText & """"
- End If
- MyText = Replace(Range(a(i)).NumberFormatLocal, """", """""")
- Print #1, "Range(" & c(i) & ").NumberFormatLocal=""" & MyText & """"
- Print #1, "Range(" & c(i) & ").Font.Name=""" & Range(a(i)).Font.Name & """"
- Print #1, "Range(" & c(i) & ").Font.Size=" & Range(a(i)).Font.Size
- Print #1, "Range(" & c(i) & ").Font.Color =" & Range(a(i)).Font.Color
- Print #1, "Range(" & c(i) & ").Font.Bold =" & Range(a(i)).Font.Bold
- Print #1, "Range(" & c(i) & ").Font.Italic =" & Range(a(i)).Font.Italic
- Print #1, "Range(" & c(i) & ").HorizontalAlignment=" & Range(a(i)).HorizontalAlignment
- Print #1, "Range(" & c(i) & ").VerticalAlignment=" & Range(a(i)).VerticalAlignment
- Print #1, "Range(" & c(i) & ").WrapText=" & Range(a(i)).WrapText
- Else
- If Len(b(i)) > 0 Then
- MyText = Replace(b(i), """", """""")
- MyText = Replace(MyText, Chr(10), """ & Chr(10) & """)
- Print #1, c(i) & ".FormulaR1C1=""" & MyText & """"
- End If
- MyText = Replace(Range(a(i)).NumberFormatLocal, """", """""")
- Print #1, c(i) & ".NumberFormatLocal=""" & MyText & """"
- Print #1, c(i) & ".Font.Name=""" & Range(a(i)).Font.Name & """"
- Print #1, c(i) & ".Font.Size=" & Range(a(i)).Font.Size
- Print #1, c(i) & ".Font.Color =" & Range(a(i)).Font.Color
- Print #1, c(i) & ".Font.Bold =" & Range(a(i)).Font.Bold
- Print #1, c(i) & ".Font.Italic =" & Range(a(i)).Font.Italic
- Print #1, c(i) & ".HorizontalAlignment=" & Range(a(i)).HorizontalAlignment
- Print #1, c(i) & ".VerticalAlignment=" & Range(a(i)).VerticalAlignment
- Print #1, c(i) & ".WrapText=" & Range(a(i)).WrapText
- End If
- Next i
- For i = 0 To 单元格分组数量 - 1
- Next i
- Print #1, "Application.DisplayAlerts = True' 打开警告提示"
- Print #1, "Application.ScreenUpdating=True'打开屏幕刷新"
- Print #1, "Application.Calculation = xlAutomatic'将手工计算改为自动计算。"
- Print #1, " End Sub"
- Close #1
- Application.VBE.ActiveVBProject.VBComponents.Import f '导入格式复制
- Kill f
- Application.DisplayAlerts = True ' 打开警告提示
- Application.ScreenUpdating = True '打开屏幕刷新
- Application.Calculation = xlAutomatic '将手工计算改为自动计算。
- End Sub
复制代码 |
|