i有许多单元格,其中一些数字存储为文本,如下所示:48820912927088000170550010004765131620601995
但当我尝试将其转换为数字时,它会转换为4,8820912927088E+43
,无论我做什么,都尝试更改每种格式,但它不会作为原始值输出。
Sub convert ()
Dim rng As Range
Dim cl As Range
Dim rConst As Range
' pick an unused cell
Set rConst = Cells(1, 4)
rConst = 1
Set rng = Cells.SpecialCells(xlCellTypeConstants)
rng.NumberFormat = "General"
rConst.Copy
rng.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
rConst.Clear
End Sub
我知道有很多类似的答案,但我没有找到与我的问题相匹配的答案?
有人可以帮忙吗?
谢谢
下面的c#代码是一个如何访问BigInteger对象的示例,该对象是Net Systen.Numerics.
类
using System.Runtime.InteropServices;
using System.Numerics;
namespace BigStuff
{
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IBigNum))]
public class BigNum : IBigNum
{
public string Add(string ipVar1, string ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
BigInteger myVar2 = BigInteger.Parse(ipVar2);
return BigInteger.Add(myVar1, myVar2).ToString();
}
public string Sub(string ipVar1, string ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
BigInteger myVar2 = BigInteger.Parse(ipVar2);
return BigInteger.Subtract(myVar1, myVar2).ToString();
}
public string Mul(string ipVar1, string ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
BigInteger myVar2 = BigInteger.Parse(ipVar2);
return BigInteger.Multiply(myVar1, myVar2).ToString();
}
public string Div(string ipVar1, string ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
BigInteger myVar2 = BigInteger.Parse(ipVar2);
return BigInteger.Divide(myVar1, myVar2).ToString();
}
public string Mod(string ipVar1, string ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
BigInteger myVar2 = BigInteger.Parse(ipVar2);
return BigInteger.Remainder(myVar1, myVar2).ToString();
}
public string[] DivRem(string ipVar1, string ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
BigInteger myVar2 = BigInteger.Parse(ipVar2);
BigInteger myMod = 0;
BigInteger myDiv = BigInteger.DivRem(myVar1, myVar2, out myMod);
return new string[] { myDiv.ToString(), myMod.ToString() };
}
public string Neg(string ipVar1)
{
return BigInteger.Negate(BigInteger.Parse(ipVar1)).ToString();
}
public string Abs(string ipVar1)
{
return BigInteger.Abs(BigInteger.Parse(ipVar1)).ToString();
}
public int Compare(string ipVar1, string ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
BigInteger myVar2 = BigInteger.Parse(ipVar2);
return BigInteger.Compare(myVar1, myVar2);
}
public dynamic Pow(string ipVar1, int ipVar2)
{
BigInteger myVar1 = BigInteger.Parse(ipVar1);
return (dynamic)BigInteger.Pow(myVar1, ipVar2).ToString();
}
}
}
Com.所需的接口
using System;
using System.Collections.Generic;
using System.Linq;
using System.Numerics;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
namespace BigStuff
{
[ComVisible(true)]
public interface IBigNum
{
string Add(string ipVar1, string ipVar2);
string Sub(string ipVar1, string ipVar2);
string Mul(string ipVar1, string ipVar2);
string Div(string ipVar1, string ipVar2);
string Mod(string ipVar1, string ipVar2);
string[] DivRem(string ipVar1, string ipVar2);
string Neg(string ipVar1);
string Abs(string ipVar1);
int Compare(string ipVar1, string ipVar2);
dynamic Pow(string ipVar1, int ipVar2);
}
}
上面的代码是使用Framework 4.8.1编译到c#windows库中的。上面的代码需要参考";System.Numerics";添加。
编译代码后,在VBA中,您需要添加对BigStuff.tlb的引用,该引用将位于c#项目目录的BigStuff.bin.Debug文件夹中。
在VBA中,您现在可以执行类似的操作
Option Explicit
Sub Test()
Dim myInt As BigStuff.BigNum
Set myInt = New BigNum
Debug.Print myInt.Add("48820912927088000170550010004765131620601995", "2")
Dim myNum As String
myNum = myInt.Mul("9007199254740992", "4096")
Debug.Print myNum
End Sub
其给出输出
488209192708800017055001000047651316206019973689348814719103232
这个例子并不完整,也没有经过任何程度的测试,但如果你想开发自己的代码,这个例子将成为一个有用的指针。
要编译上面的c#示例,您至少需要安装Visual Studio Community。