VBA 操作案例

Option Explicit
Private Sub Workbook_Open()
    Dim wb_model As Workbook
    Dim wb_factor As Workbook
    Dim wb_asset As Workbook
    Dim wb_guzhi As Workbook
    Dim model_sht As Worksheet
    Dim factor_sht As Worksheet
    Dim sht
    Dim asset_sht As Worksheet
    Dim guzhi_sht As Worksheet
    Dim equ_sht As Worksheet
    Dim bond_sht As Worksheet
    Dim deriv_sht As Worksheet
    Dim fund_sht As Worksheet
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim l As Long
    Dim m As Long
    Dim n As Long
    Dim o As Long
    Dim num
    Dim count As Long
    Dim count_equ As Long
    Dim code As Variant
    Dim code4 As String
    Dim asset_name As String
    Dim guzhi_name As String
    Dim Filename As String
    Dim cell As Range
    Dim waittime
    
    '打开要使用的工作表
    Set wb_model = Workbooks.Open(Application.ActiveWorkbook.Path & "\【公式模板】.xlsm")
    Set wb_factor = Workbooks.Open(Application.ActiveWorkbook.Path & "\【资产要素登记表】.xlsx")
    asset_name = InputBox("请输入持仓信息表名")
    guzhi_name = InputBox("请输入估值表名")
    Set wb_asset = Workbooks.Open(Application.ActiveWorkbook.Path & "\" & asset_name & ".xls")
    Set wb_guzhi = Workbooks.Open(Application.ActiveWorkbook.Path & "\" & guzhi_name & ".xls")
    Set factor_sht = wb_factor.Sheets("资产要素登记")
    Set asset_sht = wb_asset.Sheets(1)
    Set guzhi_sht = wb_guzhi.Sheets(1)
    Set equ_sht = wb_model.Sheets("资产要素登记 (权益)")
    Set bond_sht = wb_model.Sheets("资产要素登记 (债券)")
    Set deriv_sht = wb_model.Sheets("资产要素登记（衍生品）")
    Set fund_sht = wb_model.Sheets("资产要素登记（基金）")
    
    '将模板里的颜色都去掉，方便后面统一着色
    For sht = 1 To wb_model.Worksheets.count
        On Error Resume Next
        wb_model.Worksheets(sht).UsedRange.Interior.ColorIndex = 2
    Next sht
    
    '将资产要素登记表模板里内容删除
    Call clearcells(factor_sht.UsedRange)
    
    num = InputBox("B类6期: 1；强债1号：2；D类3期: 3；智慧象1号：4", "请输入您要更新的产品库序号")  '选定库
    Set model_sht = wb_model.Sheets(CInt(num))
    model_sht.Cells(model_sht.UsedRange.Rows.count, 1).Interior.ColorIndex = 50                '在上次更新的末尾处标记颜色
    '设定计数器
    j = 2
    k = 2
    l