gpt4 book ai didi

excel - VBE中的ReplaceLine方法仅替换部分行

转载 作者:行者123 更新时间:2023-12-02 07:58:27 25 4
gpt4 key购买 nike

回应this我认为编写一个 VBE 宏来自动替换看起来像这样的行会很有趣

DimAll a, b, c, d As Integer

Dim a As Integer, b As Integer, c As Integer, d As Integer

在我的初稿中,我只想修改单个选定的行。在建立适当的引用以访问 VBE 对象模型(请参阅 http://www.cpearson.com/excel/vbe.aspx )并进行一些尝试后,我想出了:

Function ExpandDim(codeLine As String) As String
Dim fragments As Variant
Dim i As Long, n As Long, myType As String
Dim last As Variant
Dim expanded As String

If UCase(codeLine) Like "*DIMALL*AS*" Then
codeLine = Replace(codeLine, "dimall", "Dim", , , vbTextCompare)
fragments = Split(codeLine, ",")
n = UBound(fragments)
last = Split(Trim(fragments(n)))
myType = last(UBound(last))
For i = 0 To n - 1 'excludes last fragment
expanded = expanded & IIf(i = 0, "", ",") & fragments(i) & " As " & myType
Next i
expanded = expanded & IIf(n > 0, ",", "") & fragments(n)
ExpandDim = expanded
Else
ExpandDim = codeLine
End If
End Function

Sub DimAll()
Dim myVBE As VBE
Dim startLine As Long, startCol As Long
Dim endLine As Long, endCol As Long
Dim myLine As String
Set myVBE = Application.VBE
myVBE.ActiveCodePane.GetSelection startLine, startCol, endLine, endCol
myLine = myVBE.ActiveCodePane.CodeModule.Lines(startLine, 1)
Debug.Print ExpandDim(myLine)
myVBE.ActiveCodePane.CodeModule.ReplaceLine startLine, ExpandDim(myLine)
End Sub

在我的另一个代码模块中:

Sub test()
DimAll a, b, c, d As Integer
Debug.Print TypeName(a)
Debug.Print TypeName(b)
Debug.Print TypeName(c)
Debug.Print TypeName(d)
End Sub

这是奇怪的部分。当我突出显示以 DimAll a 开头的行并调用我的笨拙命名的子 DimAll 时,我在立即窗口中看到

Dim a As Integer, b As Integer, c As Integer, d As Integer

这符合预期,但在代码模块本身中,该行更改为

Dim a, b, c, d As Integer

DimAll 已被 Dim 取代——但该行的其余部分未修改。我怀疑逗号混淆了 ReplaceLine 方法。关于如何解决这个问题有什么想法吗?

最佳答案

当我使用调试器运行时,myLine 会更改两个调用之间的值。 DimAll 在第二次通过时变为 Dim

这是因为在 ExpandDim 函数 中输入主 If 条件后,您将替换 codeLine 的值。

在该函数中创建一个新变量,您应该没问题...或者传递它 ByVal 就可以了:

Function ExpandDim(ByVal codeLine As String) As String

关于excel - VBE中的ReplaceLine方法仅替换部分行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30988331/

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