CODEWITHSUNDEEP
excelvbavariables
Dullspark
Dullspark

Reputation: 215

Excel VBA - Keeping variables the same after End Sub

I have some code that give me some variables - Champ. I want to be able to run this programme, then in my next Sub() reference these variables. The code is:

  Sub CreateUniqueList()
  Dim dict As Object, lastRow As Long, Champ, c
  Set dict = CreateObject("Scripting.dictionary")
  With Sheets("Raw Data")
      lastRow = .Cells(.Rows.count, "D").End(xlUp).row
      For Each c In .Range("D7:D" & lastRow).SpecialCells(xlCellTypeVisible)
        dict(c.text) = 0
      Next
  End With
  Champ = dict.Keys

  ' Now you have the "variables". To create the new sheet:
  With Sheets.Add(After:= Sheets(Sheets.Count))
    .Name = "Unique Champions"
    .Range("A2").Resize(dict.count).Value = Application.Transpose(dict.keys)
    .Range("A1").Value = Sheets("Raw Data").Range("D6").Value
  End With
End Sub


Sub Criteria ()
If (Cells(i, 2).Value = Champ3) Then...............................
End Sub

The issue is, when i try to run "Sub Criteria", Champ3 returns the value Empty.

How can i make it so the variable carries onto the separate programme?

Thanks

EDIT: The second Sub is just there for an example

Upvotes: 1

Views: 2585

Answers (1)

Tom
Tom

Reputation: 9878

Try writing it as a function instead

Function CreateUniqueList() As Variant()
    Dim dict As Object, lastRow As Long, c

    Set dict = CreateObject("Scripting.dictionary")
    With Sheets("Raw Data")
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        For Each c In .Range("D7:D" & lastRow).SpecialCells(xlCellTypeVisible)
            dict(c.Text) = 0
        Next c
    End With
    CreateUniqueList = dict.keys
End Function

Sub Criteria()
    Dim Champ As Variant

    Champ = CreateUniqueList

    ' Now you have the "variables". To create the new sheet:
    With Sheets.Add(After:=Sheets(Sheets.Count))
        .Name = "Unique Champions"
        .Range("A2").Resize(UBound(Champ)).Value = Application.Transpose(Champ)
        .Range("A1").Value = Sheets("Raw Data").Range("D6").Value
    End With

    If IsInArray(Cells(i, 2).Value, Champ) Then

    End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Upvotes: 1

Related Questions