cbrendlinger
New Member
- Joined
- Feb 7, 2012
- Messages
- 8
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!
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!