Excel

Outils
- Rubberduck : plugin pour l’éditeur VBA, permet de le rendre plus pratique https://rubberduckvba.com/
- Outils pour décomposer les formules et les lire plus facilement
  https://www.formulaboost.com/parse
  https://www.excelformulabeautifier.com/
Autres sources d'infos:

Macro pour faire une log des actions faites par les gens dans un classeur (pour faire du suivi)

Code simple:

'Macro pour faire une log des actions dans le classeur
'Nécessite une feuille appelée "Log"
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Log" Then Exit Sub
    Dim cell As Range
    For Each cell In Target
        With Sheets("Log").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 5)
            .Value = Array(Sh.Name, cell.Address(0, 0), cell.Value, Now, Application.UserName)
        End With
    Next cell
End Sub

Autre ressource: VBA-Log

Variables dynamiques dans doc word, pilotées par excel :

Utiliser des codes de champs, qui sont modifiables par une macro vba:
Variable à ajouter dans le doc word :
Sous l'onglet Insertion, cliquez sur Champ. Dans la liste Catégorie, cliquez sur la catégorie de champ que vous voulez insérer : Formule personnalisée Cette catégorie utilise les fonctions Feuille de forme que vous entrez dans la zone Formule personnalisée pour créer une formule.

*{ DOCVARIABLE “Cranebeam” \\* MERGEFORMAT }*

Ladite macro, pour un fichier word situé dans le même dossier que l’excel :

Function OfferExportUS()

    Dim objWord
    Dim objDoc
    Dim CheminFichier As String
    Dim FeuilleOffer As Worksheet
    Dim machineType As String

    ' Assurez-vous que le classeur est ouvert et actif
    Set FeuilleOffer = ThisWorkbook.Sheets("Offer")
    
    machineType = Range("OfferMachineType").Value
    
    CheminFichier = ThisWorkbook.Path & "\Offer_model_" & machineType & "_US.docx"

    ' Word Objekt erstellen
    Set objWord = CreateObject("Word.Application")
    ' Word Dokument öffnen
    Set objDoc = objWord.Documents.Open(CheminFichier)
    
    '___________Enregistrer sous OFFER TITLE_____________
    ' Créez un nom de fichier basé sur la valeur d'OfferTitle
    NomFichier = Replace(OfferTitleValue & ".docx", " ", "_")
    ' Déterminez le chemin complet du dossier du classeur actif
    DossierClasseur = ThisWorkbook.Path
    CheminFichier2 = DossierClasseur & "\" & NomFichier
    
    objDoc.SaveAs CheminFichier2
     
    'Copie des informations
    objDoc.Variables("OfferTitle").Value = FeuilleOffer.Range("LinkEN_OfferTitle").Value
		objDoc.Variables("OfferTitle2").Value = FeuilleOffer.Range("LinkEN_OfferTitle2").Value
    
    '***************RESET VARIABLES************
    'For Each objVar In objDoc.Variables
    '    ' Réinitialiser la valeur de la variable à vide
    '    objVar.Value = objVar.Name
    'Next objVar
    '******************************************
    
    ' Mettez à jour le document Word
    objDoc.Fields.Update
    
    ' Enregistrez le document Word (après les modifications)
    objDoc.Save
    
    objWord.Visible = True
    
    'Afficher la fenetre Word (ne marche pas ?)
    objWord.Activate

End Function

Faire apparaitre des images au survol d’une cellule

Méthode manuelle : https://www.youtube.com/watch?v=pPekR2rzwWI

