Variable Not Updating in Call to Macro in Another Workbook

[cbrendlinger]

I have a macro that I want to house in a separate Workbook so that I can call it from multiple macros in multiple Workbooks. The call to the macro seems to work but the variable that I am looking for does not update in the calling macro. The value returned is empty. Here is what I have:

The Macro being called:
Option Explicit

Public Logo_Name As String

Public Function Get_Logo_Name(Ref_URL As String, LogoName As String) As String

If ((Ref_URL = "/www.website.com/client1/")) Then
LogoName = "Client1"
ElseIf ((Ref_URL = "/www.website.com/client2/")) Then
LogoName = "Client2"
Else
LogoName = "None"
End If

Logo_Name = LogoName

End Function

The code calling the macro above:

Sub A_Get_Answers()

Dim AltWorkbookName As String
Dim MacroName As String
Dim folderpath As String
Dim subpath As String
Dim LogoName As String
Dim wb As Workbook

folderpath = Application.ThisWorkbook.Path
subpath = Left(folderpath, InStrRev(folderpath, "\") - 1)
AltWorkbookName = subpath & "\Current Application\AALogoPull.xlsm"
MacroName = "Get_Logo_Name"
Set wb = Workbooks.Open(AltWorkbookName, , True)
Application.Run "'" & wb.Name & "'!" & MacroName, Ref_URL, LogoName
MsgBox wb.Logo_Name

End Sub

The value of wb.Logo_Name is always blank. All I am trying to do is retrieve that value. I am sure that I am missing something (hopefully simple) but I just don't know what. I really appreciate any insight. Thank you!

 

[RoryA]

In which module is Logo_Name declared? It would have to be in the ThisWorkbook module of the other workbook (and nowhere else).

 

[cbrendlinger]

It is defined in the called macro not the calling macro.

 

[RoryA]

I know but in which module in that workbook is that code?

 

[cbrendlinger]

Module1?

 

[RoryA]

Then you should be getting an error. This line:

Public Logo_Name As String

must be in the ThisWorkbook module of that workbook, and the code line to set it should be:

ThisWorkbook.Logo_Name = LogoName

not:

Logo_Name = LogoName

 

[cbrendlinger]

Worked like a charm! Thank you so much for your help Rory!

 

[RoryA]

Glad we could help.