Learning VBA like I Learned to Use Matlab

[mike_302]

I learned Matlab before I used Excel VBA. What I learned to rely heavily on when coming up with Matlab scripts is the "Workspace" window, which displayed the declared variables and their current value in memory. This would allow me to investigate the value of variables as the program progresses, because I would debug line by line if I didn't understand something that was happening.

I am hoping I can achieve a similar thing in the Excel VBA editor. Can anyone shine a light on if I can achieve this and how?

 

[alansidman]

with the vbe open, click on debug and then step through the code by clicking on F8

 

[MARK858]

Nearest would probably be the locals window, see the link below on debugging...

http://www.cpearson.com/excel/DebuggingVBA.aspx

 

[mike_302]

Locals Window! that's what I was interested in Thanks.

I was aware of the debugging capability anyways.

So, I've created VBA scripts by copy-pasting and following exact instructions posted online before. Now I want to go about creating my own code to interrogate and modify my sheets. I have never properly understood the starting point though -- in particular the Project tree structure (Excel Objects and Modules). In my Project - VBAProject window in the VBA editor, I see several Projects at the highest level, including the file I have open. Under that level, in the branch related to my open workbook, I have 2no. folders: Microsoft Excel Objects (under which all my sheets are listed) and Modules. I have used VBA code I've found online before, that has instructed me to place the code in either of those folders, but not really justifying why.

For some further explanation of my intentions for the immediate future: I want to write a script that will loop through a whole workbook, read values, collect those values into a list, drop that list into a single worksheet, and loop through this several times for a number of conditions. From what I gather from Excel VBA explanations, I would create such a script under a new "Module" in the module folder? I'm really just looking for some confirmation; if you have any idea where I find a good introduction to this tree structure, I would appreciate the link. Most stuff I read explains it as if I'm new to programming -- you might be able to tell that I'm clearly thinking in the Matlab environment, so I am thinking about files with data, and then Scripts and Functions with which I read-write to/from those data files.

 

[MARK858]

I was aware of the debugging capability anyways.

If you were then you would have already been aware of the Locals window as it is part of debugging

From what I gather from Excel VBA explanations, I would create such a script under a new "Module" in the module folder

Basically you put regular subs/functions in regular modules, workbook event code in the thisworkbook module and worksheet event code in worksheet modules.

 

[mike_302]

Apologies: when I said I was aware of the debugging, I guess I meant that I knew you could debug code by clicking in the small grey column to the left of the code, raising a red bubble on the line that the code would stop. Beyond that --- I knew nothing :P

Thanks for the clear explanation of where to place code. I used the term "workbook event code", which I did not know before, to do a bit of a google search and fond out even more still

 

[hiker95]

mike_302,

Debugging VBA
http://www.cpearson.com/Excel/DebuggingVBA.aspx

Debug Excel VBA Macro Code
http://www.ozgrid.com/VBA/debug.htm

TechBookReport - Debugging Excel VBA Code
http://www.techbookreport.com/tutorials/debug_excel_vba_01.html


See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 11/15/2017

https://www.mrexcel.com/forum/excel-questions/1031751-two-novice-questions.html