Script: VBA:

  
  MsgBox("Welcome")
  
  scriptDir = Left(WScript.ScriptFullName, InStrRev(WScript.ScriptFullName, "\") - 1) & "\"
  
  'MsgBox(scriptDir)
  
  spreadsheetname = "Joulin Gantry Inventory.xlsx"
  
  spreadsheet     = scriptDir & spreadsheetname
  
  'MsgBox(spreadsheet)
  
  'spreadsheet     = "C:\Users\crclayton\Desktop\your_spreadsheet.xlsx"
  image_directory = scriptDir & "Images\"
  image_type      = ".jpg"       
         
  column_with_image_names   = 6
  column_to_put_comments_in = 6
  scale_images_down_by 	    = 1
   
   
  Set objExcel = CreateObject("Excel.Application")
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set oImage = CreateObject("WIA.ImageFile")
   
  Set objWorkbook = objExcel.Workbooks.Open(spreadsheet)
  objExcel.Application.Visible = True
   
  for i = 1 to objExcel.ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count
   
      image = objExcel.Cells(i,column_with_image_names).Value
      image_file = image_directory & image & image_type
		
      if image <> "" and fso.FileExists(image_file) then 

	      oImage.LoadFile image_file
		  'MsgBox("Loaded image " & image_file)
   
	      with objExcel.Cells(i,column_to_put_comments_in)		
		      .AddComment " "
		      .Comment.Shape.Fill.UserPicture image_file
		      .Comment.Shape.Width = oImage.Width/scale_images_down_by
		      .Comment.Shape.Height = oImage.Height/scale_images_down_by
	      end with
      end if	
  next
  
    MsgBox("Bye")

Original script post (reddit)

https://www.reddit.com/r/excel/comments/3amak9/script_to_create_an_excel_picture_column_shows/
Script to create an Excel picture column (shows pictures on cell hover)#

[Pro Tip](<https://www.reddit.com/r/excel/?f=flair_name%3A%22Pro%20Tip%22>)

View result here: http://i.imgur.com/s1icxkr.gif
I recently learned how to put images in an excel spreadsheet that are visible only on hover. What you do here, is create an empty comment, then format the comment's background fill to be a custom image. However, that would take ages to do manually, so I wrote a small script to do it and wanted to share.
To use it, put a bunch of images in a directory. The image names should correspond with the values in a column of the spreadsheet. So for example, let's say you have this spreadsheet:

             C:\\Users\\crclayton\\Desktop\\your_spreadsheet.xlsx

                  letters	   numbers    notes
                  a            1          do
                  b            2          re
                  c            3          mi
                  d            4          fa

And these image files:

            C:\\Users\\crclayton\\Desktop\\Images\\
                  ↳ do.jpg
                  ↳ re.jpg
                  ↳ mi.jpg
                  ↳ fa.jpg

You would take the following code, specify the variables at the top, put it in notepad save it as a .vbs file (this is VBScript, not VBA), then click it to run it.

  spreadsheet     = "C:\\Users\\crclayton\\Desktop\\your_spreadsheet.xlsx"
  image_directory = "C:\\Users\\crclayton\\Desktop\\Images\\"
  image_type      = ".jpg"

  column_with_image_names   = 3
  column_to_put_comments_in = 4
  scale_images_down_by 	    = 3

  Set objExcel = CreateObject("Excel.Application")
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set oImage = CreateObject("WIA.ImageFile")

  Set objWorkbook = objExcel.Workbooks.Open(spreadsheet)
  objExcel.Application.Visible = True

  for i = 1 to objExcel.ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count

      image = objExcel.Cells(i,column_with_image_names).Value
      image_file = image_directory & image & image_type

      if image <> "" and fso.FileExists(image_file) then

	      oImage.LoadFile image_file

	      with objExcel.Cells(i,column_to_put_comments_in)
		      .AddComment " "
		      .Comment.Shape.Fill.UserPicture image_file
		      .Comment.Shape.Width = oImage.Width/scale_images_down_by
		      .Comment.Shape.Height = oImage.Height/scale_images_down_by
	      end with
      end if
  next

Of course if you use giant images and have many, many rows, this will really bloat out your spreadsheet so use your best judgement.

Excel

Script to create an Excel picture column (shows pictures on cell hover)#