Single or Multiple Variables to Populate Message Boxes in Different Subs

AnyOldName

New Member
Joined
May 18, 2019
Messages
8
I normally use a string variable (Var_MsgText) as an input to message boxes but I'm currently writing my biggest piece of code to date that has multiple subs that all use message boxes. I don't want Var_MsgText to be a public sub as that would risk incorrect carry over of the content between different subs so the two approaches I'm considering are:

Approach 1: Var_MsgText1 in one sub, Var_MsgText2 in the next sub etc, that this means creating more variables but maybe I'm worrying about nothing

Approach 2: Have a single variable name (Var_MsgText) that's used in multiple subs but re-declaring it at the beginning of each sub so that its empty, is that possible or will it cause problems?

Approach 3: Someone gives me a better idea than 1 or 2

Thanks in advance.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,696
Office Version
365
Platform
Windows
I'm not an expert, by any means - and stand to be corrected (or shot down in flames) by some very clever Folk who help on this great site.
That having been said, for my two pennies worth:

1. Personally, as long as you're not needing the variable (presumably a string?) to service several of your msg boxes at the same time, I'd declare it publicly at the top of a module - with the workbook_open event. It's done then - once and for all, and after all, it's only an instruction to the computer, to hold a tiny bit of memory free for your code to use, so there's no need to re-declare it, or clear it out each time - just use it, as you would any variable; each time you refer to it, the previous data's cleared out, and the new string inserted. No need to declare it any more in any of your code, so it keeps things slightly more curt, too.

2. One other thing - personally, I've stopped using message boxes unless absolutely necessary. I find that it quickly gets tedious for you (or even more so for your users) to have message boxes popping up all the time, and having to react to them. I tend to use UserForms more now days - often small ones which are permanently displayed (by setting the "modeless" property) and having label controls on them. It's just as easy to then use the "Caption" property to pass messages to your users, and whilst it's not quite as "attention-getting" I find it a tidier and more user-friendly way to communicate.

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,102,774
Messages
5,488,774
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top