Solved

Debugging a Currency Macro

Posted on 2013-11-17
46
128 Views
Last Modified: 2013-11-21
EE Pros,

This may not sound easy but I have this nice little conversion routine that was written by EE Pros.  When I have this on one computer it works great.  When I copy it and put it on another computer, I get a error in this module:  

Public Sub GetExchangeRate(strHTML As String)

and the error on this line:  

Range(strRangeName).Value = IIf(IsNumeric(Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1)) = True, Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1), 1)

I have been trying to trouble shoot this but am not having any luck.

Help!  Thanks in advance.......

B.
Currency-Fetch-and-conversion.xlsm
0
Comment
Question by:Bright01
  • 18
  • 14
  • 14
46 Comments
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
Comment Utility
Is the computer that is giving you the problem in a different country?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
If it is then change the line to this              

Range(strRangeName).Value = IIf(IsNumber(Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1)) = True, Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1), 1)

and add this neat function I found on the web.

Public Function IsNumber(ByVal Value As String) As Boolean
  Dim DP As String
  '   Get local setting for decimal point
  DP = Format$(0, ".")
  '   Leave the next statement out if you don't
  '   want to provide for plus/minus signs
  If Value Like "[+-]*" Then Value = Mid$(Value, 2)
  IsNumber = Not Value Like "*[!0-9" & DP & "]*" And Not Value Like "*" & DP & "*" & DP & "*" And Len(Value) > 0 And Value <> DP
End Function

Open in new window


Here is the source of the code.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hi Bright01,

The error you encountered on the line you mentioned is only a result of a previous error mishandled.

In the Sub OpenUrl
I changed this instruction
Dim sReadBuf As String * 2048

by this instruction (it need the buffer to be increased
Dim sReadBuf As String * 51200

Then also in the same sub when it was not reading anymore data it was concidering it an error where as it is normal to generate an error when there is no more data to read so I changed this instruction

If Err.LastDllError <> 0 Then

to this instruction
If Err.LastDllError <> 0 And bytesRead <> 0 Then

That's it.
Try the file and see if it give you the expected results. (nice file by the way)

gowflow
Currency-Fetch-and-conversion.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Gowflow!  Thank you!  Some smart EE guy wrote it ;-)

Can you try the file you sent me again?  I'm still getting the same error.

Thank you,

B.
0
 

Author Comment

by:Bright01
Comment Utility
MartinLiss,

Thanks also for jumping in.

It's in the same country.  However, people in different countries (particularly Europe) will be using it.  Do I need to incorporate your changes into the code to adapt to different locations?

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Do I need to incorporate your changes into the code to adapt to different locations?
I believe so, yes, but you can test as if you are in a different country by going to Control Panel|Regional and Language Options and then choose a different country.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
For sure I did try the file I sent you extensively !!! I do not post files unless tested thouroully. and it works here like a charm. You choose from the dropdown a curreny and it update the fields in Col A butiffully.

The question is:
What Excel version you have I have 2007, I presume maybe you have 2010 ??? or higher and it might be the problem but first pls advise.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
by the way, as I posted the file with the same name that you have you may incidentally are running your version by default and not the one I posted to make sure this is not happening I changed the name so try this file.
gowflow
Currency-Fetch-and-conversion-V1.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
I'm running 2010. Let me try it with your version renamed. Back with you shortly.

B
0
 

Author Comment

by:Bright01
Comment Utility
Gowflow,

I must have done something wrong here.  I've attached the version I'm having trouble with and the screenshots of the error.  I think I may have done something wrong here....... although I cannot figure it out.

Thank you for your help.

B.
Currency-Fetch-and-conversion-V2.xlsm
Error-Snapshots.pptx
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Just so that I don't need to hang around, did you try my change in post ID: 39654667 even though different countries aren't currently involved?
0
 

Author Comment

by:Bright01
Comment Utility
MartinLiss,

I thought I would get the code stablized before testing for "different countries".  I'm afraid that I've done something to screw up his response........ I appreciate your post.... let me work with Gowflow to get the code correct and then I'll test for locations.

OK?

Thanks,

B.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You did not do anything wrong !!! Excel 2010 did.

I do not have Excel 2010 on my machine but presume from the error you are getting something around the iif formula that 2010 for some reason do not digest. so I have proceeded to this workaround.

I have replaced this instruction
Range(strRangeName).Value = IIf(IsNumeric(Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1)) = True, Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1), 1)

Open in new window


by these instructions which is practically the same but in different function using if instead of iif
If IsNumeric(Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1)) = True Then
        Range(strRangeName).Value = Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1)
Else
        Range(strRangeName).Value = 1
End If

Open in new window


I saved the file under V3 try it out.
gowflow
Currency-Fetch-and-conversion-V3.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Gowflow,

You are a real trooper trying to solve this MS problem.  I am using Excel 2010 - 32 bit; I tried version 3 and got a new error;  SEE CHART 3.

Actual code error:  

                If IsNumeric(Mid(strTemp, 1, InStr(1, strTemp, "<", vbTextCompare) - 1)) = True Then
