vba

Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]

by

There is a very large PDF from Microsoft which documents how to extract functions from the vbaproject.bin: https://interoperability.blob.core.windows.net/files/MS-OVBA/%5bMS-OVBA%5d.pdf [Source] This resource is current & available as of June 27, 2019. The event that this link goes stale (Microsoft periodically changes their permalink structure or otherwise alters how they implement their documentation/answer repositories, etc.), search for … Read more

Show “Open File” Dialog

by

My comments on Renaud Bompuis’s answer messed up. Actually, you can use late binding, and the reference to the 11.0 object library is not required. The following code will work without any references: Dim f As Object Set f = Application.FileDialog(3) f.AllowMultiSelect = True f.Show MsgBox “file choosen = ” & f.SelectedItems.Count Note that the … Read more

Unmerging excel rows, and duplicate data

by

This is a VBA solution. This macro will search every cell in the active sheet to see if they are merged. If they are, it stores the range of the merged cells in a temp. range variable, unmerges the cells, then fills the range with the value of the first cell in the unmerged range … Read more

How do I sort a collection?

by

Late to the game… here’s an implementation of the MergeSort algorithm in VBA for both Arrays and Collections. I tested the performance of this implementation against the BubbleSort implementation in the accepted answer using randomly generated strings. The chart below summarizes the results, i.e. that you should not use BubbleSort to sort a VBA collection. … Read more

Reverse order of For Each loop

by

It’s not possible to loop backwards using the for each loop syntax. As an alternative you can use a For i = a To 1 Step -1 loop: Sub reverseForEach() Dim i As Long, rng As Range Set rng = ActiveSheet.Range(“A1:B2”) For i = rng.Cells.Count To 1 Step -1 Debug.Print rng.item(i).Address ‘ Or shorthand rng(i) … Read more

Wait for Shell to finish, then format cells – synchronously execute a command

by

Try the WshShell object instead of the native Shell function. Dim wsh As Object Set wsh = VBA.CreateObject(“WScript.Shell”) Dim waitOnReturn As Boolean: waitOnReturn = True Dim windowStyle As Integer: windowStyle = 1 Dim errorCode As Long errorCode = wsh.Run(“notepad.exe”, windowStyle, waitOnReturn) If errorCode = 0 Then MsgBox “Done! No error to report.” Else MsgBox “Program … Read more

In VBA get rid of the case sensitivity when comparing words?

by

There is a statement you can issue at the module level: Option Compare Text This makes all “text comparisons” case insensitive. This means the following code will show the message “this is true”: Option Compare Text Sub testCase() If “UPPERcase” = “upperCASE” Then MsgBox “this is true: option Compare Text has been set!” End If … Read more

Excel VBA Password via Hex Editor

by

I have your answer, as I just had the same problem today: Someone made a working vba code that changes the vba protection password to “macro”, for all excel files, including .xlsm (2007+ versions). You can see how it works by browsing his code. This is the guy’s blog: http://lbeliarl.blogspot.com/2014/03/excel-removing-password-from-vba.html Here’s the file that does … Read more

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)