|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 doryan 于 2013-12-12 15:39 编辑
先发一份满足条件1和条件2的
手工开方的代码表现 LONG支持 double不支持
如楼主提示一般 自己重写了字符串形式的四则运算 来解决溢出问题
但由于算法优化不太懂 太多的数组和字符串操作 速度较慢 1000位都需要8秒 建议只测试到100位
另外指出楼主给出的参考是小数点后98位 100位应该是
1.4142135623730950488016887242096980785696718753769480731766797379907324784621070388503875343276415727- Public Function SQRT2(ByVal Length As Long, Optional ByRef dTime As Double) As String
- Dim t#: t = Timer
- On Error GoTo RTE
- SQRT2 = ss(Length, dTime)
- END_FUNC:
- t = Timer - t: dTime = t
- Debug.Print " ID: " & "<doryan>"
- Debug.Print " Time Used: " & Format(t, "0.00s")
- Debug.Print "Calculated: " & Length & " digits of square root 2."
- Exit Function
- RTE:
- Err.Clear: On Error GoTo 0
- dTime = -1
- End Function
- Private Function ss(num, n) As String
- Dim numstr As String
- Dim yushu As String
- Dim zhengshu As String, zsLen As Integer
- Dim gen As Long
- Dim kaifang As Long
- kaifang = Application.WorksheetFunction.RoundDown((VBA.Sqr(num)), 0)
- zhengshu = CStr(kaifang)
- zsLen = Len(zhengshu)
- gen = kaifang ^ 2
- yushu = (num - gen)
- ' If Len(num) Mod 2 <> 0 Then
- ' numstr = "0" & num & Application.WorksheetFunction.Rept("0", n * 2)
- ' Else
- ' numstr = "" & num & Application.WorksheetFunction.Rept("0", n * 2)
- ' End If
- Dim shishang As String
- Dim bigger As Integer
- Dim shiji As String
- For i = 1 To n
- yushu = yushu & "00"
- shishang = ChengFa(zhengshu & "", "", True)
- For j = CInt(chufa(yushu, shishang)) To 0 Step -1
- shiji = ChengFa(JiaFa(shishang, j & ""), j & "")
- bigger = CompareVal(yushu, shiji)
- If bigger = 1 Then
- zhengshu = zhengshu & j
- yushu = JianFa(yushu, shiji)
- Exit For
- ElseIf bigger = -1 Then
-
- Else
- zhengshu = zhengshu & j
- Exit For
- End If
- Next
- Next
- If n > 0 Then
- ss = Left(zhengshu, Len(CStr(kaifang))) & "." & Mid(zhengshu, Len(CStr(kaifang)) + 1)
- Else
- ss = CStr(kaifang)
- End If
- End Function
- Private Function chufa(a1 As String, a2 As String)
- Dim l1 As Long, l2 As Long
- Dim T1 As String, T2 As String
- l1 = Len(a1)
- l2 = Len(a2)
- If CompareVal(a1, a2) = -1 Then
- chufa = "0"
- Else
- T1 = Left(a1, l1 - (l2 - 1))
- T2 = Left(a2, 1)
- chufa = CInt(T1) / CInt(T2) + 1
- If chufa > 9 Then chufa = 9
- End If
- End Function
- Private Function ChengFa(a1 As String, a2 As String, Optional Twenty = False)
- If Twenty Then
- ChengFa = subChengFa(a1, 2) & "0"
- Else
- ChengFa = subChengFa(a1, a2)
- End If
- End Function
- Private Function subChengFa(a1 As String, a2 As String)
- Dim l1 As Long, l2 As Long, l3 As Long
- l1 = Len(a1) + 1
- l2 = Len(a2)
- Dim arr()
- ReDim arr(1 To l1)
- arr(1) = "0"
- For i = l1 To 2 Step -1
- arr(i) = Mid(a1, i - 1, 1) * CInt(a2)
- Next
- For i = l1 To 2 Step -1
- If Len(arr(i)) > 1 Then
- arr(i - 1) = CInt(arr(i - 1)) + CInt(Left(arr(i), 1))
- arr(i) = Right(arr(i), 1)
- End If
- Next
- Dim pos As Integer
- For i = 1 To l1
- If arr(i) = 0 Then
- pos = i
- Else
- Exit For
- End If
- Next
- If pos = l1 Then
- subChengFa = "0"
- Else
- subChengFa = Mid(Join(arr, ""), pos + 1)
- End If
- End Function
- Private Function JianFa(a1 As String, a2 As String)
- Dim l1 As Long, l2 As Long
- l1 = Len(a1)
- l2 = Len(a2)
- a2 = Application.WorksheetFunction.Rept("0", l1 - l2) & a2
- Dim arr()
- ReDim arr(1 To l1)
- For i = l1 To 1 Step -1
- arr(i) = Mid(a1, i, 1) - Mid(a2, i, 1)
- Next
- For i = l1 To 1 Step -1
- If arr(i) < 0 Then
- arr(i - 1) = arr(i - 1) - 1
- arr(i) = 10 + arr(i)
- End If
- Next
- Dim pos As Integer
- For i = 1 To l1
- If arr(i) = 0 Then
- pos = i
- Else
- Exit For
- End If
- Next
- If pos = l1 Then
- JianFa = "0"
- Else
- JianFa = Mid(Join(arr, ""), pos + 1)
- End If
- End Function
- Private Function JiaFa(a1 As String, a2 As String)
- Dim l1 As Long, l2 As Long
- l1 = Len(a1) + 1
- l2 = Len(a2)
- a2 = Application.WorksheetFunction.Rept("0", l1 - l2 - 1) & a2
- Dim arr()
- ReDim arr(1 To l1)
- arr(1) = "0"
- For i = l1 To 2 Step -1
- arr(i) = CInt(Mid(a1, i - 1, 1)) + CInt(Mid(a2, i - 1, 1))
- Next
- For i = l1 To 1 Step -1
- If Len(arr(i)) > 1 Then
- arr(i - 1) = arr(i - 1) + 1
- arr(i) = arr(i) - 10
- End If
- Next
- Dim pos As Integer
- For i = 1 To l1
- If arr(i) = 0 Then
- pos = i
- Else
- Exit For
- End If
- Next
- If pos = l1 Then
- JiaFa = "0"
- Else
- JiaFa = Mid(Join(arr, ""), pos + 1)
- End If
- End Function
- Private Function CompareVal(a1 As String, a2 As String)
- Dim l1 As Long, l2 As Long
- l1 = Len(a1)
- l2 = Len(a2)
- If l1 > l2 Then
- CompareVal = 1
- ElseIf l1 = l2 Then
- Dim m1 As Integer, m2 As Integer
- For i = 1 To l1
- m1 = CInt(Mid(a1, i, 1))
- m2 = CInt(Mid(a2, i, 1))
- If m1 > m2 Then
- CompareVal = 1
- Exit Function
- ElseIf m1 = m2 Then
- GoTo NEXTLOOP
- Else
- CompareVal = -1
- Exit Function
- End If
- NEXTLOOP:
- Next
- CompareVal = 1
- Else
- CompareVal = -1
- End If
- End Function
- Private Sub TimeCompare_4Secs()
- Dim i&, s$, t#
- t = Timer
- For i = 1 To 83500
- s = s & Chr(Int(Rnd * 10 + 48))
- Next
- s = ""
- Debug.Print Timer - t
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|