Menu
I want to do this but it won't compile:
Public MyVariable as Integer = 123 What's the best way of achieving this?
563
Step 1: Create a new module. Go to Insert tab, click on Module to add a new module under VBE. Step 2: Add Option Explicit within the module. Step 3: Define a new variable named var1 and var2 both as a type integer.
Setting variables In VBA, declaring variables is optional, but you can't declare AND set the variable at the same time. We've added a line that assigns the value of a variable. In VBA, just append “. Value”, an equals sign, and the value you want to assign (in quotation marks).
Use the Public statement to declare public module-level variables. Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared.
.NET has spoiled us :) Your declaration is not valid for VBA.
Only constants can be given a value upon application load. You declare them like so:
Public Const APOSTROPHE_KEYCODE = 222 Here's a sample declaration from one of my vba projects:
If you're looking for something where you declare a public variable and then want to initialize its value, you need to create a Workbook_Open sub and do your initialization there. Example:
Private Sub Workbook_Open() Dim iAnswer As Integer InitializeListSheetDataColumns_S HideAllMonths_S If sheetSetupInfo.Range("D6").Value = "Enter Facility Name" Then iAnswer = MsgBox("It appears you have not yet set up this workbook. Would you like to do so now?", vbYesNo) If iAnswer = vbYes Then sheetSetupInfo.Activate sheetSetupInfo.Range("D6").Select Exit Sub End If End If Application.Calculation = xlCalculationAutomatic sheetGeneralInfo.Activate Load frmInfoSheet frmInfoSheet.Show End Sub Make sure you declare the sub in the Workbook Object itself: 
156
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
