Category: VBA

Excel VBA from shell command line

Dim objXL Dim wsSheet ‘– The full path to the template file must be provided in the command line ‘– level eg c:\Temp\_excel> npfmacro.vbs c:\temp\_excel\npftemplate.xlsm ‘– where the npfmacro.vbs is located in the directory c:\temp\_excel ‘– the npftemplate.xlsm has a macro in the module called npf_errorasses which is ‘– looking for a file called batch.xlsx […]

Desktop VBS to rename files

Drop into target folder and execute option explicit Dim fso, folder, file Dim sCurPath, sExtension Dim sOldTargetEnv Dim sNewTargetEnv Dim sNewName ‘ Where are we sCurPath = CreateObject(“Scripting.FileSystemObject”).GetAbsolutePathName(“.”) ‘ Confirm path & get prefix sOldTargetEnv = InputBox(“Enter substring to replace e.g. PROD”) sNewTargetEnv = InputBox(“Enter replacement substring e.g. TEST”) if sOldTargetEnv <> “” and sNewTargetEnv […]

Personal VBA

Lost old Personal.. so starting to collect new one.. Private Sub BorderRowOnTop(row As Long)     With ActiveSheet.Rows(row).Borders(xlEdgeTop)         .LineStyle = xlContinuous         .ColorIndex = 0         .TintAndShade = 0         .Weight = xlThin     End With    End Sub Private Sub ColourRow(row As Integer, colourSet1 As Boolean, inSelectionOnly As Boolean)     Dim r As Range […]

MS Excel custom function generate random strings

Pinched from someone else, (needs additional character sets, e.g. mixed case, text & numbers)     Dim Rand As String     Dim i As Integer, RndNo As Integer, XSet As Integer     Dim MyCase As Integer          Application.Volatile     Select Case MySet     Case Is = “1” ‘Upper case         MyCase = 65: XSet = 26 […]

Collections object in MS Excel

20150304 Revising a complex test data generator worksheet with VBA scripts I have hidden ranges that specify interaction options for matched rows in the data entry area. I need to increase speed when referencing this range for each row so looking at options for reading and accessing it in memory.  Previous script was for each […]