Excel VBA 的新手:创建字符串时出现 #Value 错误

Question

对不起，我认为错误是基本的，但我不确定我做错了什么。

我正在尝试编写一个函数，该函数采用单元格并将标记为红色的字符转换为小写。我通过在新变量中重建字符串来做到这一点。

然后返回此重建的字符串。但是，当我尝试在 Excel 中对字符串使用此函数时，它会返回 #Value 错误。代码没有编译错误，所以我不知所措。任何帮助将不胜感激。

Function Convert_Red(rng As Range)
If (rng.Cells.Count >1) Then
AcceptOneCell = "Only allow 1 cell"
Exit Function
End If
Dim i As Long
Dim text As String
Dim new_text As String
Dim placeholder As String
text = rng.Cells(1, 1).Value
For i = 1 To Len(text)
If rng.Cells(1, 1).Characters(Start:=i, Length:=1).Font.Color  vbRed 
Then
new_text = new_text + LCase(rng.Cells(1, 1).Characters(Start:=i, 
Length:=1))
Else
new_text = new_text + rng.Cells(1, 1).Characters(Start:=i, Length:=1)
End If
i = i + 1
Next
Convert_Red = new_text
End Function

Answer 1

我猜你当前的代码是什么，因为发布的代码无法编译。 我相信您正在追求以下几点：

Function Convert_Red(rng As Range)
If (rng.Cells.Count > 1) Then
'Use the correct function name when returning a result
'AcceptOneCell = "Only allow 1 cell"
Convert_Red = "Only allow 1 cell"
Exit Function
End If
Dim i As Long
Dim text As String
Dim new_text As String
Dim placeholder As String
text = rng.Cells(1, 1).Value
For i = 1 To Len(text)
'Fix syntax errors
'If rng.Cells(1, 1).Characters(Start:=i, Length:=1).Font.Color  vbRed
'Then
If rng.Cells(1, 1).Characters(Start:=i, Length:=1).Font.Color = vbRed Then
' 1) Fix syntax errors
' 2) Use & for string concatenation
' 3) A Characters object has no default property - specify Text
'new_text = new_text + LCase(rng.Cells(1, 1).Characters(Start:=i, 
'Length:=1))
new_text = new_text & LCase(rng.Cells(1, 1).Characters(Start:=i, Length:=1).Text)
Else
' 1) Use & for string concatenation
' 2) A Characters object has no default property - specify Text
'new_text = new_text + rng.Cells(1, 1).Characters(Start:=i, Length:=1)
new_text = new_text & rng.Cells(1, 1).Characters(Start:=i, Length:=1).Text
End If
'Don't mess with the loop counter
'i = i + 1
Next
Convert_Red = new_text
End Function

请注意，如果源范围包含公式、数值或日期，则使用Characters将不起作用。

Answer 2

试试这个：

Option Explicit
Public Function LCaseReds(rng As Range)
Dim i As Long, ltr As String, adr As String, result As String
Select Case True
Case rng.Cells.Count > 1: result = "Use only one cell"
Case Len(rng.Value2) = 0: result = "Empty cell: '" & rng.Address(False, False) & "'"
Case Else:
For i = 1 To Len(rng)
With rng.Characters(Start:=i, Length:=1)
ltr = .Text
result = result + IIf(.Font.Color = vbRed, LCase(ltr), ltr)
End With
Next
End Select
LCaseReds = result
End Function

请注意，使用多个区域(C3、E3)时，您仍然会得到 #Value！