VBA IsError() Showing a false positive. Advice needed

[Sp1ral0u1]

Hello,

First, thanks everyone on here for all the great info. This forum has saved me many months of haggle over time. I am running into an issue that I can't seem to figure out as well as find any information specific to this circumstance that I'm aware of after several days of searching and debugging. So thanks ahead of time for any insight.

Currently, I am working on a Workbook that inserts an employee name to a manager named range as well as copies over formulas that look for values linked to workbooks in SharePoint. Example formula: =CONCATENATE('https://SharePoint Site/LASTNAME FIRSTNAME - 2015 ATTENDANCE.xlsm'!Employee_Number," - ",'https://SharePoint Site/LASTNAME FIRSTNAME - 2015 ATTENDANCE.xlsm'!Employee_Last_Name," ",'https://SharePoint Site/LASTNAME FIRSTNAME - 2015 ATTENDANCE.xlsm'!Employee_First_Name," - ",'https://SharePoint Site/LASTNAME FIRSTNAME - 2015 ATTENDANCE.xlsm'!Cost_Center)

So ultimately this will pull the data from the workbook and concatenate the data in the cell field. What I am trying to do is catch an error in the event the employee name is misspelled, delete the record and end the Sub. This all works fine in theory. When the employee record gets inserted with the formula that gets updated with employee info, if the employee name is misspelled, then the cell will show an error in the form of #NAME! which will trigger the IsError function in VBA as True otherwise False and move on. So what is happening is that it is firing as a false positive and trying to end the function. When I toggle a breakpoint to check what the range value is prior to is exiting the sub, it shows as the cell.value as the correct value and not an error even though the it is being read as an error. Also, this error does not occur if I F8 and step through each line by line. My theory is that is checking the cell field so fast that it is not allowing for the time it takes the UI to make the quick calculation to update the cells with Employee data from SharePoint. So there is probably a split second timeframe where the cell automatically results in a #NAME! error before the updated info. Does this make sense? If so, is there a way around it? I know there is something I'm not seeing or a better way to go about it.. Again thanks for any input. Code and example as follows below:

Please Login or Register  to view this content.

Error.JPG

[GC Excel]

Hello and welcome to the forum,

Why not test by adding a small delay in your code:

Please Login or Register  to view this content.

[Sp1ral0u1]

Thanks for the reply GC Excel. After more debugging, it does appear that the cell value shows as a #NAME? Error for a split second and is causing the error. The is error function is catching it prior to the change. I had previously attempted to use the Application.wait thinking this would allow for the time needed to show the correct cell value but it proved otherwise. Unless I'm using it wrong but I've used it plenty in the past... Another strange aspect is that this only happens when I enter the employee name the first time. The second time it works fine so I'm note sure what it's actually doing. Maybe a memory issue I'm not accounting for? I don't know.....

[Sp1ral0u1]

Well, so I went the route I didn't really want to go but it works. Instead of matching, in string, the employee name with the cell value to validate the reference link is valid, and validates the name was spelled correctly at the same time,
I used a loop process to compare each each excel file in the SharePoint folder to the employee name string variable. If a match was found, then I just set a boolean variable as true and use that to trigger whether to end the Sub or continue the process. It takes a few seconds longer but it works. I'm under a time crunch and a deliverable is required. I will have to revisit the IsError issue at a later time.

Please Login or Register  to view this content.