5 Simple Tips for Making BETTER Macros in Excel!

[@grisheldamarks-webster-noa1706]

Hello, Is there a similar function like macros that allows you to hide rows based on a cell value (dropdown menu)? I started with macros but the clients were having trouble with the workbooks. I am currently using excel 2016, I can upgrade to a non- subscription version if need be. Thanks

[@rcwagon]

So ByVal is a closed valve that prevents flow, and ByRef is an open valve that lets its value flow through. Not a solid way to remember but I don't have one. Thanks for showing this chaos . I could imagine using one without even understanding what you showed and wasting days trying to figure it out without a clue what the issue is.

[@seanmahoney1077]

ByRef means reference to a memory location. That memory location can have many names in your program. All those names mean the same address in memory. That is why changing NameB causes NameA to change. Both reference the same location in memory. ByVal creates a separate memory location for the new nameB and therefore does not affect the value in the memory location NameA. NameA cannot affect NameB either. Essentially they are DIM'd separately.

[@TeachExcel]

Sean has a very good explanation of the memory pointer setup in his comment. When I am teaching someone this topic, like in my full VBA course, not just a quick KZbin tutorial, I say to make everything ByVal always. In that way, you aren't trying to use one way for a variable here and another way for a variable there - stick to ByVal everywhere and, if you want to change a value in the main macro, use a function that returns a value to do that; this setup will avoid the issue.

[@enrikekasijas705]

Nice. I never knew that part with function not closing itself by default. That's so illogical.

[@TeachExcel]

I'm glad I could show you this! It's SO easy to forget and then spend hours debugging. :/

[@seanmahoney1077]

EXIT FUNCTION is NOT a good substitute for really crappy programming. Better solution is "IF-ELSE-ENDIF.

[@TeachExcel]

For sure! It is better to have one way in and one way out. A lot of people do not use this type of structure though, so, hopefully, they can at least use the Exit Function tip to save themselves from debugging hell.