(Learned later that I should use [Excel] prefix in the title. Cannot edit title. But the information is there.)

In Excel VBA, I want to select a range (n rows, m columns) and remove duplicates.

The following works for 7 columns:

r.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes

But I want it work with a variable number of columns.

I've tried the following. None works.

' Selection should be the upper-left corner (header row)
Set r = Range(Selection, Selection.End(xlDown).End(xlToRight))
r.Select

r.RemoveDuplicates
r.RemoveDuplicates Header:=xlYes

ncol = r.Columns.Count
ReDim dupecol(1 To ncol)
For i = 1 To ncol: dupecol(i) = i: Next
r.RemoveDuplicates Columns:=dupecol, Header:=xlYes
r.RemoveDuplicates Columns:=(dupecol), Header:=xlYes

The first two RemoveDuplicates simply do nothing (!).

The last code snippet results in error 5: invalid procedure call or argument in both cases.

I confirmed that r.Address, ncol, Typename(dupecol), dupecol(1) and dupecol(ncol) are what they should be.

Any idea how to make it work without using hardcoded Array(...)?

EDIT.... For testing purposes, I used the same conditions that worked with hardcoded Array(...). So, r.Address comprises only 7 columns, multiple rows and no adjacent data; ncol is 7; Typename(dupecol) is Variant(); LBound(dupecol) is 1 (*); UBound(dupecol) is 7; dupecol(1) is 1; and dupecol(ncol) is 7.

(*) UPDATE.... As u/ZetaPower noted, LBound must be zero for it work with RemoveDuplicates Columns:=(dupecol). That is, it requires ReDim dupecol(0 to ncol-1).

Writing VBA back into Excel files is not “just editing text in a zip file”

I went down the rabbit hole of exploring how VBA modules are stored inside Office files, and the format is much stranger than I expected.

The most surprising part is how many layers are involved.

For a modern .xlsm file, the path looks roughly like this:

Excel workbook
  -> ZIP / Open XML package
    -> xl/vbaProject.bin
      -> Microsoft Compound File Binary
        -> VBA project streams
          -> compressed module source

So replacing a VBA module is not just:

open file
replace text
save file

It is closer to:

preserve the workbook container
extract vbaProject.bin
parse the compound file
decompress the VBA streams
find the real source offset
replace only the source body
recompress it correctly
invalidate Office caches
drop stale compiled-cache streams
avoid breaking protected or signed projects
put everything back without touching unrelated bytes

A few details made this more interesting than expected:

• the dir stream is itself compressed
• module source does not always start at byte zero
• VBA source uses the project codepage, not UTF-8
• short final compression chunks cannot be written as raw chunks
• Office stores compiled cache streams that should not be rewritten
• digital signatures become invalid after source changes
• the real test is “does Excel reopen it without a "your workbook is broken" prompt?”

The main lesson:

Editing VBA inside Office files is not just editing a script file inside of a zip file. It's way more complicated. You have to maintain a small filesystem, a compression format, a project manifest, and a set of Office-specific safety rules at the same time.

Implementation guide:

https://github.com/WilliamSmithEdward/pyOpenVBA/blob/main/docs/ms-ovba-implementation-guide_v2.md

References:

• Microsoft MS-OVBA specification
• Microsoft Compound File Binary format
• Office Open XML package structure
• Real Excel workbooks tested against Excel for Microsoft 365

Hi all,

I am trying to write a personal compendium, sort of self-studying wikipedia document.

What i want the program to do is automatically detect dates written in a specific format (i prefer the 'Jan 01, 2000' format, but am not picky) sort them into chronological order, and list them into a master timeline underneath a header in the document. I would like each date in the timeline to link back to the original source within the document.

Is this something I can easily do?

I understand other programs will be more suitable for longterm goals and organisation, but it's too much of an undertaking to learn a whole new document program right now.

>Context: I was trying to make a template for my Uni so that I don't need to manually edit anything, but I ran into two issues which I kind-of Solved

Ms Word Ver: MS Word-365 (2026)
Windows Ver: Windows 10 Pro (Home would work too maybe)
OneDrive: Uni Acc. (Personal Would work too maybe)

Problem: File Not Auto-Syncing. Error: Macro Enabled, disabling it

Fix: Just click on the 'File' Section and it will Auto-Sync

[How to use Macro/ .dotm Template]