Error-Snapshots.pptx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
No problem.
You are getting this error at this specific line due to previous Internet error. I have put a code to bypass this routine in case your connection is not stable or got an error due to not finding site. It happened so that when I ran the file again I got the same error that you had and it appeared on my taks bar that I did not have internet.

Try this new version and let me know.
gowflow
Currency-Fetch-and-conversion-V4.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Gowflow,

Interesting development.  The code works flawlessly, except when I transfer it to a machine where I'm running a VPN line.  then when it updates, it hangs.  If I disconnect the VPN, it updates via the internet.  Is there a slight adjustment that will prevent it from hanging?  If not, I'll live with it.

Thanks and advise,

B.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Are you able to know where it hangs ? we can then fix it. When you feel it hangs just hit CTRL Break so it break the code and you will get a yellow line where it is hanging let me know what it is and I'll look at it.
gowflow
0
 

Author Comment

by:Bright01
Comment Utility
gowflow;  I have a CTRL button; but no button labeled "Break".  Any ideas?   Or can I step through this in a different way?

Thank you,

b.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Usually Break is in the same button as Pause it depend what keyboard you have a notebook keyb or desktop ?
try if you can find this button CTRL C
gowflow
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You can use the On Screen Keyboard to find the key.

Open On-Screen Keyboard by going to Start|All Programs|Accessories|Accessibility (or Ease of Access)|On-Screen Keyboard
0
 

Author Comment

by:Bright01
Comment Utility
gowflow,

I have a Ctrl but no Ctrl C Key.  I'm using a Lenovo T430 Laptop.  

MartinLiss,

I tried the On Screen Keyboard but still no "Break" Key.

See Attached Slides at the end for actual screen shots.

Thank you both,

B.
Error-Snapshots.pptx
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Take a look at this article.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
From the article.

If you would like to use those function keys, you can refer to below:
Break (FN + B)
SysRq (FN + S)
ScrLK (FN + K)
Pause (FN + P)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Bright01
Comment Utility
MartinLiss,

I feel like a "moron" at this point....... I start the macro, then Ctrl + FN + B and it doesn't stop.  It runs..... longer then usual, then does not complete the Macro.  Is there another way to step through the macro to see where it is "hanging"?   Again, the issue is when I have the VPN up and running, I get this macro misbehavior.  Without the VPN running the macro works just fine.

B.
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
Comment Utility
Take a look at my article on debugging. Don't be put off because it says VB6 since most of it applies to VBA.

BTW I think you just need to press the FN key and while holding it down type B (without Ctrl).
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes FN button hold it then press on B
or what you can do is on the menu look for Developper then press on the first icon visual basic you will have the editor that shows then on the menu you have the play paus buttons press on the pause button it is like II like on your DVD button
gowflow
0
 

Author Comment

by:Bright01
Comment Utility
gowflow; I think the error associated with having my VPN up and running and accessing the internet is in this code;

Option Explicit

Public Declare Function InternetGetConnectedState Lib "wininet.dll" (lpdwFlags As Long, ByVal dwReserved As Long) As Boolean

Function IsConnected() As Boolean
    Dim Stat As Long
    IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)
End Function

I'm reading through MartinLiss's article on debugging also......

B.
0
 

Author Comment

by:Bright01
Comment Utility
MartinLiss;

Under your section:

Break
Availability: Run time only
Shortcut: CTRL+BREAK

Once your program has started, and, for example, the main form has loaded and it is ready for interaction with the user, pressing the Break button pauses the execution of the program and takes you to the edit window for the form.

When I perform the CTRL BREAK, the program doesn't stop and the edit window doesn't open.

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
In your case because of the keyboard that you use you need to do Fn+B instead (see post 39660094). If that doesn't work then please let me know. BTW in some situations where the program is in a tight loop you may find that you need to press Fn+B several times.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
on your ID: 39660332 which line of code is YELLOW ???
gowflow
0
 

Author Comment

by:Bright01
Comment Utility
So here's what I got; when using the VPN, neither the sort (C14) due to currency change nor the update work.  When I press Update!, the system shows "busy" (hourglass).  If I try to break the macro to see where the error is occurring, using Fn and then B, nothing happens.  If I hit B (until the buffer fills up) the screen dims and then comes back after about 20 seconds.  

It has to be something in the Internet access code.  If I turn off the VPN, both the sort and the update work.

Bizarre.

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Have you tried setting a breakpoint on some line in the macro before the point where it fails and using F8 repeatedly to see line by line where the code goes and at important lines running the cursor over variables to see what they contain?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well from this distance we cannot break your code !!!
do this:

on the Function that you mentioned before

Function IsConnected() As Boolean
    Dim Stat As Long
    IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)
End Function

Open in new window


put your cursor at this line
IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)

and Hit F9 you will see that this line will turn Brown this means that the program once it reaches this lines will break and you will see the code and the line will turn yellow.

