从单元格中获取值作为格式

I am using VBA to pull in a bunch of data from several workbooks into several worksheets and then process that data later.
WorkFlow: As I pull the data into my workbook, I first place all my data into arrays before outputting them into my worksheets.

我正在使用VBA将一堆数据从几个工作簿中提取到几个工作表中，然后再处理这些数据。 WorkFlow：当我将数据拉入我的工作簿时，我首先将所有数据放入数组，然后将它们输出到我的工作表中。

Problem

While I am pulling in data, I noticed that some of the "Hours" or "Work Order Number" data are pulled into my arrays in the form of dates or datetime, when they should just be some number format.
e.g.:
I should be getting: Number of Hours: 2; Work Order Number: 41235612
Instead, I am getting: Number of Hours: 0-Jan-00 (equivalent to 1/0/1900, which was originally entered as "0") Work Order Number: 0-Jan-00
I found that the issue lies in the source documents, where it looks like Excel may have automatically (although incorrectly) converted those cell values into a date type form, when first entered as a plain number.

当我提取数据时，我注意到一些“小时”或“工作订单号”数据以日期或日期时间的形式被提取到我的数组中，当时它们应该是某种数字格式。例如：我应该得到：小时数：2;工单号：41235612相反，我得到：小时数：0-Jan-00（相当于1/0/1900，最初输入为“0”）工单号：0-Jan-00我发现问题出在源文档中，当首次作为普通数字输入时，Excel可能会自动（尽管错误地）将这些单元格值转换为日期类型形式。

Question

How can I pull data from the different documents into my arrays so that all values in that column come out as what they should be (a Number)?
- Is there a way to format the value before placing it into the array?
  - Can I format the value into a number format like the "General" number format (or something that makes sense) while it is being pulled?
  - 在拉动时，我可以将值格式化为数字格式，如“常规”数字格式（或有意义的内容）吗？
- 有没有办法在将值放入数组之前格式化值？在拉动时，我可以将值格式化为数字格式，如“常规”数字格式（或有意义的内容）吗？
如何将不同文档中的数据提取到我的数组中，以便该列中的所有值都以它们应该的形式出现（数字）？有没有办法在将值放入数组之前格式化值？在拉动时，我可以将值格式化为数字格式，如“常规”数字格式（或有意义的内容）吗？

I would rather not make any changes within the source documents, and just format the values I am pulling as I pull them.

我宁愿不在源文档中进行任何更改，只需格式化我拉动它们时的值。

1 个解决方案

#1

you can use Range().value2 instead of .value property

您可以使用Range（）。value2而不是.value属性

the .value2 dont accept Currency and Date formats, so it should read your hours values properly.

.value2不接受货币和日期格式，因此它应该正确读取您的小时值。

for example cell A1 has value 2 and saved with Date format.

例如，单元格A1的值为2，并以日期格式保存。

so Range("A1").value will return date value "1/2/1900"

所以范围（“A1”）。值将返回日期值“1/2/1900”

Range("A1").value2 will return numeric value 2 you can save in your array.

范围（“A1”）。value2将返回您可以保存在数组中的数值2。

You can format the values with Clng() or CDbl(). for example:

您可以使用Clng（）或CDbl（）格式化值。例如：

CDbl(Range("A1").Value)

But if the cell has non numeric value, for example text, code throws an error!

但是如果单元格具有非数值，例如文本，则代码会抛出错误！

And you can format the cells (source or destination) with .NumberFormat property

您可以使用.NumberFormat属性格式化单元格（源或目标）

Range("A1").NumberFormat = "General"

#1