Question

ExecuteExcel4Macro 提取超过255个字符会显示#VALUE! 如何修改？

ExecuteExcel4Macro 提取超过255个字符的时候，显示#VALUE! 如何修改代码？【由于代码已经设置了路径，所以请将下载的实例test.xlsm放置到E盘根目录下。】Sub DataInput255()Dim path As String, file As String, sheet As Stringpath = "E:\"file = "test.xlsx"sheet = "test1"For r = 1 To 10 '1-10行For c = 1 To 10 'a-j列Cells(r, c) = ExecuteExcel4Macro("'" & path & "[" & file & "]" & sheet & "'!" & Cells(r, c).Address(, , xlR1C1))Next c, rEnd Sub





Answer 1

下面是用VBA在excel中定义的宏，可以用快捷键呼叫来分析被点击的任意单元格的内容并给出答案。不仅对数字有效，更对所有文字符号都有效。已经调试过了，对你的例子也有效。

Sub BaiduGame()
'
' 分析当前活动单元格内文本，结果1:得到不含重复字符的文本放在NoSameCharTxt，结果2:得到重复字符的列表SameCharList，不破坏任何单元格原内容。
' Macro recorded 5/31/2008 by CNYIZHO
'
' Keyboard Shortcut: Ctrl+m
'
Dim txt1 As String, Schar As String, SameCharList As String, NoSameCharTxt As String
NoSameCharTxt = ""
SameCharList = ""
txt1 = ActiveCell '取当前活动单元格内文本到给定串变量中
For I = 1 To Len(txt1) '
Schar = Mid(txt1, I, 1) '从I位置采样字符

'将采样字符加入非重复字符文本
If InStr(1, NoSameCharTxt, Schar) = 0 Then NoSameCharTxt = NoSameCharTxt & Schar

'如果被采样字符在原文本中出现两次以上，将采样字符补入重复字符表
If InStr(1, Left(txt1, I - 1), Schar) Or (InStr(1, Right(txt1, Len(txt1) - I), Schar)) Then
If InStr(1, SameCharList, Schar) = 0 Then SameCharList = SameCharList & Schar
End If
Next I
MsgBox "当前活动单元格内容:" & txt1 & Chr(10) & Chr(13) & "处理后不含重复字符的文本:" & NoSameCharTxt & Chr(10) & Chr(13) & " 重复字符为:" & SameCharList, vbOKOnly

'也可以存入指定单元例如: Cells(m,n)=NoSameCharTxt

End Sub