So do this
1) After the line is brown press F5 this will launch the program
2) at sometime the program will break and reach the above instruction in yellow
3) press F8 once (this means you are telling the program to only execute 1 instruction. If the cursor stays indefinitively on this line this means this is where it is hanging if not then it  will move to the next line which will turn yellow.
4) Keep on pressing F8 and you will see the program executing all the instructions one at a time when you feel the program is hanging this is where I need you to tell me the instruction it got stuck on.

Let  me know
gowflow
0
 

Author Comment

by:Bright01
Comment Utility
gowflow,

I'm learning how to troubleshoot!   This is awesome.   OK, here's what I think may be the problem.  As I step through the macro, it initially goes to this Process:

From the Public Sub Process, it calls GetExchangeRate.
It then goes through the Function fGetCurrencySymbol

all seems good.

It might be a Cache problem:

If IsConnected Then
inet.DontUseCache = True

Then it moves to the Function IsConnected () As Boolen
IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)

Then it goes again to:

If IsConnected Then
inet.DontUseCashe = True

Then it goes to Private Sub Class_Initialize()
Don'tUseCache = False

One other place that may be making this code hang due to some pre-configuration for internet access is;

hInet = InternetOpen (SCUSERAGENT, INTERNET_OPEN_TYPE_PRECONFIG.........

and finally, it may be going into this "retry" connection;

retrynum_internetopenurl = retrynum_internetopenurl + 1

So, using F9, F5 and F8 to step through, these are the areas where it may be hanging due to the type of internet connection.

Does that help?

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You could create some Public variable in a module and add to it each time an attempt is made to create a connection and if the variable exceeds some reasonable number, display a msgbox, reset the variable to 0, and exit.
0
 

Author Comment

by:Bright01
Comment Utility
At this point, you guys have done a great job helping me.  If Gowflow has no solution here, then I'm going to close this out and will use the app. without the VPN in operation or I will author a new question to improve on what we have here.  

You guys have really helped out here in helping me learn to troubleshoot.

B.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I only need the instruction where it is hanging to help you further. Did you try the steps I posed in ID: 39661207

gowflow
0
 

Author Comment

by:Bright01
Comment Utility
OK, gowflow.....think I have it!  

Based on MartinLiss's helpful Debug Doc.;  If I run the macro and "Step Into" using the F8 button, I think it steps me through the Macro, executing on each step.  If that is true, there it gets hung up is in the Class Module  rwinetXfer, in this area:

  ' retrieve the requested URL
    '   we might be retrying... update a counter var
retry_internetopenurl:
    retrynum_internetopenurl = retrynum_internetopenurl + 1
    If retrynum_internetopenurl > 3 Then GoTo exitfunc
    If DontUseCache Then
        hUrl = InternetOpenUrl(hInet, sUrl, vbNullString, 0, INTERNET_FLAG_RELOAD, 0)
    Else
        hUrl = InternetOpenUrl(hInet, sUrl, vbNullString, 0, 0, 0)
    End If
    If Err.LastDllError <> 0 Then
        lastErr = Err.LastDllError
        If lastErr = 2 Then
            'a hack, sometimes this seems to fail the first time but succeed on subsequent
            '   first noted the problem getting http://finance.yahoo.com/?u,  using XP/IE6.0.26
            Sleep (250)
            InternetCloseHandle (hUrl)
            GoTo retry_internetopenurl
        End If
        ret = "error (wininet.dll," & lastErr & " on InternetOpenUrl)"
        GoTo exitfunc
    End If


On this line:

If DontUseCache Then
        hUrl = InternetOpenUrl(hInet, sUrl, vbNullString, 0, INTERNET_FLAG_RELOAD, 0)

Does that help?

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This is totally out of my area but perhaps you can do something using the flags described in this article or this one.
0
 

Author Comment

by:Bright01
Comment Utility
Marty,

Thanks for the debugging article. Let me see if gowflow has a quick answer.  I keep thinking this is a parameter change to fix not a lot of lines of code.  Anyway thanks for hanging (no pun intended) in there with me/us.

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome. As I said the flags mentioned in the articles I posted in post ID 39666032 are out of my area of expertise but you could experiment with some of them and see if it helps.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Let me get this correct now:

When you use internet direct the macro now works fine
If you are connect via VPN it hangs on line
hUrl = InternetOpenUrl(hInet, sUrl, vbNullString, 0, INTERNET_FLAG_RELOAD, 0)

in Sub OpenURL

pls confirm
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 400 total points
Comment Utility
Try this version and see if this fixes it.
gowflow
Currency-Fetch-and-conversion-V5.xlsm
0
 

Author Closing Comment

by:Bright01
Comment Utility
Guys,

Wonderful teamwork on a complex fix!  The program, when VPN Connected, still hangs but I'm going to live with it because it is a great piece of work and it's impossible to troubleshoot a communication problem from "afar".  It may have to do with the AT&T VPN Dialer which would be a nightmare to figure out.  You guys did a great job and I learned a lot of new things.

Have a great evening.

All the best,

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome and I'm glad I was able to help even in a small way.

Marty - MVP 2009 to 2013
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Your welcome and glad we could help.
gowflow
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now