CODEWITHSUNDEEP
excelvba
Ben
Ben

Reputation: 271

Sub that returns a value

Is there a way that I can call a block of code and get the value that I am looking for and store it as the variable I want and then use it in a different sub?

For example. I want to search all the values in row 1 starting at A1 and going till the there is a blank value. I want to find the cell with the value "Frequency" in it and then return the column index number.

Sub findFrequency()
    'find "Frequency" colum and save the column index number
    Dim fFreq As String
    Dim FreqCol As Variant
    Range("A1").Select
    fFreq = "Frequency"
    Do Until IsEmpty(ActiveCell)
        If ActiveCell.Value = fFreq Then
             FreqCol = ActiveCell.Column
             Exit Do
        End If
        ActiveCell.Offset(0, 1).Select
    Loop
End Sub

Now ideally I can write a different sub and use the value from the above code.

Sub Execute()
    Call findFrequency
    Cells(5, FreqCol).Select
    With Selection.Interior     
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
End Sub

I get an error when I run this because the value of FreqCol is not set to anything from running the Call findFrequency line.

Upvotes: 2

Views: 6261

Answers (4)

jimifiki
jimifiki

Reputation: 5544

let me tell you that you should not apologize for asking a basic question about VBA: nobody deserves to spend time with VBA and I'm glad you are more acquainted with other programming languages.

I write my answer for all people not familiar with VBA.

VBA passes arguments by default by reference so you can write your code in this way:

Sub findFrequency(FreqCol as Long)
...
End SUb 

Sub Execute()
    Dim FreqCol As Long
    findFrequency FreqCol 
    ... 
End SUb

Upvotes: 0

Dy.Lee
Dy.Lee

Reputation: 7567

The code would to be like this.

Main Main sub is findFrequency, subprocedure is Sub Execute(rng As Range)

Sub findFrequency()
        'find "Frequency" colum and save the column index number
        Dim fFreq As String
        Dim FreqCol As Variant
        Range("A1").Select
        fFreq = "Frequency"
        Do Until IsEmpty(ActiveCell)
            If ActiveCell.Value = fFreq Then
                FreqCol = ActiveCell.Column
                Execute Cells(5, FreqCol)
                Exit Do
            End If
        ActiveCell.Offset(0, 1).Select
        Loop
End Sub
Sub Execute(rng As Range)

With rng.Interior
     .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .ThemeColor = xlThemeColorAccent5
     .TintAndShade = 0.599993896298105
     .PatternTintAndShade = 0
End With
End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149315

Why loop or select a cell to find a value? Simply use .Find

Function findFrequency() As Long
    Dim ws As Worksheet
    Dim aCell As Range

    Set ws = ActiveSheet

    With ws
        Set aCell = .Columns(1).Find(What:="Frequency", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then findFrequency = aCell.Column
    End With
End Function

Also what should happen if the "Frequency" is not found. You need to cater to that as well.

Sub Execute()
    Dim FreqCol As Long

    FreqCol = findFrequency

    If FreqCol = 0 Then 
        MsgBox "Frequency not found"
        Exit Sub
    End If

    With Cells(5, FreqCol).Interior
         .Pattern = xlSolid
         .PatternColorIndex = xlAutomatic
         .ThemeColor = xlThemeColorAccent5
         .TintAndShade = 0.599993896298105
         .PatternTintAndShade = 0
    End With
End Sub

Upvotes: 1

Egan Wolf
Egan Wolf

Reputation: 3573

Try this:

Function findFrequency()
    'find "Frequency" colum and save the column index number
    Dim fFreq As String
    Dim FreqCol As Variant
    Range("A1").Select
    fFreq = "Frequency"
    Do Until IsEmpty(ActiveCell)
        If ActiveCell.Value = fFreq Then
            findFrequency = ActiveCell.Column
            Exit Do
        End If
    ActiveCell.Offset(0, 1).Select
    Loop
End Function

Sub Execute()
Dim FreqCol As Long
FreqCol = findFrequency()
Cells(5, FreqCol).Select
With Selection.Interior     
     .Pattern = xlSolid
     .PatternColorIndex = xlAutomatic
     .ThemeColor = xlThemeColorAccent5
     .TintAndShade = 0.599993896298105
     .PatternTintAndShade = 0
End With
End Sub

Upvotes: 1

Related Questions