How to declare global variable to access other functions in vba?

Question

I have browse button variable and coding inside the browse button event. Now I have to access those variable in another button event. How to declare that in vba ?

private sub commandbutton1_click()
Dim someFileName As Variant
Dim folderName As String
Dim i As Integer
Const STRING_NOT_FOUND As Integer = 0

'select a file using a dialog and get the full name with path included
 someFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

 If someFileName <> False Then

'strip off the folder path
folderName = vbNullString
i = 1

While STRING_NOT_FOUND < i
    i = InStr(1, someFileName, "\", vbTextCompare)  'returns position of the first       backslash "\"
    If i <> STRING_NOT_FOUND Then
        folderName = folderName & Left(someFileName, i)
        someFileName = Right(someFileName, Len(someFileName) - i)
    Else 'no backslash was found... we are done
        GetAFileName = someFileName

    End If
Wend

Else
GetAFileName = vbNullString
End If
end sub

private sub commandbutton2_click()

I have to access GetAFileName variable here?

Out of curiosity - why don't you accept answers, like at all? Many users have helped you, however, you don't vote on anything, accept anthing or provide any answers of your own - you find this fair? — Jook
– Jook, Commented Oct 26, 2012 at 10:58

Jook · Accepted Answer · 2012-10-26 11:59:44Z

2

This would be the structure you would have to use:

Option Explicit
private GetAFileName as string

private sub commandbutton1_click()
  Dim some As Variant
  some = "test2"
  GetAFileName = some
end sub

private sub commandbutton2_click()
  MsgBox GetAFileName
end sub

You have to define this GetAFileName outside of your functions, to access it from both of them.

By the way - you should use option explicit to make sure, every variable has a stated definition somewhere.

edited Oct 26, 2012 at 11:59

answered Oct 26, 2012 at 10:53

Jook

4,7523 gold badges31 silver badges54 bronze badges

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

1 Comment

Jook Over a year ago

my edit proofs basic functionality - if you are still getting nullstring, it is because button1 was not clicked, or you did not set GetAFileName - use the debugger.

jpsfer · Accepted Answer · 2012-10-26 10:53:41Z

1

Use enter code here Global VARNAME

Example: 'Run this function "aaa" and a message box is displayed with caption "1"

Global A
Function aaa()
    A = 1
    Call BBB
End Function

Function BBB()
    MsgBox (A)
End Function

Hope that helps!

answered Oct 26, 2012 at 10:53

jpsfer

5943 gold badges8 silver badges18 bronze badges

2 Comments

jpsfer Over a year ago

Just happened to be browsing the questions at the right time, I guess ;P

Daniel Over a year ago

Doesn't really matter, but using Global is not recommended. You should use Public instead (which does the same thing), as Global is only still around for backwards compatibility. Though since both subs are in the same module, I would go with Private like Jook's answer.

user1934049 · Accepted Answer · 2013-01-07 05:49:47Z

Option Compare Database
Option Explicit

Public Type BROWSEINFO
hwndOwner As Long
pidlRoot As Long
pszDisplayName As String
pszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Const MAX_PATH As Long = 260
Const dhcErrorExtendedError = 1208&
Const dhcNoError = 0&

'specify root dir for browse for folder by constants 'you can also specify values by constants for searhcable folders and options.

Const dhcCSIdlDesktop = &H0
Const dhcCSIdlPrograms = &H2
Const dhcCSIdlControlPanel = &H3
Const dhcCSIdlInstalledPrinters = &H4
Const dhcCSIdlPersonal = &H5
Const dhcCSIdlFavorites = &H6
Const dhcCSIdlStartupPmGroup = &H7
Const dhcCSIdlRecentDocDir = &H8
Const dhcCSIdlSendToItemsDir = &H9
Const dhcCSIdlRecycleBin = &HA
Const dhcCSIdlStartMenu = &HB
Const dhcCSIdlDesktopDirectory = &H10
Const dhcCSIdlMyComputer = &H11
Const dhcCSIdlNetworkNeighborhood = &H12
Const dhcCSIdlNetHoodFileSystemDir = &H13
Const dhcCSIdlFonts = &H14
Const dhcCSIdlTemplates = &H15

'constants for limiting choices for BrowseForFolder Dialog

Const dhcBifReturnAll = &H0
Const dhcBifReturnOnlyFileSystemDirs = &H1
Const dhcBifDontGoBelowDomain = &H2
Const dhcBifIncludeStatusText = &H4
Const dhcBifSystemAncestors = &H8
Const dhcBifBrowseForComputer = &H1000
Const dhcBifBrowseForPrinter = &H2000

'... you can get a lot more of these values from your integrated API viewer for constant specifcation or go to AllPai.net and see their samples.

Public Declare Function SHBrowseForFolder Lib "shell32.dll" (ByRef lpbi As BROWSEINFO) As Long

'corrected

Public Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" _
(ByVal hwndOwner As Long, ByVal nFolder As Long, ByRef pidl As Long) As Long



Public Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long

 Public Function BrowseForFolder(ByVal lngCSIDL As Long, _
 ByVal lngBiFlags As Long, _
 strFolder As String, _
 Optional ByVal hWnd As Long = 0, _
 Optional pszTitle As String = "Select Folder") As Long


Dim usrBrws As BROWSEINFO
Dim lngReturn As Long
Dim lngIDL As Long

If SHGetSpecialFolderLocation(hWnd, lngCSIDL, lngIDL) = 0 Then

'set up the browse structure here

With usrBrws
   .hwndOwner = hWnd
   .pidlRoot = lngIDL
   .pszDisplayName = String$(MAX_PATH, vbNullChar)
   .pszTitle = pszTitle
   .ulFlags = lngBiFlags
End With

'open the dialog

 lngIDL = SHBrowseForFolder(usrBrws)

 If lngIDL = 0 Then Exit Function

'if successful

 If lngIDL Then strFolder = String$(MAX_PATH, vbNullChar)

   'resolve the long value form the lngIDL to a real path

   If SHGetPathFromIDList(lngIDL, strFolder) Then
       strFolder = Left(strFolder, InStr(1, strFolder, vbNullChar))
   lngReturn = dhcNoError 'to show there is no error.
   Else
       'nothing real is available.
       'return a virtual selection
       strFolder = Left(usrBrws.pszDisplayName, InStr(1, usrBrws.pszDisplayName, vbNullChar))
    lngReturn = dhcNoError 'to show there is no error.
    End If
Else
 lngReturn = dhcErrorExtendedError 'something went wrong
End If


BrowseForFolder = lngReturn

End Function

user1934049 · Accepted Answer · 2013-01-07 13:53:00Z

0

Sub hth() 

With Application.FileDialog(msoFileDialogFolderPicker) 
    .AllowMultiSelect = False 
    .Show 

    If .SelectedItems.Count > 0 Then 
txt2.setfocus
        txt2.Text = .SelectedItems(1) 
    End If 

End With 

End Sub

answered Jan 7, 2013 at 13:53

user1934049

72 bronze badges

Comments

user1934049 · Accepted Answer · 2013-01-07 14:38:59Z

0

Private Sub Command9_Click()
Call BrowseForFolder(dhcCSIdlDesktop, dhcBifReturnOnlyFileSystemDirs, _
STRPATH2, pszTitle:="Select a folder:")
If STRPATH2 <> "" Then
STRPATH2 = Left(STRPATH2, Len(STRPATH2) - 1)
Text7.Value = STRPATH2
'DoCmd.Close acForm, "frm_generate_report", acSaveNo
'DoCmd.OpenForm "frm_generate_report", acNormal
End If
End Sub

answered Jan 7, 2013 at 14:38

user1934049

72 bronze badges

Collectives™ on Stack Overflow

How to declare global variable to access other functions in vba?

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related