public variable

I appreciate you all in advance. Now I'm struggle to break down VBA codes. My demands are three. 1. Declare Public Variable 2.Use it in worksheet_change 3.Call certain procedure from worksheet_change Thus I have three codes below, Code1 in worksheet module("Sheet1") And Code2, 3 in standard...

It seems that a Public variable declared in one module is available in others as well. But what about assigning the value to the variable in one module to be made available in others. Public InitialPart As String 'Module 1 Sub UserInput() InitialPart = "This is the initial part" End Sub...

Scenario: I have 3 subs inModule 1. Each one of thesesubs uses some but not all of 5 available variables. All Strings. Instead of pickingand choosing which variables to define in each of the 3 subs, I'd like tocreate 1 more sub in Module 2. This sub would be called at the start of each ofthe 3...

Scenario: I have 3 subs inModule 1. Each one of thesesubs uses some but not all of 5 available variables. All Strings. Instead of pickingand choosing which variables to define in each of the 3 subs, I'd like tocreate 1 more sub in Module 2. This sub would be called at the start of each ofthe 3...

Hello All, So I wanted to change the way I create macros usually (I'm no expert but want to be one), and decided that I want to declare my variables once as public if I use them in multiple modules and subs. And I had an example like this: Option Explicit 'Variables only here Public wsP...

Hello I want to declare public constant variable with following code Public Const sheetname As String = Format(Date - 30, "mmm-yy") It is giving compiling error 'Constant expression required'. I want to declare string variable which will store date in specific format. This variable value...

I have come across a problem that has stumped my resident VBA expert. Currently, I am running a sub that has Public Variables (String). These variables when used, will place a string of text in a message box, as well as an email. When the code is run, and the condition is engaged, the...

Hi all, I new to the site and still a bit new to VBA. What I am trying to do is open a user form and allow the user to pick a file (perhaps a few files in the future) open it and display the file name with one button click. Then use another control bottun to do the work of getting the needed...

Hello, All I need, is to be able to call the public variable 'lastrow2' from the module it is defined in to the sheet 1 object. I have the following code: IN THE 'Sheet1(Drawings List)' OBJECT [I have omitted some irrelevant code]: Private Sub Worksheet_Change(ByVal Target As Range) Dim...

Hi, I've been looking online for the last couple of hours for a solution to my problem. I've created a sub that works if I declare the array within the sub (see commented out declaration line below). However, I want to use the array in another module. So I've added in the Public line at the top...

Hello, I'm using a userform with 2 commandButtons ("Corp1"and "Corp2"), each one calls a different macro. what I need it's declare the name of the commandButtons that was clicked as a public variable, because I need that word latter for the report header. That's what I'm trying: 'in a...

I was curious if there was a way to call an Array from numerous modules. I am learning VBA, and have actually just learned how to declare an Array :) which lead me to be curious if there was a way to call an Array from multiple modules?

Hi, Another dumb question: I have Userform1 which obtains info which is then used by Userform 2. To achieve this I am using public variables. What I am unsure of is how to clear the values in those variables once UserForm2 ends. Is it as simple as vPublic1="" in the last line of userform? Is...

I am trying to use a variable already declared in a workbook module, in a second workbook. In the first workbook module I write: Public StrA As String Sub SetString() StrA = "abc" MsgBox StrA End Sub I can verify in the inspection window that StrA has the correct value after...