Wenn ein VBA-Projekt an einen oder mehrere Nutzer übergeben wurde werden Änderungen mühsam. Da die Nutzer in ihre Arbeitsmappe Daten eingetragen haben, müssen Code Änderungen kopiert werden. Weil der VBA-Editor dafür nur sehr bedingt geeignete Mittel zur Verfügung stellt, bleibt nur: Modul für Modul aus der Entwickler-Arbeitsmappe exportieren, in der Nutzer-Arbeitsmappe löschen und neu importieren. Ich habe dafür ein VBA-Projekt in einer dedizierten Arbeitsmappe CoMMa.xlsm erstellt, die - im Normalfall selber unsichtbar - einen Nutzerdialog anzeigt, der Export, Import, Remove, Synchronisation, Transfer und Code-Cleanup für beliebig viele Moduln in geöffneten Arbeitsmappen unterstützt. Die Aktionen sind auch für VBA-Code in Daten Moduln (Arbeitsmappe und Arbeitsblätter) möglich. Da für diese Export, Remove Import nicht möglich ist, werden alle VBA-Code-Zeilen einzeln kopiert. Eine typische Aktion stellt sich wie folgt dar:
1. Schritt: Operation und Quell- und Ziel-Arbeitsmappe auswählen
2. Schritt: Code Moduln auswählen und Operation ausführen
3. Schritt: Ergebnis Log ansehen
Erwähnen möchte ich noch, dass ein neues/zusätzliches Arbeitsblatt komplett transferiert/synchronisiert werden kann, solange es in der Ziel-Arbeitsmappe noch nicht existiert - ohne dass dabei Rückwärtslinks zum Quell-Arbeitsblatt enstehen!
Wer sich mit dieser Materie schon mal auseinander gesetzt hat und an meiner Problemlösung interessiert ist, ist herzlich eingeladen die Arbeitsmappe zu verwenden und mit Feedback nicht zu sparen. Der Quellcode in der Arbeitsmappe ist leider zu umfangreich um ihn hier direkt zu publizieren. Für diejenigen, die sich an solch einer Löung selbst versuchen wollen oder sogar ein VBA-Projekt erstellen möchten, welches sich selbst aktualisiert, hier die beiden entralen Prozeduren:
1. Schritt: Operation und Quell- und Ziel-Arbeitsmappe auswählen
2. Schritt: Code Moduln auswählen und Operation ausführen
3. Schritt: Ergebnis Log ansehen
Erwähnen möchte ich noch, dass ein neues/zusätzliches Arbeitsblatt komplett transferiert/synchronisiert werden kann, solange es in der Ziel-Arbeitsmappe noch nicht existiert - ohne dass dabei Rückwärtslinks zum Quell-Arbeitsblatt enstehen!
Wer sich mit dieser Materie schon mal auseinander gesetzt hat und an meiner Problemlösung interessiert ist, ist herzlich eingeladen die Arbeitsmappe zu verwenden und mit Feedback nicht zu sparen. Der Quellcode in der Arbeitsmappe ist leider zu umfangreich um ihn hier direkt zu publizieren. Für diejenigen, die sich an solch einer Löung selbst versuchen wollen oder sogar ein VBA-Projekt erstellen möchten, welches sich selbst aktualisiert, hier die beiden entralen Prozeduren:
Quellcode
- Public Function CoMReplaceLbL(ByVal wbkTarget As Workbook, _
- Optional ByVal sExportFile As String = vbNullString, _
- Optional ByVal wbkSource As Workbook = Nothing, _
- Optional ByRef sCoModName As String = vbNullString, _
- Optional ByRef lUpdated As Long = 0, _
- Optional ByRef lAdded As Long = 0, _
- Optional ByRef lDeleted As Long = 0, _
- Optional ByRef lSource As Long = 0, _
- Optional ByRef lTarget As Long = 0, _
- Optional ByRef sResult As String = vbNullString) As String
- ' -----------------------------------------------------------------------------
- ' Replaces the Code Module in 'wbkTarget' by one with the same name either
- ' provided through an 'sExportFile' (.bas or .cls) or through 'wbkSource' and
- ' 'sCoModName'. When an 'sExportFile' is provided, 'wbkSource' and 'sCoModName'
- ' are ignored and the 'sCoModName' is determined by the export files name.
- ' The function is able to replace its own Code Modules (except to Code Module
- ' this function resides) provided the source is an 'sExportFile'.
- ' The function returns a vbNullString or a short error message.
- ' lUpdated, lAdded, lDeleted, lSource, lTarget, sResult are optional return
- ' values.
- ' See also: http://www.excelforum.com/excel-programming-vba-macros/1116013-when-programmaticaly-remove-replace-code-modules-doesnt-work.html
- '
- ' Autohor: W. Rauschenberger, Berlin Dec 2015
- ' -------------------------------------------------------------------------------------
- ' ------------- Should be adjusted when applicable !!!!! ----------
- Const sMyOwnCoM As String = "clsCoMMa" ' The Code Module in which this function resides
- Const sDebugName As String = "CoMReplaceLbL" ' My own name (to be replaced when changed)
- ' -----------------------------------------------------------------
- Const sVBAttribute As String = "Attribute VB_"
- Const sVbName As String = "Attribute VB_Name = "
- Const sTempSuffix As String = "_temp_" ' Temorary Source Code Module's name suffix
- Const sComment As String = "'"
- Const sBackSlash As String = "\"
- Const sDot As String = "."
- Const sPhcom As String = "<com>" ' Placeholder for error messages below
- Const sPhwbk As String = "<wbk>" ' Placeholder for error messages below
- Const sPhfile As String = "<file>" ' Placeholder for error messages below
- Const sMsgCoMExcl As String = "When the target Workbook is 'ThisWorkbook', '<com>' cannot be replaced!"
- Const sMsgFileError As String = "The provided file '<file>' does not exist!"
- Const sMsgNoCoM As String = "No Code Module's name is provided!"
- Const sMsgNoExport As String = "'<file>' is not an exported Code Module!"
- Const sMsgNoSource As String = "Neither an 'sExportFile' nor a 'wbkSource' provided!"
- Const sMsgNoSrcCoM As String = "The Code Module '<com>' not exists in source Workbook '<wbk>'!"
- Const sMsgNoTarget As String = "No Target Workbook provided!"
- Const sMsgNoTrgtCoM As String = "Code Module '<com>'not exists in target Workbook '<wbk>'!"
- Const sMsgSrcIsTrgt As String = "Source and Target Workbbok cannot be the same!"
- Const sMsgUsrFrm As String = "Code Module '<com>' must not be UserForm!"
- Const sLogNoChange As String = "(nothig changed, source and target were already equal)"
- Const sLogAdded As String = "(<a> lines added)"
- Const sSkp As String = " unchanged" ' Log line yet not implemented
- Const sRep As String = " replaced" ' Log line yet not implemented
- Const sAdd As String = " added" ' Log line yet not implemented
- Const sDel As String = " deleted" ' Log line yet not implemented
- Dim asFile() As String
- Dim asLines() As String
- Dim bRenamed As Boolean
- Dim bReplaceDirect As Boolean ' TRUE when no 'sExportFile' is provided
- Dim i As Long
- Dim lToBeReplaced As Long
- Dim lCoMType As enCompType
- Dim lUnchngd As Long
- Dim sComma As String
- Dim sErr As String ' Return error message
- Dim sLineNew As String
- Dim sLineOld As String
- Dim sTempFile As String
- Dim sTempCoMod As String
- Dim tsFile As TextStream
- Dim vbcSource As VBComponent
- Dim vbcTarget As VBComponent
- Dim vbcmSource As VBIDE.CodeModule
- Dim vbcmTarget As VBIDE.CodeModule
- On Error GoTo on_error
- CoMReplaceLbL = vbNullString
- If Len(sExportFile) <> 0 Then
- '~~> Terminate when 'sExportFile' doesn't exist
- With New FileSystemObject
- If Not .FileExists(sExportFile) Then
- CoMReplaceLbL = Replace(sMsgFileError, sPhfile, sExportFile)
- Exit Function
- End If
- End With
- '~~> Extract the Code Module's name from the provided 'sExportFile'
- asFile = Split(sExportFile, sBackSlash)
- sCoModName = asFile(UBound(asFile))
- asFile = Split(sCoModName, sDot)
- sCoModName = asFile(LBound(asFile))
- '~~>Terminate if no Target Workboook provided
- If wbkTarget Is Nothing Then
- CoMReplaceLbL = sMsgNoTarget
- Exit Function
- End If
- '~~> Terminate when target is ThisWorkbbok and 'sCoModName is the excluded CoM
- If wbkTarget.Name = ThisWorkbook.Name _
- And sCoModName = sMyOwnCoM Then
- CoMReplaceLbL = Replace(sMsgCoMExcl, sPhcom, sCoModName)
- Exit Function
- End If
- '~~>Terminate when the 'sCoModName' derived from 'sExportFile' does not exist in 'wbkTarget'
- Set vbcTarget = Nothing
- On Error Resume Next
- Set vbcTarget = wbkTarget.VBProject.VBComponents(sCoModName)
- If vbcTarget Is Nothing Then
- sErr = Replace(sMsgNoTrgtCoM, sPhcom, sCoModName)
- CoMReplaceLbL = Replace(sErr, sPhwbk, wbkTarget.Name)
- Exit Function
- End If
- Else
- '~~> Terminate when neither a 'sExportFile' nor a 'wbkSource' is provided
- If wbkSource Is Nothing Then
- CoMReplaceLbL = sMsgNoSource
- Exit Function
- End If
- '~~> Terminate when 'wbkTarget' is identical with 'wbkSource'
- If wbkSource.Name = wbkTarget.Name Then
- CoMReplaceLbL = sMsgSrcIsTrgt
- Exit Function
- End If
- '~~> Terminate when no 'sCoModName' is provided
- If Len(sCoModName) = 0 Then
- CoMReplaceLbL = sMsgNoCoM
- Exit Function
- End If
- '~~> Terminate when target is ThisWorkbbok and 'sCoModName' is the excluded CoM
- If wbkTarget.Name = ThisWorkbook.Name _
- And sCoModName = sMyOwnCoM Then
- CoMReplaceLbL = Replace(sMsgCoMExcl, sPhcom, sCoModName)
- Exit Function
- End If
- ' -------------------------------------------------------------------------------
- ' When no 'sExportFile' is provided the code lines from 'wbkSource'.'sCoModName'
- ' are directly used for replacing the lines in the target Workbook's Code Module.
- ' -------------------------------------------------------------------------------
- '~~> Terminate when the 'sCoModName' does not exist in 'wbkSource'
- Set vbcSource = Nothing
- On Error Resume Next
- Set vbcSource = wbkSource.VBProject.VBComponents(sCoModName)
- If vbcSource Is Nothing Then
- sErr = Replace(sMsgNoSrcCoM, sPhcom, sCoModName)
- CoMReplaceLbL = Replace(sErr, sPhwbk, wbkSource.Name)
- Exit Function
- End If
- Set vbcmSource = vbcSource.CodeModule
- bReplaceDirect = True
- End If
- '~~> Terminate when the 'sCoModName' derived from 'sExportFile' does not exist in the 'wbkTarget'
- Set vbcTarget = Nothing
- On Error Resume Next
- Set vbcTarget = wbkTarget.VBProject.VBComponents(sCoModName)
- If vbcTarget Is Nothing Then
- sErr = Replace(sMsgNoTrgtCoM, sPhcom, sCoModName)
- CoMReplaceLbL = Replace(sErr, sPhwbk, wbkTarget.Name)
- Exit Function
- End If
- '~~> Terminate when 'sCoModName' is a UserForm
- If vbcTarget.Type = vbext_ct_MSForm Then
- CoMReplaceLbL = Replace(sMsgUsrFrm, sPhcom, sCoModName)
- Exit Function
- End If
- Set vbcmTarget = vbcTarget.CodeModule
- If Not bReplaceDirect Then
- ' ----------------------------------------------------------------------------
- ' When the new code is provided through an export file, the content cannot be
- ' used directly to line-wise replace the target Code Module. The content is
- ' copied into a temporary file by replacing the name in 'Attribute VB_Name = '
- ' by a temporary Code Module name and 'out-commenting' all code lines. This
- ' modified export file is imported under the temporary Code Module name, now
- ' not causing any code conflicts. When this code lines are used to replace
- ' those of the target Code Module, the comment sign is removed before.
- ' ----------------------------------------------------------------------------
- With New FileSystemObject
- '~~> Get the content of the sExportFile into the array asLines
- Set tsFile = .OpenTextFile(sExportFile, ForReading)
- If tsFile.AtEndOfStream Then Erase asLines Else asLines = Split(tsFile.ReadAll, vbLf)
- Set tsFile = Nothing
- End With
- sTempCoMod = sCoModName & sTempSuffix
- sTempFile = Replace(sExportFile, sCoModName, sTempCoMod)
- bRenamed = False
- With New FileSystemObject
- If .FileExists(sTempFile) Then .DeleteFile sTempFile
- With .CreateTextFile(sTempFile)
- For i = LBound(asLines) To UBound(asLines) - 1 ' Drop last (always empty) line to prevent duplication
- sLineNew = asLines(i)
- If Len(sLineNew) > 1 Then sLineNew = Left(sLineNew, Len(sLineNew) - 1) Else sLineNew = vbNullString
- If Not bRenamed Then
- ' ------------------------------------------------------------------
- ' All lines before the VBName had been replaced and all lines after
- ' when starting with 'Attribute VB_' are transferred unchanched.
- ' ------------------------------------------------------------------
- If Mid$(sLineNew, 1, Len(sVbName)) = sVbName Then
- '~~> Modify name in VBAttribute line
- sLineNew = Replace(asLines(i), sCoModName, sTempCoMod)
- sLineNew = Left(sLineNew, Len(sLineNew) - 1)
- .WriteLine sLineNew
- bRenamed = True
- End If
- If Not bRenamed Then .WriteLine sLineNew ' write lines before the vbname line as is!
- ElseIf Mid$(sLineNew, 1, Len(sVBAttribute)) = sVBAttribute Then
- '~~> Write VBAttribute line unmodified
- .WriteLine sLineNew
- Else
- '~~> Modify line to become a comment line
- .WriteLine sComment & sLineNew
- End If
- Next i
- .Close
- End With
- If Not bRenamed Then
- ' -------------------------------------------------------------------------------------
- ' When no 'Attribute VB_Name' line was found to rename the Code Module, the export file
- ' is not regarded as one. The temp file is deleted and the function is terminated.
- ' -------------------------------------------------------------------------------------
- .DeleteFile sTempFile
- CoMReplaceLbL = Replace(sMsgNoExport, sPhfile, sExportFile)
- Exit Function
- End If
- End With
- '~~> Import the modified export file under the temporary Code Module name
- wbkTarget.VBProject.VBComponents.Import sTempFile
- Set vbcmSource = wbkTarget.VBProject.VBComponents(sTempCoMod).CodeModule
- ' Set vbcmSource = vbcSource.CodeModule
- bReplaceDirect = False
- End If
- ' -------------------------------------------------------------
- ' With the lines of this 'image' of the new Code Modules's code
- ' update the existing Code Module by un-commenting each line.
- ' -------------------------------------------------------------
- lAdded = 0
- lUpdated = 0
- lDeleted = 0
- lUnchngd = 0
- lTarget = vbcmTarget.CountOfLines
- lSource = vbcmSource.CountOfLines
- If lSource < lTarget Then lToBeReplaced = lSource Else lToBeReplaced = lTarget
- With vbcmTarget
- '~~> Replace all 'old' lines with the 'new' lines
- For i = 1 To lToBeReplaced
- sLineNew = vbcmSource.Lines(i, 1)
- sLineOld = vbcmTarget.Lines(i, 1)
- If Not bReplaceDirect Then
- '~~> Un-Comment line
- If Len(sLineNew) <= 1 Then sLineNew = "" Else sLineNew = Mid(sLineNew, 2)
- End If
- If Trim(sLineNew) <> Trim(sLineOld) Then
- .ReplaceLine i, sLineNew
- lUpdated = lUpdated + 1
- Else
- lUnchngd = lUnchngd + 1
- End If
- Next i
- '~~> Add new lines which exceed the number of 'old' lines
- While .CountOfLines < lSource
- sLineNew = vbcmSource.Lines(.CountOfLines + 1, 1)
- If Not bReplaceDirect Then
- '~~> Un-Comment line
- If Len(sLineNew) <= 1 Then sLineNew = " " Else sLineNew = Mid(sLineNew, 2)
- End If
- .InsertLines .CountOfLines + 1, sLineNew
- lAdded = lAdded + 1
- Wend
- '~~> Delete excessive lines
- If .CountOfLines > lSource Then
- lDeleted = .CountOfLines - lSource
- .DeleteLines lSource + 1, lDeleted
- End If
- End With
- sResult = vbNullString
- sComma = vbNullString
- If lUpdated = 0 And lAdded = 0 And lDeleted = 0 Then
- If lTarget <> 0 Then
- sResult = sResult & "no lines changed"
- sComma = ", "
- End If
- End If
- If lUpdated <> 0 Then
- sResult = sResult & lUpdated & " of " & lTarget & " target lines updated"
- sComma = ", "
- End If
- If lAdded <> 0 Then
- sResult = sResult & sComma & lAdded & " of " & lSource & " source lines added to target"
- sComma = ", "
- ElseIf lDeleted <> 0 Then
- sResult = sResult & sComma & lDeleted & " lines deleted from target"
- End If
- If Not bReplaceDirect Then
- ' -------------------------------------------------------------------------------------------
- ' When replacing the target code lines had not been done directly with the source code lines,
- ' the temporary imported Code Module and the temporary file are removed.
- ' -------------------------------------------------------------------------------------------
- wbkTarget.VBProject.VBComponents.Remove wbkTarget.VBProject.VBComponents(sTempCoMod)
- With New FileSystemObject
- If .FileExists(sTempFile) Then .DeleteFile sTempFile
- End With
- End If
- Exit Function
- on_error:
- Debug.Print "Error in '" & sMyOwnCoM & sDot & sDebugName & "'!"
- wbkTarget.VBProject.VBComponents.Remove wbkTarget.VBProject.VBComponents(sTempCoMod)
- With New FileSystemObject
- If .FileExists(sTempFile) Then .DeleteFile sTempFile
- End With
- End Function
- Public Function WshAddToWrkbk(ByVal wbkSource As Workbook, _
- ByVal wbkTarget As Workbook, _
- ByVal sCodeName As String) As Boolean
- ' -----------------------------------------------------------------
- ' Copies the sheet named 'sCodeName' from the 'wbkSource' to the
- ' 'wbkTarget' Workbook. Prevents back-links to the source Workbook
- ' by moving the sheet from a temporary copy. Removes all code lines
- ' in the temporary Worksheet before close to prevent compile errors
- ' because of the moved sheet. Returns FALSE when a sheet named
- ' 'sCodeName' already exists in 'wbkTarget'.
- ' -----------------------------------------------------------------
- Const sNameRefErr1 As String = "='#" ' Indicator for a name error
- Const sNameRefErr2 As String = "=#" ' Indicator for a name error
- Const sWbkNmTmpSffx As String = "_temp_"
- Const sDot As String = "."
- Dim bEvents As Boolean
- Dim nm As Name
- Dim nmSrc As Name
- Dim sWbkExt As String
- Dim sWbkNm As String
- Dim sWbkNmTmp As String
- Dim sWbkNmTmpFull As String
- Dim vbc As VBComponent
- Dim wbkTemp As Workbook
- Dim wsh As Worksheet
- Dim cllNames As Collection
- Dim sWshName As String
- Dim sWshBaseCname As String
- Dim wshTemp As Worksheet
- Dim bWshProtected As Boolean
- On Error GoTo on_error
- WshAddToWrkbk = False
- For Each wsh In wbkTarget.Sheets
- '~~> Exit with False when the sheet already exists in the target Workbook
- If wsh.CodeName = sCodeName Then Exit Function
- Next wsh
- '~~> Remove any name errors
- For Each nm In Application.Names
- If Left(nm.Value, Len(sNameRefErr1)) = sNameRefErr1 Or _
- Left(nm.Value, Len(sNameRefErr2)) = sNameRefErr2 Then
- nm.Delete
- End If
- Next nm
- '~~> Create a temporary source Workbook copy
- With wbkSource
- sWbkNm = Left(.Name, (InStrRev(.Name, sDot, -1, vbTextCompare) - 1))
- sWbkExt = Right(.Name, Len(.Name) - (InStrRev(.Name, sDot, -1, vbTextCompare) - 1))
- sWbkNmTmp = sWbkNm & sWbkNmTmpSffx & sWbkExt
- sWbkNmTmpFull = Replace(.FullName, .Name, sWbkNmTmp)
- With New FileSystemObject
- If .FileExists(sWbkNmTmpFull) Then .DeleteFile sWbkNmTmpFull
- End With
- .SaveCopyAs sWbkNmTmpFull
- End With
- Application.DisplayAlerts = False
- bEvents = Application.EnableEvents
- Application.EnableEvents = False
- Set wbkTemp = Workbooks.Open(sWbkNmTmpFull)
- With wbkTemp
- '~~> Add a spare sheet when the to be moved one is the only sheet in the source Workbook
- If .Sheets.Count = 1 Then .Sheets.Add
- '~~> Move the sheet to the target Workbook
- For Each wsh In .Sheets
- If wsh.CodeName = sCodeName Then
- wsh.Move After:=wbkTarget.Sheets(wbkTarget.Sheets.Count)
- Exit For
- End If
- Next wsh
- '~~> Remove all code lines in the temporary Workbook and close it
- For Each vbc In .VBProject.VBComponents
- With vbc.CodeModule
- If .CountOfLines > 0 Then .DeleteLines .CountOfLines
- End With
- Next vbc
- .Close SaveChanges:=False
- End With
- Application.EnableEvents = bEvents
- '~~> Remove the temprary Workbook
- With New FileSystemObject
- .DeleteFile sWbkNmTmpFull
- End With
- WshAddToWrkbk = True
- Exit Function
- on_error:
- Debug.Print "Error in 'clsCoMMa.WshAddToWrkbk'! (" & sCodeName & ")"
- End Function