When applying GetRate UDF to multiple cells at once some cells becomes "0" result!!

[orkhan.gasimli]

Hello everyone!!

I need your help. I've written UDF called GetRate, which gets the exchange rates from the site of National Bank, based on Currency Name and Date. This function works well when I apply this to one cell, but when I try to apply this function to the multiple cells at once, it returns sometimes "0" for some cells. I can not solve this problem by myself, therefor I need your help dear Excel Gurus.

Here is the code of GetRate function:

Please Login or Register  to view this content.

Excel file whith UDF is in below attached file:

GetRate.xlsm



http://www.mrexcel.com/forum/excel-q...-0-result.html

[millz]

Hi, I tried changing the formula for one of the 0's, for example cell C11, to " =GetRate(B11,A11) ", and it's working fine.

[orkhan.gasimli]

You are right. When I do this manually for each cell with 0 result, it works. But this is annoying when you have thousand of cells

[orkhan.gasimli]

Please guys! I need your help!

[orkhan.gasimli]

[romperstomper]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[orkhan.gasimli]

No one wants to solve my problem, that is why I've posted in other forum.

[orkhan.gasimli]

[orkhan.gasimli]

Please somebody help!

[JBeaucaire]

I can't solve this problem, but I can shed some light on what is happening and why it fails when applied enmasse.

Most UDFs operate within Excel's own memory, even very long complex calculations carried out very quickly in memory and the results fed back to the cell.

Your UDF does not operate that way, it actually creates a connection to an external document type, fills that document, then extract a piece of information from that document, then feeds that back to the cell before destroying the object it created.

That's fine for a one-off, but when you have Excel trigger a bunch of those at the same time, it just gets lost in the resource overload. Many of the the cells simply fail as a result, while you can come back to those cells later and reapply the formula and it works fine, because once again it's a one-off.

So, you're going to have to live with this behavior if you're applying this particular UDF to lots of cells, it's the nature of that beast.

[orkhan.gasimli]

I can't solve this problem, but I can shed some light on what is happening and why it fails when applied enmasse.

Most UDFs operate within Excel's own memory, even very long complex calculations carried out very quickly in memory and the results fed back to the cell.

Your UDF does not operate that way, it actually creates a connection to an external document type, fills that document, then extract a piece of information from that document, then feeds that back to the cell before destroying the object it created.

That's fine for a one-off, but when you have Excel trigger a bunch of those at the same time, it just gets lost in the resource overload. Many of the the cells simply fail as a result, while you can come back to those cells later and reapply the formula and it works fine, because once again it's a one-off.

So, you're going to have to live with this behavior if you're applying this particular UDF to lots of cells, it's the nature of that beast.

Thank you a lot! This is very helpfull comment and I try to apply this approach. I hope I will be able to fix this problem. Thanks again!

[orkhan.gasimli]

I can't solve this problem, but I can shed some light on what is happening and why it fails when applied enmasse.

Most UDFs operate within Excel's own memory, even very long complex calculations carried out very quickly in memory and the results fed back to the cell.

Your UDF does not operate that way, it actually creates a connection to an external document type, fills that document, then extract a piece of information from that document, then feeds that back to the cell before destroying the object it created.

That's fine for a one-off, but when you have Excel trigger a bunch of those at the same time, it just gets lost in the resource overload. Many of the the cells simply fail as a result, while you can come back to those cells later and reapply the formula and it works fine, because once again it's a one-off.

So, you're going to have to live with this behavior if you're applying this particular UDF to lots of cells, it's the nature of that beast.

I want to mention one more point. I have created my function based on the similar function which extracts the data from the site of Russian Central Bank. This function works well also when applied enmasse. Here is the code of this function:

Please Login or Register  to view this content.

[Kyle123]

If I were to put money on it, I'd say that the website limits the amount of requests in a given time period either to prevent DoS attacks or to prevent this type of automated scraping in vast quantities. My advice would be to write a loop to pull all the data in a macro, check the results and if the expected results aren't found then pause the macro for a second or two before continuing

[orkhan.gasimli]

If I were to put money on it, I'd say that the website limits the amount of requests in a given time period either to prevent DoS attacks or to prevent this type of automated scraping in vast quantities. My advice would be to write a loop to pull all the data in a macro, check the results and if the expected results aren't found then pause the macro for a second or two before continuing

Thank you for your advice. I've tried to do so, but I'm doing something wrong. Could you please help me?

[orkhan.gasimli]

Please heeelp!!!

[protonLeah]

Have you considered DoEvents or Application.Wait before/after the load?

[orkhan.gasimli]

Have you considered DoEvents or Application.Wait before/after the load?

Please Login or Register  to view this content.

I've rewritten my code in this way, but I'm still getting zeros. Am I missing something?

[orkhan.gasimli]

Noone wants to help mee