pytwc
commented
1 year ago @jordbowie, I have Windows 11, 64 bits and Microsoft 365. I use the following approach in Excel VBA to get all named formulas from any active workbook. I only have to run the Sub named AFEExportNamesInNameManager(). Load this code in a new Module, and check if it helps you. I’m sure there could be easier ways, but this works for me. Open to any comments.
Option Explicit
'2020-01-02 Compilations taken or adapted from:
' https://stackoverflow.com/questions/35416662/text-to-clipboard-in-vba-windows-10-issue
' https://docs.microsoft.com/es-es/office/vba/access/Concepts/Windows-API/send-information-to-the-clipboard
#If VBA7 Then
Private Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hWnd As LongPtr) As LongPtr
Private Declare PtrSafe Function EmptyClipboard Lib "User32" () As LongPtr
Private Declare PtrSafe Function CloseClipboard Lib "User32" () As LongPtr
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "User32" (ByVal wFormat As LongPtr) As LongPtr
Private Declare PtrSafe Function GetClipboardData Lib "User32" (ByVal wFormat As LongPtr) As LongPtr
Private Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As Long
Private Declare PtrSafe Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
#Else
Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hWnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32.dll" () As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long
#End If
Sub AFEExportNamesInNameManager()
'2023-03-06 Exportar masivamente al clipboard TODAS las fórmulas con nombre existentes en el Name Manager de Excel, incluidos Comentarios (descripción).
'Es útil para extraer y reutilizar snippets ya existentes en un archivo Excel, precargarlos en un gist (Ctrl+V, paste), y
'descargarlos a través del botón cloud-download del AFE Advanced Formula Environment.
'También es útil para aislar algunos Names útiles que vayan surgiendo en nuevos libros o modelos, y apendizarlos al gist existente(s).
Dim nm As name
Dim s As String, pref As String
Const pAp = vbNewLine
s = ""
'Loop active workbook names.
For Each nm In ActiveWorkbook.Names
pref = ""
'November 2022 AFE ChangeLog says:
'When defining names within modules, leading doc comments of the form {"/** */"} will be saved to the name manager.
If nm.Comment <> "" Then pref = "/**" & nm.Comment & "*/" & pAp
If nm.visible Then s = s & pref & nm.name & " " & nm.RefersTo & ";" & pAp & pAp
Next nm
'Load full string to clipboard.
SetClipboard sUniText:=s
Debug.Print "Ok, all named formulas are now in the clipboard. Check them in a text editor and massively paste them to a new or existing gist..."
End Sub
Private Sub SetClipboard(sUniText As String)
'2020-01-02 Compilations taken or adapted from:
' https://stackoverflow.com/questions/35416662/text-to-clipboard-in-vba-windows-10-issue
' https://docs.microsoft.com/es-es/office/vba/access/Concepts/Windows-API/send-information-to-the-clipboard
#If VBA7 Then
Dim iStrPtr As LongPtr
Dim iLock As LongPtr
#Else
Dim iStrPtr As Long
Dim iLock As Long
#End If
Dim iLen As Long
Const GMEM_MOVEABLE As Long = &H2
Const GMEM_ZEROINIT As Long = &H40
Const CF_UNICODETEXT As Long = &HD
OpenClipboard 0&
EmptyClipboard
iLen = LenB(sUniText) + 2&
iStrPtr = GlobalAlloc(GMEM_MOVEABLE + GMEM_ZEROINIT, iLen)
iLock = GlobalLock(iStrPtr)
lstrcpy iLock, StrPtr(sUniText)
GlobalUnlock iStrPtr
SetClipboardData CF_UNICODETEXT, iStrPtr
CloseClipboard
End Sub
jack-williams
linuxiamo
BB-IG
I've written several functions using the Names>Functions tab, but now I'd like to share these to other workbooks or other users. I hoped that the functions that I wrote would appear in the Workbook module so that I could just copy the whole thing to a gist, but it appears that the Workbook module is separate from the Names tab.
Is there a simple way to copy these functions into a gist? It looks to me like I would have to individually copy each function definition into a gist/workspace, which is complicated by the fact that the Names>Functions tab hides the LAMBDA part of the definition.