+ Reply to Thread
Results 1 to 5 of 5

Dim, Public, Private MyVariable As String

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Dim, Public, Private MyVariable As String

    Hi all,

    How can I make below variables available in all my modules? They won't ever change unless the filename changes and I need to refer to them in several modules. I'm not familiar with the different type of variable definitions as I always use "Dim", not sure what "Global", "Public", "Private" and all the others do. Would appreciate if someone could explain the difference and usage...

        Dim Season As Variant
        Dim SuperSeason As Variant
        Dim Brand As Variant
        Dim Division As Variant
        Dim FilePath As String
        
        SuperSeason = Evaluate(ThisWorkbook.Names("SuperSeasonName").Value)
        Season = Evaluate(ThisWorkbook.Names("SeasonName").Value)
        Brand = Evaluate(ThisWorkbook.Names("BrandName").Value)
        Division = Evaluate(ThisWorkbook.Names("DivisionName").Value)
        FilePath = "'S:\COMMON-HK2\HK-APAC-MAP\Range Plan\POLO\"

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,300

    Re: Dim, Public, Private MyVariable As String

    I suggest you read this: http://www.cpearson.com/Excel/Scope.aspx
    Anyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Dim, Public, Private MyVariable As String

    Thanks! Don't seem to get it right though. Below is what I've done now in one of my modules, but when I run a procedure in another module with these variables, it says "variable not defined"...

    Option Explicit
    
    Public SuperSeason As Variant
    Public Season As Variant
    Public Brand As Variant
    Public Division As Variant
    Public FilePath As String
        
    Public TSS As String
    Public LSS As String
    Public LLSS As String
        
    Public TY As String
    Public LY As String
    Public LLY As String
    
    Sub LDP()
        
        SuperSeason = Evaluate(ThisWorkbook.Names("SuperSeasonName").Value)
        Season = Evaluate(ThisWorkbook.Names("SeasonName").Value)
        Brand = Evaluate(ThisWorkbook.Names("BrandName").Value)
        Division = Evaluate(ThisWorkbook.Names("DivisionName").Value)
        FilePath = "'S:\COMMON-HK2\HK-APAC-MAP\Range Plan\POLO\"
        
        TSS = Left(SuperSeason, 4) & Right(SuperSeason, 2)
        LSS = Left(SuperSeason, 4) & Right(SuperSeason, 2) - 1
        LLSS = Left(SuperSeason, 4) & Right(SuperSeason, 2) - 2
        
        TY = "SS" & Right(SuperSeason, 2)
        LY = "SS" & Right(SuperSeason, 2) - 1
        LLY = "SS" & Right(SuperSeason, 2) - 2

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,300

    Re: Dim, Public, Private MyVariable As String

    Which module did you declare the variables in? It should be a normal module.

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Dim, Public, Private MyVariable As String

    Ups... had it in a UserForm! Now it works!! Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Parameters to Function and Using the Public/Private
    By venkatdavuluri in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2017, 05:39 AM
  2. Macros saved private - to public
    By Amber12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2017, 05:10 PM
  3. Public vs Private?
    By jplank in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2016, 03:05 PM
  4. [SOLVED] Private Sub Workbook_Open() Vs Public Sub Auto_Open()
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-12-2014, 07:10 PM
  5. Convert this private sub to public sub to work
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2013, 02:59 PM
  6. Public or Private Variable
    By jo15765 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-12-2012, 02:46 PM
  7. Option Explicit function and private/public/dim
    By larryg003 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2010, 03:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1