两个数组相乘-本地小数分隔符问题 [英] Multiplying two arrays - Local Decimal Seperator Issue

问题描述

_背景:

现在，我回答了一个问题，我必须将两个相等大小的数组相乘.但是，我发现本地十进制分隔符存在一个特殊问题.

Just now, I answered a question where I had to multiply two equal sized arrays. However I notice a particular issue with my local decimal seperator.

_{示例代码:}

Sub Sample()

Dim arr1 As Variant, arr2 As Variant, arr3 As Variant, arr4 As Variant

arr1 = Array(1, 2, 3)
arr2 = Array(0.4, 0.3, 0.2)

'Working method for static
arr3 = Evaluate("{1,2,3}*{0.4,0.3,0.2}")

'Failing method for dynamic
arr4 = Evaluate("{" & Join(arr1, ",") & "}*{" & Join(arr2, ",") & "}")

End Sub

---

_问题:

arr3 会正确填充，而 arr4 不会正确填充，并且会出现很多错误.原因是我的本地十进制分隔符.

Whereas arr3 would populate correctly, arr4 would not and will hold a lot of errors. Reason being my local decimal seperator.

只需一个简单的 Debug.Print arr2(1)将返回 0,4 .没什么大不了的，因为VBA仍然可以使用以本机(en_us)点作为小数点分隔符输入的变量.

Just a simple Debug.Print arr2(1) will return 0,4. Not much of a deal because VBA can still work with the variable that have been inputed with the native (en_us) dot as a decimal seperator.

问题是 Debug.Print Join(arr2，，")将返回: 0,5,0,4,0,3 .正如您所看到的，VBA本来需要使用的点现在都变成了逗号.轮流在 .evaluate 中使用自然点的情况下将无法正常工作.

The issue is that Debug.Print Join(arr2, ",") will return: 0,5,0,4,0,3. And as you can see the dots that VBA would natively need to work with have now all turned into commas. Which in it's turn will not work correctly in a .Evaluate that would expect the native dots.

_问题:

要解决此问题，在不更改 Application.DecimalSeperator 或编写一个字符串值的情况下(我现在已经克服了这个问题)，在动态检索数组的情况下，这不是很好吗?

Any way around this issue without changing Application.DecimalSeperator or writing a string value (what I did to overcome this now) which is not exactly great in case of a dynamically retrieved array?

根据请求，可以在此处找到链接的帖子，基本上可以归结为添加一些变量以插入书面值字符串，例如: str = {0.4,0.3,0.2}

As per request, the linked post can be found here which basically comes down to adding a few variables to insert a written string of values, eg.: str = {0.4,0.3,0.2}

我可以解决此问题的另一种方法是使用 TEXTJOIN ，它实际上代替了 Join 保留点:

Another way I could approach the issue is to use TEXTJOIN, which instead of Join actually retains the dots:

arr4 = Evaluate("{" & Application.TextJoin(",", True, arr1) & "}*{" & Application.TextJoin(",", True, arr2) & "}")

问题在于，并非每个人都可以使用该功能.因此，尽管这可以与具有不同本地设置的其他用户互换，但他们需要使用最新的Excel版本之一.

The problem is, not everyone will have access to the function. So while this would be exchangable to others with different local settings, they would need to have one of the latest Excel versions.

另一种使用的方法是遍历 arr2 ，将每个元素与 Cstr 联接在一起，但是这也会将点替换为逗号.

Another used method is looping through arr2, joining each element with Cstr however this would also swap the dot for a comma.

推荐答案

我的第一个猜测是Application.DecimalSeperator在arr4情况下不起作用的原因是Join()函数导致字符串，该字符串无法处理作为小数，因此该点不会被昏迷代替.

My first guess is that the reason Application.DecimalSeperator does not work in the case of arr4 is because Join() function results in a string, which is not handled as a decimal, therefore the dot is not replaced by a coma.

我认为下面的代码是这样运行的:

I assume that code below runs like this:

1. 首先，它从."转换十进制符号.到，"，包括arr2
2. 但是Join(arr2，;")会产生字符串因此Application.DecimalSeparator =."不起作用(提醒-它需要将数组项识别为数字以更改小数分隔符)

1. first it converts the decimal sign from "." to ",", including in arr2
2. However Join(arr2, ";") results in a string so Application.DecimalSeparator = "." does not work (reminder - it needs to recognize an array item as a number to change the decimal separator)

-要修复它，我们可以使用Replace()作为解决字符串的方法.

-to fix it, we can use Replace() as a workaround for string.

Sub Sample()

Dim arr1 As Variant, arr2 As Variant, arr3 As Variant, arr4 As Variant

arr1 = Array(1, 2, 3)
arr2 = Array(0.4, 0.3, 0.2)
Debug.Print arr2(1)
'Failing method for dynamic

arr4 = Application.WorksheetFunction.Transpose(Evaluate("{" & Join(arr1, ";") & "}*{" & Replace(Join(arr2, ";"), Application.DecimalSeparator, ".") & "}"))
Debug.Print Join(arr1, ";")
Debug.Print Join(arr2, ";")

End Sub

这篇关于两个数组相乘-本地小数分隔符问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！