在VBA中有这段代码能运行,换在VB做com加载里怎么做?一直提示没定义,本人是新手不知道应在VB里如何设置
Sub 汇总计算()
On Error Resume Next
'If Target.Row >= 4 And Target.Column = 6 Or Target.Column = 7 Then
Application.ScreenUpdating = False
Call 计算表
Call 特征
Call 计算
Dim i%, rng, x&
r = Sheets("计算表").[g65536].End(xlUp).Row + 1
Set d = [J:J].Find("√", LookIn:=xlValues)
n1 = d.Row
n2 = n1
Do
Set d = [J:J].FindNext(d)
If d.Row <> n1 Then
Cells(n2, 7) = Application.Sum(Range(Cells(n2 + 1, 7), Cells(d.Row - 1, 7))) / Cells(n2, 11)
n2 = d.Row
Else
Cells(n2, 7) = Application.Sum(Range(Cells(n2 + 1, 7), Cells(r, 7))) / Cells(n2, 11)
End If
Loop While Not d Is Nothing And d.Row <> n1
Sheets("计算表").Range("i1:z3000").Value = ""
Application.ScreenUpdating = True
MsgBox "OK,计算已完成!", vbInformation, "提示您!"
'End If
End SubSub 特征()
On Error Resume Next
Dim RegEx, Myr&, x&, A, b, bb
Set RegEx = CreateObject("VBSCRIPT.REGEXP") 'RegEx为建立正则表达式
RegEx.Global = True '设置全局可用
RegEx.Pattern = "[^!-~]" '留汉字样式
Myr = [b3000].End(xlUp).Row
For x = 4 To Myr
A = Cells(x, 2)
b = RegEx.Replace(A, "*") '把匹配样式的字符用*替换
bb = Replace(b, "*", "") '把*用""替换
If Len(A) > Len(bb) Then
Cells(x, 10) = "√"
End If
Next x
Set RegEx = Nothing
End Sub
Sub 计算()
On Error Resume Next
'Application.ScreenUpdating = False
Dim i As Integer, arr1
Sheet3.Range("g4:g3000").Value = ""
i = Sheets("计算表").[d6200].End(xlUp).Row
Range("d4:d" & i).Replace What:="~*", replacement:="×"
Range("d4:d" & i).Replace What:="/", replacement:="÷"
For i = 4 To Sheets("计算表").[d6200].End(xlUp).Row
If Cells(i, 5) <> "" Then Cells(i, 11) = Val(Cells(i, 5)) * 1 '单位取值
If Cells(i, 11) = 0 Or Cells(i, 11) = "" Then Cells(i, 11) = 1
If Cells(i, "d") <> "" And Cells(i, "b") = "" Then
str1 = Cells(i, "d")
str1 = Replace(str1, "×", "*")
str1 = Replace(str1, "÷", "/")
str1 = Replace(str1, "[", "(")
str1 = Replace(str1, "]", ")")
100:
A = InStr(str1, "{")
If A > 0 Then
str1 = Mid(str1, 1, A - 1) & Mid(str1, InStr(str1, "}") + 1, 99)
GoTo 100
End If
[m1] = "=" & str1
If Cells(i, "f") = "" Then
Cells(i, "g") = [m1]
Else
Cells(i, "g") = [m1] * Cells(i, "f")
End If
End If
Next i
Set d = Nothing
'Application.ScreenUpdating = True
End Sub
Sub 汇总计算()
On Error Resume Next
'If Target.Row >= 4 And Target.Column = 6 Or Target.Column = 7 Then
Application.ScreenUpdating = False
Call 计算表
Call 特征
Call 计算
Dim i%, rng, x&
r = Sheets("计算表").[g65536].End(xlUp).Row + 1
Set d = [J:J].Find("√", LookIn:=xlValues)
n1 = d.Row
n2 = n1
Do
Set d = [J:J].FindNext(d)
If d.Row <> n1 Then
Cells(n2, 7) = Application.Sum(Range(Cells(n2 + 1, 7), Cells(d.Row - 1, 7))) / Cells(n2, 11)
n2 = d.Row
Else
Cells(n2, 7) = Application.Sum(Range(Cells(n2 + 1, 7), Cells(r, 7))) / Cells(n2, 11)
End If
Loop While Not d Is Nothing And d.Row <> n1
Sheets("计算表").Range("i1:z3000").Value = ""
Application.ScreenUpdating = True
MsgBox "OK,计算已完成!", vbInformation, "提示您!"
'End If
End SubSub 特征()
On Error Resume Next
Dim RegEx, Myr&, x&, A, b, bb
Set RegEx = CreateObject("VBSCRIPT.REGEXP") 'RegEx为建立正则表达式
RegEx.Global = True '设置全局可用
RegEx.Pattern = "[^!-~]" '留汉字样式
Myr = [b3000].End(xlUp).Row
For x = 4 To Myr
A = Cells(x, 2)
b = RegEx.Replace(A, "*") '把匹配样式的字符用*替换
bb = Replace(b, "*", "") '把*用""替换
If Len(A) > Len(bb) Then
Cells(x, 10) = "√"
End If
Next x
Set RegEx = Nothing
End Sub
Sub 计算()
On Error Resume Next
'Application.ScreenUpdating = False
Dim i As Integer, arr1
Sheet3.Range("g4:g3000").Value = ""
i = Sheets("计算表").[d6200].End(xlUp).Row
Range("d4:d" & i).Replace What:="~*", replacement:="×"
Range("d4:d" & i).Replace What:="/", replacement:="÷"
For i = 4 To Sheets("计算表").[d6200].End(xlUp).Row
If Cells(i, 5) <> "" Then Cells(i, 11) = Val(Cells(i, 5)) * 1 '单位取值
If Cells(i, 11) = 0 Or Cells(i, 11) = "" Then Cells(i, 11) = 1
If Cells(i, "d") <> "" And Cells(i, "b") = "" Then
str1 = Cells(i, "d")
str1 = Replace(str1, "×", "*")
str1 = Replace(str1, "÷", "/")
str1 = Replace(str1, "[", "(")
str1 = Replace(str1, "]", ")")
100:
A = InStr(str1, "{")
If A > 0 Then
str1 = Mid(str1, 1, A - 1) & Mid(str1, InStr(str1, "}") + 1, 99)
GoTo 100
End If
[m1] = "=" & str1
If Cells(i, "f") = "" Then
Cells(i, "g") = [m1]
Else
Cells(i, "g") = [m1] * Cells(i, "f")
End If
End If
Next i
Set d = Nothing
'Application.ScreenUpdating = True
End Sub
我这里有个简单的COM加载的例子,你先看看。
http://blog.caozhongyan.com/files/exceladdins.rar
Dim excel_app As Object
'建立 Excel 应用程序
Set excel_app = CreateObject("Excel.Application")
'然后用excel_app对象,引用VBA中的EXCEL对象属性
r = excel_app.Sheets("计算表").[g65536].End(xlUp).Row + 1
大体上是这样~~~~~~看看吧
现在就是计算部分,根据二楼的方法还是提示 r、d、n1、n2没有定义,在特征这部分提示Myr 没定义,希望大家能提供一些方法,谢谢各位!
http://www.bccn.net/Article/kfyy/vb/jc/200511/1204.html