Based on some of the logic and objects in this excel standalone subroutine, please write…
- User
Based on some of the logic and objects in this excel standalone subroutine, please write a Word VBA function which runs on the active document to list the margins in a comma-delimited list for every section with greater than 1 page of length:
Sub FindMargins()
Dim wordApp As Word.Application Set wordApp = New Word.Application Dim resultsSheet As Worksheet Set resultsSheet = ActiveSheet Dim curCell As Range Set curCell = resultsSheet.Range("A3") Dim intResult As Long Dim docCur As Document Dim intSecNum As Integer Dim curSecPage As Integer Dim prevSecPage As Integer Dim dblStartTime As Double Dim vDirectory As String Dim prodName As String ' Asks the user which file to run in Application.FileDialog(msoFileDialogFolderPicker).InitialFileName = "C:" intResult = Application.FileDialog(msoFileDialogFolderPicker).Show If intResult <> 0 Then vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) Else MsgBox ("There was an issue with the selection. Please try again.") Exit Sub End If prodName = Dir(vDirectory & "\" & "*.doc*") ' Cycles through each document in the selected folder Do While prodName <> "" Set docCur = wordApp.Documents.Open(Filename:=vDirectory & "\" & prodName, Visible:=False, ReadOnly:=True, AddToRecentFiles:=False) wordApp.ScreenUpdating = False dblStartTime = Timer ' --- First Row: Margins --- curCell.Value = prodName curCell.Offset(0, 2).Value = "Margins (Left & Right)" ' Loop through sections to write margin data starting in Column C For intSecNum = 1 To docCur.Sections.Count On Error Resume Next curCell.Offset(0, 2 + intSecNum).Value = docCur.Sections(intSecNum).PageSetup.LeftMargin & " & " & docCur.Sections(intSecNum).PageSetup.RightMargin On Error GoTo 0 Next intSecNum ' --- Second Row: Page Counts --- Set curCell = curCell.Offset(1, 0) ' Move to the next row for the second set of data curCell.Value = prodName curCell.Offset(0, 2).Value = "Page Counts" prevSecPage = 0 For intSecNum = 1 To docCur.Sections.Count On Error Resume Next curSecPage = docCur.Sections(intSecNum).Range.Information(wdActiveEndPageNumber) curCell.Offset(0, 2 + intSecNum).Value = curSecPage - prevSecPage prevSecPage = curSecPage On Error GoTo 0 Next intSecNum ' Write the elapsed time in Column B for both rows Dim elapsedTime As Double elapsedTime = Round(Timer - dblStartTime, 2) curCell.Offset(-1, 1).Value = elapsedTime ' Time for margin row curCell.Offset(0, 1).Value = elapsedTime ' Time for page count row docCur.Close SaveChanges:=wdDoNotSaveChanges ' Move to the next available row for the next document Set curCell = curCell.Offset(1, 0) prodName = Dir Loop wordApp.Quit Set wordApp = Nothing ' release the memory MsgBox ("Done")End Sub
- Assistant
Certainly! Below is a Word VBA function that runs on the active document and returns a comma-delimited list of the margins (LeftMargin and RightMargin) for every section that has a length greater than 1 page.
Explanation:
- It loops through each section in the active document.
- It calculates the number of pages in the section by subtracting the previous section's last page number from the current section's last page number.
- If the section length is greater than 1 page, it appends the margins (LeftMargin and RightMargin) to a string.
- Margins are returned in points (Word's internal unit). You can convert to inches or cm if needed.
Word VBA Function:
Function ListMarginsForLongSections() As String Dim sec As Section Dim intSecNum As Integer Dim curSecPage As Long Dim prevSecPage As Long Dim marginList As String Dim secLength As Long prevSecPage = 0 marginList = "" For intSecNum = 1 To ActiveDocument.Sections.Count On Error Resume Next ' Get the last page number of the current section curSecPage = ActiveDocument.Sections(intSecNum).Range.Information(wdActiveEndPageNumber) On Error GoTo 0 secLength = curSecPage - prevSecPage If secLength > 1 Then With ActiveDocument.Sections(intSecNum).PageSetup ' Append margins as "LeftMargin & RightMargin" in points marginList = marginList & .LeftMargin & " & " & .RightMargin & "," End With End If prevSecPage = curSecPage Next intSecNum ' Remove trailing comma if any If Len(marginList) > 0 Then marginList = Left(marginList, Len(marginList) - 1) End If ListMarginsForLongSections = marginList End Function
How to use:
- Open the VBA editor in Word (
Alt + F11). - Insert a new module.
- Paste the above function.
- Run it from the Immediate Window with:
? ListMarginsForLongSections() - Or call it from another macro or assign it to a button.
Optional: Convert margins from points to inches (1 inch = 72 points)
If you want the margins in inches instead of points, replace the line inside the
Withblock with:marginList = marginList & Format(.LeftMargin / 72, "0.00") & " & " & Format(.RightMargin / 72, "0.00") & ","
Let me know if you want me to help with that or any other customization!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.