vba IsNumeric 函数为空单元格返回 true
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18225497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IsNumeric function returning true for an empty cell
提问by user2681358
I run a macro that copies tables from a PDF file and saves them on Excel. some of the tables contain empty cells and in my analysis I need to know the number of cells that are empty. I have a function that iterates through each column to check if the value within that cell is numeric or not. the trouble is when I run this function on an empty cell it returns true. I even tried manually cheeking the cells using the Isblank() function and it returns "false". (if I try this on any cell outside the pasted range it returns "true")
我运行一个从 PDF 文件复制表格并将它们保存在 Excel 中的宏。一些表格包含空单元格，在我的分析中，我需要知道空单元格的数量。我有一个函数可以遍历每一列以检查该单元格中的值是否为数字。问题是当我在一个空单元格上运行这个函数时它返回 true。我什至尝试使用 Isblank() 函数手动调整单元格，它返回“false”。（如果我在粘贴范围之外的任何单元格上尝试此操作，它将返回“true”）
I am guessing that when I copy and paste things from PDF it somehow pastes some value for the empty cells.
我猜当我从 PDF 复制和粘贴内容时，它会以某种方式为空单元格粘贴一些值。
did anyone ever encounter a similar problem? if so, any ideas on how it can be solved?
有没有人遇到过类似的问题？如果是这样，关于如何解决它的任何想法？
if it is any help here is the code I use to copy and paste
如果有任何帮助，这里是我用来复制和粘贴的代码
'Initialize Acrobat by creating App object
Set PDFApp = CreateObject("AcroExch.App")
'Set AVDoc object
Set PDFDoc = CreateObject("AcroExch.AVDoc")
'Open the PDF
If PDFDoc.Open(PDFPath, "") = True Then
PDFDoc.BringToFront
'Maximize the document
Call PDFDoc.Maximize(True)
Set PDFPageView = PDFDoc.GetAVPageView()
'Go to the desired page
'The first page is 0
Call PDFPageView.GoTo(DisplayPage - 1)
'-------------
'ZOOM options
'-------------
'0 = AVZoomNoVary
'1 = AVZoomFitPage
'2 = AVZoomFitWidth
'3 = AVZoomFitHeight
'4 = AVZoomFitVisibleWidth
'5 = AVZoomPreferred
'Set the page view of the pdf
Call PDFPageView.ZoomTo(2, 50)
End If
Set PDFApp = Nothing
Set PDFDoc = Nothing
On Error Resume Next
'Show the adobe application
PDFApp.Show
'Set the focus to adobe acrobat pro
AppActivate "Adobe Acrobat Pro"
'Select All Data In The PDF File's Active Page
SendKeys ("^a"), True
'Right-Click Mouse
SendKeys ("+{F10}"), True
'Copy Data As Table
SendKeys ("c"), True
'Minimize Adobe Window
SendKeys ("%n"), True
'Select Next Paste Cell
Range("A" & Range("A1").SpecialCells(xlLastCell).Row).Select
'Cells(1, 1).Select
'Paste Data In This Workbook's Worksheet
ActiveSheet.Paste
采纳答案by user2681358
There are cases where it is better to check the lengthof the characters inside cells instead of using the isNumeric(), or check for errors etc...
在某些情况下，最好检查单元格内字符的长度而不是使用isNumeric(), 或检查错误等...
For example try the below code
例如尝试下面的代码
it establishes the Range used in the active worksheet then iterates through checking the length (len()) of each cell
它建立活动工作表中使用的范围，然后通过检查每个单元格的长度 (len()) 进行迭代
you can look at Immediate Window CTRL+Gin VBE to see which cell addresses are emptyorwait until the macro finishes executing and you will be welcomed with a Message Box saying how many empty cells are within the range
您可以在 VBE 中查看Immediate Window CTRL+G以查看哪些单元格地址为空或等到宏完成执行，您将收到一个消息框，说明该范围内有多少个空单元格
Option Explicit
Sub CheckForEmptyCells()
Dim lastCol As Range
Set lastCol = ActiveSheet.Cells.Find(What:="*", After:=ActiveSheet.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
Dim rng As Range
Set rng = Range("A1:" & lastCol.Address)
Dim cnt As Long
cnt = 0
Dim cell As Range
For Each cell In rng
If Len(cell) < 1 Then
Debug.Print cell.Address
cnt = cnt + 1
End If
Next
MsgBox "there are " & cnt & " empty cells within the range " & rng.Address
End Sub
回答by Red_Riot
This is kind of a hackish solution, but works as a simple solution.
这是一种骇人听闻的解决方案，但可以作为一个简单的解决方案。
Since 'IsNumeric(p variant)' uses a variant, you can append a "-" to the input parameter. That means null gets interpreted as "-" which is not a number, where as a true number will get treated as a negative number, and thereby meet the condition of truly being a number. (although now negative)
由于 'IsNumeric(p variant)' 使用变体，您可以在输入参数后附加“-”。这意味着 null 被解释为“-”，它不是数字，而真正的数字将被视为负数，从而满足真正是数字的条件。（虽然现在是负面的）
IsNumeric("-" & string_Value)vs. IsNumeric(str_Value)
IsNumeric("-" & string_Value)与IsNumeric(str_Value)
回答by varocarbas
I have checked it with an empty cell right now (without involving a PDF file at all) and you are right: IsNumericreturns Truefor empty cells.
我现在用一个空单元格检查了它（根本不涉及 PDF 文件），你是对的：IsNumeric返回True空单元格。
I haven't ever had this problem because, when coding, I intend to not bring the in-built functions "to its limits" (determining whether an empty cell can be considered as numeric or not might be even discussion-worthy). What I do always before performing any kind of analysis on a cell (or a string in general) is making sure that it is not empty:
我从来没有遇到过这个问题，因为在编码时，我不打算将内置函数“发挥到极致”（确定一个空单元格是否可以被视为数字可能甚至值得讨论）。在对单元格（或一般的字符串）执行任何类型的分析之前，我总是确保它不为空：
Dim valIsNumeric As Boolean
If (Not IsEmpty(Range("A1"))) Then
valIsNumeric = IsNumeric(Range("A1"))
End If
Or in a more generic version (highly reliable with any kind of string under any circumstance):
或者在更通用的版本中（在任何情况下对任何类型的字符串都高度可靠）：
If (Len(Trim(Range("A1").Value))) Then
valIsNumeric = IsNumeric(Range("A1"))
End If
Making sure that the given cell/string is not blank represents just a small bit of code and increases appreciably the reliability of any approach.
确保给定的单元格/字符串不是空白仅代表一小部分代码，并显着提高了任何方法的可靠性。