Step 1: Make your template file first

Step 2: Save it as ".dotm" [Word will automatically save it at it's default position, re-open it and press Left-Alt + F11

Step 3: Double click on "ThisDocument" and paste the given macro with your formatting

Step 4: Save it 'CTRL+S' and exit everything, re-open word.

Step 5: Below the "Good Morning-etc." Greetings will be the template section, click on "More Templates" and head to "Personal" Section, Open the file and it should automatically save, Do the work-around I suggested.

Macro/ VBA Used:

    Dim srNo As String
    srNo = InputBox("Enter Experiment No.:", "New Document")
    ' Only Edit the "Enter Exp No.: " if needed.
    
    If srNo = "" Then Exit Sub
    
    Dim defaultName As String
    defaultName = "YourExpName" & srNo & "-YourID.docx"
    
    Dim saveFolder As String
    saveFolder = "OneDrive Folder Path"
    
    ' Since the post is for OneDrive Sync i.e One-Drive Path
    ' Create the folder if it doesn't exist yet
    If Dir(saveFolder, vbDirectory) = "" Then
        MkDir saveFolder
    End If
    
    ActiveDocument.SaveAs2 FileName:=saveFolder & defaultName, _         
       FileFormat:=wdFormatXMLDocumentPrivate Sub Document_New()
End Sub

If anyone has a better way to do it please comment on this post

I have a workbook I'm creating that will handle a repeatable task (first worksheet is tables/graphics, second worksheet is formulas/calculations, next 5 worksheets are newly imported data). I want the imported worksheets to be dynamically renamed in accordance to text in cell A2 in order to simplify formula references and functionality.

VBA Code I have so far:

ThisWorkbook()

Private Sub Workbook_Open()
   Dim i As Long
   Dim rawname As String
   Dim modname As String

   Application.ScreenUpdating = False
   For i = 3 To 7
      Call TabName(Worksheets(i))
   Next i
   Application.ScreenUpdating = True
End Sub

Module1()

Sub TabName(ws As Worksheet)
   With ws
      rawname = Range("A2").Value
      modname = Split(rawname, ":")(0)
      ActiveSheet.Name = modname
   End With
End Sub

When I open the workbook, it will only rename the active worksheet, and not increment to all other worksheets. I can make a new one active, save, close, reopen, and it will rename it. I've struggle with automatic dynamic worksheet renaming macros in general, definitely misunderstanding a process within excel and/or vba. I can add running macros upon opening workbook to my list of misunderstandings.

So basic parts I'm looking for a solution for:

- Activate a macro upon opening workbook

- Properly increment said macro to multiple worksheets within workbook

Hi all,

I’m running into an issue with a VBA script that downloads files from a SharePoint folder using the REST API.

Most of the time, the code works perfectly, it connects, retrieves the file list, and downloads everything without any issue.

But randomly, I get this error:

MsgBox "Failed to connect to SharePoint API", vbCritical

This happens when the XMLHTTP request does not return status 200.

The confusing part is:

• I can still open the SharePoint site manually in my browser without any issue
• No changes in URL or permissions
• Same code, same machine

So I don’t understand why the connection sometimes fails and sometimes works fine.

My setup:

• Using MSXML2.XMLHTTP to call SharePoint REST API
• Using URLDownloadToFile to download files
• No explicit authentication handled in VBA (relying on logged-in session)

If this approach is fundamentally unreliable, I’m open to switching methods but still prefer to use in VBA

Below i provide full code setup to review.

Option Explicit

#If VBA7 Then

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _

ByVal pCaller As LongPtr, _

ByVal szURL As String, _

ByVal szFileName As String, _

ByVal dwReserved As LongPtr, _

ByVal lpfnCB As LongPtr) As Long

#Else

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

#End If

Sub Download_All_From_SharePoint()

Dim apiURL As String

Dim json As String

Dim xmlhttp As Object

Dim saveFolder As String

Dim fileName As String

Dim fileURL As String

Dim arr() As String

Dim i As Long

apiURL = "https://TEST.sharepoint.com/sites/TEST/TEST/_api/web/GetFolderByServerRelativeUrl('/sites/TEST/TEST/TEST/TEST/Confirming Temp')/Files"

saveFolder = "C:\Temp\CONFIRMING\"

If Dir(saveFolder, vbDirectory) = "" Then MkDir saveFolder

