VBA - applying function to a sub

Question

im a VBA newbie. I have to create function, that transform state address to state full name eg. CA -> California and apply it to the whole column with addresses. Given task:

*Open VBE and add module named modFunction. In this module create function (named this function StateFullName) which transform state address into state full name. Function should take one parameter (state address) and return one string value (state full name)

In column 7 each rows with data should have full state name (Tip: use loop and function prepared in modFunction).*

I have created a function, but now I don't know how to apply it in sub with loop.

My function:

Function StateFullName(state_address As String) As String

    Select Case state_address
        Case "CA": StateFullName = "California"
        Case "AZ": StateFullName = "Arizona"
        Case "MT": StateFullName = "Montana"
        Case "NM": StateFullName = "New Mexico"
    End Select
    
End Function

on any other script you do, state = StateFullName("CA"). May also want to make this one a Public Function — Ricardo A
– Ricardo A, Commented Jun 9, 2022 at 15:36
It sounds like this is supposed to be a UDF (User Defined Function) for use in a cell on the worksheet. If that's the case, then @RicardoA advice to make it a Public function is spot on. If the function is NOT a UDF, then you'll have to create a Sub and create a loop that calls the StateFullName function for each abbreviation and puts the value into a row in column 7. — PeterT
– PeterT, Commented Jun 9, 2022 at 16:07

Ricardo A · Accepted Answer · 2022-06-09 16:36:28Z

1

When setting up your Function as a public function, you can use it in two ways, as a formula and as a Function on other Macros/Subscripts.

Here is a sample of your code:

Public Function statestring(twoletter As String) As String
    Select Case twoletter
        Case "CA": statestring = "California"
        Case "AZ": statestring = "Arizona"
        Case Else: statestring = "Unknown State"
    End Select
End Function

And here I am using it as a Formula on Column B, top right you can see the formula:

And here I am using it on another Sub as a Function to fill Column C:

(It could be a lot better coded but I just wrote it as it came to mind)

Edit: After a quick thought an offset would be make it much better

Sub getFullState()
    Set wk = ThisWorkbook
    With wk.Sheets("Sheet1")
        'startRow = 2 ' Removed
        For Each Rng In .Range("A2:A4")
            'Next line Replaced with Offset
            '.Range("C" & startRow).Value = statestring(Rng.Value)
            Rng.Offset(0, 2).Value = statestring(Rng.Value)
            'startRow = startRow + 1 'Removed
        Next
    End With
End Sub

answered Jun 9, 2022 at 16:36

Ricardo A

1,8151 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BeeLee Over a year ago

Thanks so much for your effort! I get it know. Cheers!

Ricardo A Over a year ago

Np, glad I could help. If this answered your question please mark it as answered, if not please let me know and I will try to make sure it does.

Collectives™ on Stack Overflow

VBA - applying function to a sub

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related