Option Compare Text
Option Explicit
Function Excludes(Ext As String) As Boolean
'Function purpose: To exclude listed file extensions from hyperlink listing
Dim X, NumPos As Long
'Enter/adjust file extensions to EXCLUDE from listing here (only last 3 chars):
X = Array("exe", "bat", "dll", "zip", "xls", "lsx", "lsm", "ipx", ".db")
On Error Resume Next
NumPos = Application.WorksheetFunction.Match(Ext, X, 0)
If NumPos > 0 Then Excludes = True
On Error GoTo 0
End Function
Sub A_HyperlinkFileList()
'Macro purpose: To create a hyperlinked list of all files in a user
'specified directory, including file size and date last modified
'NOTE: The 'TextToDisplay' property (of the Hyperlink object) was added
'in Excel 2000. This code tests the Excel version and does not use the
'Texttodisplay property if using XL 97.
Dim fso As Object, _
ShellApp As Object, _
File As Object, _
SubFolder As Object, _
Directory As String, _
Problem As Boolean, _
ExcelVer As Integer
'Turn off screen flashing
Application.ScreenUpdating = False
'Create objects to get a listing of all files in the directory
Set fso = CreateObject("Scripting.FileSystemObject")
'Prompt user to select a directory
Do
Problem = False
Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\\")
On Error Resume Next
'Evaluate if directory is valid
Directory = ShellApp.self.Path
Set SubFolder = fso.GetFolder(Directory).Files
If Err.Number <> 0 Then
If MsgBox("You did not choose a valid directory!" & vbCrLf & _
"Would you like to try again?", vbYesNoCancel, _
"Directory Required") <> vbYes Then Exit Sub
Problem = True
End If
On Error GoTo 0
Loop Until Problem = False
'Set up the headers on the worksheet
With ActiveSheet
With .Range("A1")
.Value = "Listing of all files in:"
.ColumnWidth = 40
'If Excel 2000 or greater, add hyperlink with file name
'displayed. If earlier, add hyperlink with full path displayed
If Val(Application.Version) > 8 Then 'Using XL2000+
.Parent.Hyperlinks.Add _
Anchor:=.Offset(0, 1), _
Address:=Directory, _
TextToDisplay:=Directory
Else 'Using XL97
.Parent.Hyperlinks.Add _
Anchor:=.Offset(0, 1), _
Address:=Directory
End If
End With
With .Range("A2")
.Value = "File Name"
'.Interior.ColorIndex = 15
With .Offset(0, 1)
.ColumnWidth = 15
.Value = "Date Modified"
'.Interior.ColorIndex = 15
.HorizontalAlignment = xlCenter
End With
With .Offset(0, 2)
.ColumnWidth = 15
.Value = "File Size (Kb)"
'.Interior.ColorIndex = 15
.HorizontalAlignment = xlCenter
End With
End With
End With
'Adds each file, details and hyperlinks to the list
For Each File In SubFolder
If Not Excludes(Right(File.Path, 3)) = True Then
With ActiveSheet
'If Excel 2000 or greater, add hyperlink with file name
'displayed. If earlier, add hyperlink with full path displayed
If Val(Application.Version) > 8 Then 'Using XL2000+
.Hyperlinks.Add _
Anchor:=ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0), _
Address:=File.Name, _
TextToDisplay:=File.Name
Else 'Using XL97
.Hyperlinks.Add _
Anchor:=ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0), _
Address:=File.Path
End If
'Add date last modified, and size in KB
With .Range("A65536").End(xlUp)
.Offset(0, 1) = File.DateLastModified
With .Offset(0, 2)
.Value = WorksheetFunction.Round(File.Size / 1024, 1)
.NumberFormat = "#,##0.0"
End With
End With
End With
End If
Next
End Sub
Sub B_CleanUpList()
'
' CleanUpList Macro
'
'
' Rows("1:2").Select
' Selection.Delete Shift:=xlUp
' Range(["B2:C" & CountA(A:A)]).Select
' Selection.Delete Shift:=xlToLeft
Range(["A3:A" & CountA(A:A)]).Select
Selection.Copy
Range(["D3:D" & CountA(A:A)]).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("D3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(4, 9), Array(5, 2), Array(7, 2), Array(9, 9)), _
TrailingMinusNumbers:=True
Range("G3").Select
ActiveCell.FormulaR1C1 = "=DATEVALUE(CONCATENATE(C[-2],""/"",C[-1],""/"",C[-3]))"
Selection.Copy
Range(["G3:G" & CountA(A:A)]).Select
ActiveSheet.Paste
Range(["G3:G" & CountA(A:A)]).Select
Application.CutCopyMode = False
Selection.Copy
Range(["D3:D" & CountA(A:A)]).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Range("E1").Select
Selection.NumberFormat = "mm/dd/yyyy;@"
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
End Sub Write, Run & Share VB.net code online using OneCompiler's VB.net online compiler for free. It's one of the robust, feature-rich online compilers for VB.net language, running on the latest version 16. Getting started with the OneCompiler's VB.net compiler is simple and pretty fast. The editor shows sample boilerplate code when you choose language as VB.net. OneCompiler also has reference programs, where you can look for the sample code to get started with.
OneCompiler's VB.net online editor supports stdin and users can give inputs to programs using the STDIN textbox under the I/O tab. Following is a sample VB.net program which takes name as input and prints hello message with your name.
Public Module Program
Public Sub Main(args() As string)
Dim name as String = Console.ReadLine() ' Reading input from STDIN
Console.WriteLine("Hello " & name) ' Writing output to STDOUT
End Sub
End Module
Visual Basic is a event driven programming language by Microsoft, first released in the year 1991.
Variable is a name given to the storage area in order to identify them in our programs.
Simple syntax of Variable declaration is as follows
Dim variableName [ As [ New ] dataType ] [ = initializer ]
variableName = value
If condition-expression Then
'code
End If
If(conditional-expression)Then
'code if the conditional-expression is true
Else
'code if the conditional-expression is false
End If
If(conditional-expression)Then
'code if the above conditional-expression is true
Else If(conditional-expression) Then
'code if the above conditional-expression is true
Else
'code if the above conditional-expression is false
End If
If(conditional-expression)Then
'code if the above conditional-expression is true
If(conditional-expression)Then
'code if the above conditional-expression is true
End If
End If
Select [ Case ] expression
[ Case expressionlist
'code ]
[ Case Else
'code ]
End Select
For counter [ As datatype ] = begin To end [ Step step ]
'code
[ Continue For ]
'code
[ Exit For ]
'code
Next [ counter ]
For Each element [ As datatype ] In group
'code
[ Continue For ]
'code
[ Exit For ]
'code
Next [ element ]
While conditional-expression
'Code
[ Continue While ]
'Code
[ Exit While ]
'Code
End While
Do { While | Until } conditional-expression
'Code
[ Continue Do ]
'Code
[ Exit Do ]
'Code
Loop
Do
'Code
[ Continue Do ]
'Code
[ Exit Do ]
'Code
Loop { While | Until } conditional-expression
Procedure is a sub-routine which contains set of statements. Usually Procedures are written when multiple calls are required to same set of statements which increases re-usuability and modularity.
Procedures are of two types.
Functions return a value when they are called.
[accessModifiers] Function functionName [(parameterList)] As returnType
'code
End Function
Sub-procedures are similar to functions but they don't return any value.
Sub ProcedureName (parameterList)
'Code
End Sub