Set xmlhttp = CreateObject("MSXML2.XMLHTTP")

xmlhttp.Open "GET", apiURL, False

xmlhttp.setRequestHeader "Accept", "application/json"

xmlhttp.Send

If xmlhttp.Status <> 200 Then

MsgBox "Failed to connect to SharePoint API", vbCritical

Exit Sub

End If

json = xmlhttp.ResponseText

arr = Split(json, """Name"":""")

For i = 1 To UBound(arr)

fileName = Split(arr(i), """")(0)

fileURL = "https://TEST.sharepoint.com/sites/TEST/TEST/TEST/TEST/Confirming Temp/" & Replace(fileName, " ", "%20") & "?download=1"

If URLDownloadToFile(0, fileURL, saveFolder & fileName, 0, 0) = 0 Then

Debug.Print "Downloaded: " & fileName

Else

Debug.Print "FAILED: " & fileName

End If

Next i

MsgBox "All files downloaded!", vbInformation

End Sub

Hi! I'm the old (deleted) r/rubberduckvba account, now wearing a "social media manager" hat for my new private company 9562-7303 Québec inc., which was founded this last spring specifically to fulfill the vision of this project.

I've spent the past few weeks working double-time exclusively on the implementation and documentation of the spiritual successor to the Rubberduck VBIDE add-in project, RDCore - a modern, extensible, observable language server and analytics platform... not a VBE add-in.

As of today, the RDCore repository and its massive documentation site are public (although, contributions still closed pending a CLA).

While not a VBA project in itself, it seems to me that the complete reimplementation of the VBA language from its specifications makes an objectively interesting project to share and discuss here; perhaps a bit understandably underwhelming from an end-user (VBA dev) standpoint at this stage though.

This open-core project is very transparently managed with an eventual commercial interest, and the implications of its eventual completion have massive (very, very good!) consequences for all the legacy VBA code currently in existence worldwide.

> Note: since this is literally the first post of a technically brand new account, I'm not sure what the basis might be to calculate a 10% linking to my own content.. I hope this is fine!

Building SciMat : A Scientific Formatting Tool for Microsoft Word

I've been working on a VBA project called SciMat, a tool that automates scientific formatting directly inside Microsoft Word.

It started as a simple chemistry formatter, but it's gradually evolving into a broader scientific writing assistant.

Current Features

- Automatic formatting of chemical formulas (H₂O, CO₂, Fe₂(SO₄)₃)

- Ion and charge formatting (NH₄⁺, SO₄²⁻, MnO₄⁻)

- Isotope notation support (²³⁸U, ¹⁴C)

- Markdown-style math conversion using Word's Equation Editor

- Built entirely with Word VBA and native wildcard searches

Currently in Development

- Expanded molecule and ion support

- Periodic table integration

- Statistical reporting tools

- ANOVA and regression notation formatting

- Greek symbol shortcuts (α, β, μ, σ)

One of the most interesting challenges has been building everything using Word's native wildcard engine instead of traditional regex libraries.

The goal is simple: reduce repetitive formatting work for students, researchers, teachers, and anyone writing scientific documents in Word.

I'd love to hear what scientific formatting tasks you would automate if Word could do them automatically.

I would like to replace a bookmark inside a word document with a picture.

I have:

Sub makro()


Dim wApp As Object
Dim wDoc As Object
Dim bR As Object
Dim b1 As String
Dim b2 As String
Dim fp As String
Dim stuff As String

b1 = "Bookmark 1"
b2 = "Bookmark 2"
fp = "my file path"
stuff = "some text"

Set wApp = CreateObject("Word.Application")
wApp.Visible = True
Set wDoc = wApp.Documents.Open(fp)

Set bR = wDoc.Bookmarks(b1).Range
bR.text = stuff        'this is how I do it with text

Set bR = wDoc.Bookmarks(b2).Range
'???

'I know of wDoc.Content.InlineShapes.Addpicture FileName:=filepath etc. but not how to apply it at the position of the bookmark.

Set wApp = Nothing
Set wDoc = Nothing
Set bR = Nothing

End Sub

Is there a simple way of replacing b2 with a picture, like I did with b1?

I might have missed some necessities in the code. Like closing/quitting the word document and application, but that is irrelevant to the question and I can figure that out on my own.

I would appreciate any ideas and suggestions.

I use Office 365.