Help Deciphering VB module

[jjjjjjjjunit]

Hi all,

I have been set a task that involves modifying the way that a macro works for a client.. whilst I know how to do what i want to do, i do not know how to get excel to do it for me, so I will be learning VBA over the coming days

My first step is to understand what the author has written in the first place so I know what I'm working with.

I have annotated the code to try to explain what I think its saying.

would someone be willing to give up a little time to check my annotations and expand upon anything that I haven't understood or have missed? the simplicity of my annotations should be a good indication of my level; of experience in VBA (close to zero)?

This is what I have so far, I'll work on the other modules as the days pass..... The whole program takes a .csv file and converts it to a .xlsx file and organises data in a meaningful way and plots a graph from it (the graph has a zoom function too) and certain points of interest are read from the graph and reported in numbers.

There is probably a fair bit that I haven't understood, so help would be great and really appreciated!

I have crossposted this message on another forum - https://www.mrexcel.com/forum/excel-...=1#post5125039

my comments follow '-JS-

is it easier to upload my workbook?

Please Login or Register  to view this content.

[6StringJazzer]

I haven't analyzed the code or your own analysis yet, but I will point out that you have not interpreted DoEvents correctly. In all of the operations shown, it is not necessary to insert special code to wait for the operation to finish. In each case, the line of code will be in control until the operation is finished, then it simply moves to the next line of code. DoEvents is an explicit opportunity for Excel to respond to any events that occur outside the code, such as a user interacting with a worksheet. Without DoEvents, the user interface is locked down until the code finishes (this is a bit oversimplified; a modeless form allows interaction while it is displayed). In this case, it is unclear why it is needed; I believe it is not needed at all, though it's not harmful.

Please Login or Register  to view this content.

This is not correct. This mean that if a runtime error occurs any time after this line is executed, go directly to Done. It has nothing to do with whether files exist. It is unclear what errors the programmer was anticipating could occur.

Please Login or Register  to view this content.

This is a matter of opinion and style but my opinion is that a GoTo is a poor programming practice and should be avoided. I think I have written two GoTos in 40 years. This creates what is known in the trade as "spaghetti code."

Please Login or Register  to view this content.

This is not correct. GoTo 0 means if an error occurs, raise the error. It is the default. It is generally used to return to the default sometime after a previous On Error statement was used to change the default.

[jjjjjjjjunit]

Thanks for the pointers,

I see now then that the

Please Login or Register  to view this content.

was used to return to default after the previous command (~18 lines up) was

Please Login or Register  to view this content.

.

I also see how jumping forwards and backwards like this can get confusing, but i suppose as its a short script it may be acceptable?

I've tried to split up my post into comments and code to make it easier to read, and have taken your comments on board...

Please Login or Register  to view this content.

'-JS- suspend screen updating

Please Login or Register  to view this content.

'-JS- "source" is the range in which the root directory is specified by the user

Please Login or Register  to view this content.

'-JS- If no root directory was specified, the default will be C:\

Please Login or Register  to view this content.

'-JS- If root directory is specified, specification is prioritised

Please Login or Register  to view this content.

'-JS- Exports The rescaling module as a .bas file (to the root directory?)

Please Login or Register  to view this content.

'-JS- Sets abbreviation for access to file system object functions

Please Login or Register  to view this content.

'-JS- Source folder is set as the root directory (with file system object functions enabled)

Please Login or Register  to view this content.

'-JS- sets "MyFile" as the .csv file within each sub-sub-folder ("Exports") in the source folder.

'-JS- I think this section searches for a folder called exports and converts a .csv file to a .xslsb file,
' then it processed the file by calling another subroutine ("show bounce" which is within the previously exported module "graph_scaling"),
' then continues this process for any nyumber of folders in the root directory.

Please Login or Register  to view this content.

'-JS- If files DO NOT exist, then do the following

Please Login or Register  to view this content.

'-JS- Convert .csv file to .xslb, but if both already exist, then go to "Done"

Please Login or Register  to view this content.

Please Login or Register  to view this content.

'-JS- open the .xlsb workbook

Please Login or Register  to view this content.

'-JS- wait till workbook has finshed opening before proceeding

Please Login or Register  to view this content.

'-JS- If files DO exist, then do the following

Please Login or Register  to view this content.

'-JS- Once the workbook is open, wait this length of time before proceeding

Please Login or Register  to view this content.

'-JS- Once the workbook is open, ensure it is the active workbook

Please Login or Register  to view this content.

'-JS- Imports the module into the active workbook, presumably so that the subroutine within it can be applied to the active workbook.

Please Login or Register  to view this content.

'-JS- calls and runs the sub "show bounce"

Please Login or Register  to view this content.

'-JS- saves processed file as a macro enabled .xslb file

Please Login or Register  to view this content.

Please Login or Register  to view this content.

'-JS- return to default error handling

Please Login or Register  to view this content.

'-JS- resore screen updating

Please Login or Register  to view this content.

[dominicb]

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

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).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

[jjjjjjjjunit]

Appologies, but I need to mention that I have cross posted in another forum.. Hope I haven't broken any rules.

https://www.mrexcel.com/forum/excel-...=1#post5125039

[jjjjjjjjunit]

Turns out I broke a rule.. I posted this before seeing the message on this forum. Apologies again. hope its sorted now.

I appreciate everyone time and would like to get to a stage where i can contribute to helping other in the future (have done once and it was great )

[6StringJazzer]

Appologies, but I need to mention that I have cross posted in another forum.. Hope I haven't broken any rules.

https://www.mrexcel.com/forum/excel-...=1#post5125039

Thanks for posting the link. It is not against the rules to cross-post if you include the link.

[jjjjjjjjunit]

So I've made a fair bit of progress in understanding this, thanks to posters here and my brother who gave me some pointers.

I've uploaded the workbook to make it simple to read, but if someone could confirm that I have got the gist of it all that would be fab I'' crack on with the next module (three in total) before pestering again