Modules and Function

[sabascal]

Hi all,

I am building a menu bar around functions.
For more clarity, I put the menu code in a MenuBar module.
The first item of my menu is calling MarketRates() which is stored in dataDownload module.

When using my menu to call MarketRates() I keep getting a "Can not finf MarketRates()" error message.

What's wrong?

Thanks
Seb

 

[JohnAcc]

Can you post your code?

Rgds, John

 

[sabascal]

Here it is

'-------------------------------
MenuBar Module
'-------------------------------
Sub CreateMenu()
Dim NewMenu As CommandBarPopup
' Delete the menu if it already exists
Call DeleteMenu

' Find the Help Menu
Set HelpMenu = CommandBars(1).FindControl(Id:=30010)

If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Before:=HelpMenu.Index, _
temporary:=True)
End If

' Add a caption for the menu
NewMenu.Caption = "&EVT Tools"

' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Money Market..."
.OnAction = "Macro1"
End With

' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Market Rates..."
.OnAction = "MarketRates()"
End With

' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "&Core EVT"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Index"
.OnAction = "Macro3"
End With

' SECOND SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&CAGR"
.OnAction = "Macro4"
End With

' THIRD SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&FWD Rate"
.OnAction = "Macro4"
End With

' 4th SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Promote"
.OnAction = "Macro4"
End With

' 5th SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Check"
.OnAction = "Macro4"
End With

' 6th SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Residual"
.OnAction = "Macro4"
End With

' 7th SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&DTL"
.OnAction = "Macro4"
End With

' 4th MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "&Utilities"
.BeginGroup = True
End With

' 1st SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)

With SubMenuItem
.Caption = "&File list"
.OnAction = "ListFiles()"
End With

' 2nd SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Kill Links"
.OnAction = "Macro4"
End With

' 3rd SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "..."
.OnAction = "Macro4"
End With

' 5th MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "&Format"
.BeginGroup = True
End With

' 1st SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Thousands"
.OnAction = "Num()"
End With

' 2nd SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Pics resize"
.OnAction = "Macro4"
End With

' 3rd SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Format Table"
.OnAction = "Cadre()"
End With

' 4th SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "&Print Area"
.OnAction = "Ajustement()"
End With

End Sub
Sub DataEntryMacro()
MsgBox "Hello from the data entry macro"
End Sub
Sub Macro1()
MsgBox "This is a dummy macro for demonstration pursposes."
End Sub
Sub Macro2()
MsgBox "This is a dummy macro for demonstration pursposes."
End Sub
Sub Macro3()
MsgBox "This is a dummy macro for demonstration pursposes."
End Sub
Sub Macro4()
MsgBox "This is a dummy macro for demonstration pursposes."
End Sub


Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("EVT Tools&quot.Delete
End Sub

'------------------------------------
'DataDownload module
'-------------------------------'-------------------------------------------------------------
'-This all procedure is getting market rates from GE CRE web
'-------------------------------------------------------------

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

Sub test()
MsgBox DownloadFile("

http://www.gecapitalrealestate.com/marketrates/marketrates.asp?page=euro",

"C:\test.txt&quot
End Sub


Private Function ReadFileText(ByVal FileName As String) As String
On Error GoTo EHandler
Dim Handle As Integer
Dim strBegin, strEnd, lenght As Integer
Handle = FreeFile
Open FileName For Input As #Handle
ReadFileText = Input$(LOF(Handle), Handle)
strBegin = InStr(ReadFileText, &quot;<BODY&quot - 1
strEnd = InStr(ReadFileText, &quot;</BODY>&quot
strLenght = Len(ReadFileText)
ReadFileText = Replace(Left(Right(ReadFileText, strLenght - strBegin), strEnd - strBegin), &quot;&nbsp;&quot;, &quot;&quot
On Error Resume Next
Close #Handle
Exit Function
EHandler:
On Error Resume Next
Close #Handle
End Function

Sub MarketRates()
Set MyData = New DataObject
Dim b As Boolean
b = DownloadFile(&quot;

http://www.gecapitalrealestate.com/marketrates/marketrates.asp?page=euro&quot;,

&quot;C:\test.txt&quot
Workbooks.Add
Sheets(&quot;Sheet1&quot.Select
Sheets(&quot;Sheet1&quot.Name = &quot;EUR&quot;
MyData.SetText ReadFileText(&quot;C:\test.txt&quot
MyData.PutInClipboard
Range(&quot;A1&quot.Select
ActiveSheet.Paste
End Sub


'-----------------------------------

Thanks

 

[michaelbr55]

Try adding the module name before the Sub Name ie DataDownload.marketrates and type in lower case to see if the module can see the Sub on another module, if it sees it the text will change to the case of the Sub or Function you are calling, ie to MarketRates from marketrates.

Mike