gpt4 book ai didi

excel - 使用 VBA 在 Excel 中刷新对 VBProject.VBComponents 所做的更改

转载 作者:IT王子 更新时间:2023-10-28 23:32:34 28 4
gpt4 key购买 nike

在以编程方式删除模块然后从文件中重新导入它们时,我在 Excel 中遇到了一些奇怪的问题。基本上,我有一个名为 VersionControl 的模块,它应该将我的文件导出到预定义的文件夹,并根据需要重新导入它们。这是重新导入的代码(问题如下所述):

Dim i As Integer
Dim ModuleName As String
Application.EnableEvents = False
With ThisWorkbook.VBProject
For i = 1 To .VBComponents.Count
If .VBComponents(i).CodeModule.CountOfLines > 0 Then
ModuleName = .VBComponents(i).CodeModule.Name
If ModuleName <> "VersionControl" Then
If PathExists(VersionControlPath & "\" & ModuleName & ".bas") Then
Call .VBComponents.Remove(.VBComponents(ModuleName))
Call .VBComponents.Import(VersionControlPath & "\" & ModuleName & ".bas")
Else
MsgBox VersionControlPath & "\" & ModuleName & ".bas" & " cannot be found. No operation will be attempted for that module."
End If
End If
End If
Next i
End With

运行后,我注意到有些模块不再出现,而有些模块有重复(例如 mymodule 和 mymodule1)。在单步执行代码时,很明显,一些模块在 Remove 调用之后仍然存在,并且它们仍然在项目中时被重新导入。有时,这只会导致模块以 1 为后缀,但有时我同时拥有原件和副本。

有没有办法刷新对 RemoveImport 的调用,以便它们自己应用?我正在考虑在每个之后调用一个 Save 函数,如果 Application 对象中有一个函数,尽管如果在导入过程中出现问题,这可能会导致损失。

想法?

编辑:将标签 synchronization 更改为 version-control

最佳答案

这是一个实时数组,您在迭代期间添加和删除项目,从而更改索引号。尝试向后处理数组。这是我的解决方案,没有任何错误处理:

Private Const DIR_VERSIONING As String = "\\VERSION_CONTROL"
Private Const PROJ_NAME As String = "PROJECT_NAME"

Sub EnsureProjectFolder()
' Does this project directory exist
If Len(Dir(DIR_VERSIONING & PROJ_NAME, vbDirectory)) = 0 Then
' Create it
MkDir DIR_VERSIONING & PROJ_NAME
End If
End Sub

Function ProjectFolder() As String
' Ensure the folder exists whenever we try to access it (can be deleted mid execution)
EnsureProjectFolder
' Create the required full path
ProjectFolder = DIR_VERSIONING & PROJ_NAME & "\"
End Function

Sub SaveCodeModules()

'This code Exports all VBA modules
Dim i%, sName$

With ThisWorkbook.VBProject
' Iterate all code files and export accordingly
For i% = 1 To .VBComponents.count
' Extract this component name
sName$ = .VBComponents(i%).CodeModule.Name
If .VBComponents(i%).Type = 1 Then
' Standard Module
.VBComponents(i%).Export ProjectFolder & sName$ & ".bas"
ElseIf .VBComponents(i%).Type = 2 Then
' Class
.VBComponents(i%).Export ProjectFolder & sName$ & ".cls"
ElseIf .VBComponents(i%).Type = 3 Then
' Form
.VBComponents(i%).Export ProjectFolder & sName$ & ".frm"
ElseIf .VBComponents(i%).Type = 100 Then
' Document
.VBComponents(i%).Export ProjectFolder & sName$ & ".bas"
Else
' UNHANDLED/UNKNOWN COMPONENT TYPE
End If
Next i
End With

End Sub

Sub ImportCodeModules()
Dim i%, sName$

With ThisWorkbook.VBProject
' Iterate all components and attempt to import their source from the network share
' Process backwords as we are working through a live array while removing/adding items
For i% = .VBComponents.count To 1 Step -1
' Extract this component name
sName$ = .VBComponents(i%).CodeModule.Name
' Do not change the source of this module which is currently running
If sName$ <> "VersionControl" Then
' Import relevant source file if it exists
If .VBComponents(i%).Type = 1 Then
' Standard Module
.VBComponents.Remove .VBComponents(sName$)
.VBComponents.Import fileName:=ProjectFolder & sName$ & ".bas"
ElseIf .VBComponents(i%).Type = 2 Then
' Class
.VBComponents.Remove .VBComponents(sName$)
.VBComponents.Import fileName:=ProjectFolder & sName$ & ".cls"
ElseIf .VBComponents(i%).Type = 3 Then
' Form
.VBComponents.Remove .VBComponents(sName$)
.VBComponents.Import fileName:=ProjectFolder & sName$ & ".frm"
ElseIf .VBComponents(i%).Type = 100 Then
' Document
Dim TempVbComponent, FileContents$
' Import the document. This will come in as a class with an increment suffix (1)
Set TempVbComponent = .VBComponents.Import(ProjectFolder & sName$ & ".bas")

' Delete any lines of data in the document
If .VBComponents(i%).CodeModule.CountOfLines > 0 Then .VBComponents(i%).CodeModule.DeleteLines 1, .VBComponents(i%).CodeModule.CountOfLines

' Does this file contain any source data?
If TempVbComponent.CodeModule.CountOfLines > 0 Then
' Pull the lines into a string
FileContents$ = TempVbComponent.CodeModule.Lines(1, TempVbComponent.CodeModule.CountOfLines)
' And copy them to the correct document
.VBComponents(i%).CodeModule.InsertLines 1, FileContents$
End If

' Remove the temporary document class
.VBComponents.Remove TempVbComponent
Set TempVbComponent = Nothing

Else
' UNHANDLED/UNKNOWN COMPONENT TYPE
End If
End If
Next i
End With

End Sub

关于excel - 使用 VBA 在 Excel 中刷新对 VBProject.VBComponents 所做的更改,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8855996/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com