Calling VBA custom Function in the main Subroutine

Question

I have two functions that both determine the output depending on the value in a cell. The outcome is Column N should have a combined output of function mBlineCTG + findShpc. They separately work, I would like to call them on the main function that way I don't have to run both routines separately. Am open to help including the logic behind

Option Compare Text
Sub Main()
 M_xx
 f_xxx
 Cells(i, 14).Value = M_xx + f_xxx
 Range("Q1").Value = "xxx_xxxx"
End Sub

Public Function M_xxx()
 Dim LastRow As Long
 LastRow = Cells(Rows.Count, 1).End(xlUp).Row

 Dim  s_Cxx
 Dim i As Integer

For i = 2 To LastRow
Select Case UCase(Left(Cells(i, 10).Value, 1))
    Case "s"
        s_Cxx = "D"
    Case "0"
         s_Cxx= "SD"
    Case "1", "R", "E", "F"
        Select Case UCase(Left(Cells(i, 13).Value, 2))
          Case "13"
             s_Cxx = "SB"
            Case Else
             s_Cxx = "SS"
        End Select
    Case "I"
      s_Cxx = "IS"
    Case "2", "15"
         s_Cxx = "BB"
    Case "3"
         s_Cxx = "SF"
    Case Else
         s_Cxx = ""
 End Select

    Next i
End Function

Public Function f_xxxx()
 'declare variables
 Dim LastRow As Long

 LastRow = Cells(Rows.Count, 1).End(xlUp).Row
 'Dim ThisCont
 'ThisCont = Range("E2").Value
  Dim S_xxx2
  'loop thru' rows 
  Dim i As Integer

  For i = 2 To LastRow
  Select Case UCase(Left(Cells(i, 5).Value, 1))
   'Select Case ThisCont
        Case "A"
           S_xxx2 = "big-base-Post-01 0 01 Cee-xx-04"
        Case "X"
           S_xxx2 = "small-shelf-Post-01 0 01 Cee-xx-01"
        Case Else
           S_xxx2= "big-drawer-Post-01 0 01 Cee-xx-06"
  End Select
   'Cells(i, 14).Value = S_xxx2
 Next i
End Function

it looks like you are calling both of them from the main function. It's not really clear what you are after. — Jeremy
– Jeremy, Commented Dec 10, 2015 at 14:49
Yes, you're calling both functions twice. Can you elaborate on what is the output you're getting, and how that differs from what you expect? — David Zemens
– David Zemens, Commented Dec 10, 2015 at 14:49
The Range "N1" value is just the column header, the results are supposed to populate from N2 to FinalRow. The output I want fill this is if for example if Cell E1 value is "C" then function findShpc returns this string "S-Sh-PS-01 0 01 CC-SM-01 and function mBlinCTG returns a "V" — joes
– joes, Commented Dec 10, 2015 at 15:19
Sorry pressed enter before finishing, So Column "N2" onward should populate with both the returns of functions. At the moment, If I run the functions as subs they output the desired results but I cant them to run as functions in the main sub. I get error 1004 "Application defined or object- defined error" — joes
– joes, Commented Dec 10, 2015 at 15:23
David, thanks for your response, I tried your solution but got the same error. — joes
– joes, Commented Dec 10, 2015 at 15:31

David Zemens · Accepted Answer · 2015-12-10 16:21:04Z

1

I think you have some confusion, as you're calling both functions twice.

The first call to each is redundant as it does not return any value to the main procedure. The second call is an error since i is undefined in the scope of Main procedure.

Option Compare Text
Sub Main()
 mBlineCTG   '<-- calls the mBlineCTG function but does not return any value
 findShpc    '<-- calls the findShpc function but does not return any value
 Cells(i, 14).Value = mBlineCTG + findShpc  'This line is an error because "i" _
                                             is undefined, but this would writes the _
                                             sum of two functions to Column N/Row i, _
                                             if "i" is an integer >= 1
 Range("N1").Value = "SHP_SRC"  '<-- overwrites the value in Range("N1")
End Sub

I think this is what you need:

Option Compare Text
Sub Main()
 Range("N1").Value = mBlineCTG & findShpc  '<-- concatenates mBlineCTG and findShpc in to range("N1")
End Sub

Also, I note that neither of your functions has a return value of any sort, so this actually won't write anything in to the N1 cell... You're going to need to elaborate more on what is the expected output.

I think this might take care of it, do the loop in the main function (instead of redundant loop in the called functions):

Option Compare Text
Option Explicit
Sub Main()
Dim FinalRow As Long
Dim i As Long

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To FinalRow
    Range("N2").Cells(i).Value = mBlineCTG(i) & findShpc(i) '<-- concatenates mBlineCTG and findShpc in to range("N1")
Next
End Sub


Public Function mBlineCTG(i As Long)
    Dim ThisCont
    Dim shpCt As String

    Select Case UCase(Left(Cells(i, 13).Value, 1))
        Case "D"
            shpCt = "D"
        Case "0"
            shpCt = "V"
        Case "1", "R", "E", "F"
            Select Case UCase(Left(Cells(i, 13).Value, 2))
              Case "13"
                shpCt = "B"
                Case Else
                shpCt = "S"
            End Select
        Case "I"
            shpCt = "I"
        Case "2", "15"
            shpCt = "B"
        Case "3"
            shpCt = "F"
        Case Else
            shpCt = ""
     End Select

     '#### RETURN THE VALUE TO CALLING PROCEDURE:
     mBlineCTG = shpCt

End Function

Public Function findShpc(i As Long)
  Dim SHPcT2 As String
  Select Case UCase(Left(Cells(i, 5).Value, 1))
   'Select Case ThisCont
        Case "A"
           SHPcT2 = "L-BM-PS-01 0 01 CC-LG-01"
        Case "X"
           SHPcT2 = "X-BM-PS-01 0 01 CC-XX-01"
        Case Else
           SHPcT2 = "S-SH-PS-01 0 01 CC-SM-01"
  End Select
  '#### RETURN THE VALUE TO CALLING PROCEDURE:
  findShpc = SHPcT2
End Function

edited Dec 10, 2015 at 16:21

answered Dec 10, 2015 at 14:52

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

6 Comments

Jeremy Over a year ago

While the + can be used to concatenate strings in VBA, the & would be preferable just to make sure VB is treating it as a string.

David Zemens Over a year ago

Good point @Jeremy I didn't look closely at the function body to see what if anything they are returning. On closer look, these functions aren't returning any value...

joes Over a year ago

David... thanks ALOT! great answer and explanation, it worked just as I had in mind.

David Zemens Over a year ago

You're welcome! Do consider accepting the answer if it's solved the problem :)

joes Over a year ago

@David there are no errors with data being written from "N2", I used Range ("N1").Value = "SHP_CAT" to write the Column Header.Thanks again!

|

Collectives™ on Stack Overflow

Calling VBA custom Function in the main Subroutine

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related