title: Using a string variable to set value of .NumberFormat in Excel VBA
body: <p>I'm trying to set a custom number format with the following result:<br>
  the value "22,800" becomes "Europe: 22.8 M"</p>
  
  <p>I can achieve this using the number formatting window, and using:<br>
  "Europe:" #,##0.0, "M"; "Europe:" -#,##0.0, "M"</p>
  
  <p>I can also achieve this in VBA window using <br>
  ActiveCell.NumberFormat = """Europe:"" #,##0.0, ""M""; ""Europe:"" -#,##0.0, ""M"""</p>
  
  <p>Here is the issue: I'm running a loop function which takes the values from different sheets and pastes them onto a summary sheet - I need the region (e.g. "Europe") to show up next to the number, but I don't want to write the code for the loop function 9 different times for my 9 regions. </p>
  
  <p>My attempted solution, here I would just change the value of the variable "region" for each loop:</p>
  
  <pre><code>Dim region As String
  Dim numformat As String
  region = "Europe"
  numformat = Chr(34) + Chr(34) + Chr(34) + region + ":" + Chr(34) + Chr(34) + " #,##0.0, " + Chr(34) + Chr(34) + "M" + Chr(34) + Chr(34) + "; " + Chr(34) + Chr(34) + region + ":" + Chr(34) + Chr(34) + " -#,##0.0, " + Chr(34) + Chr(34) + "M" + Chr(34) + Chr(34) + Chr(34)
  Selection.NumberFormat = numformat
  </code></pre>
  
  <p>Which formats the number as "Europe: #,##0.0, M; Europe: -#,##0.0, M" - it doesnt show the number at all, just this string.</p>
  
  <p>However, if I use ?numformat in the immediate window to return the string's value, and then copy/paste that value so that <br>
  Selection.NumberFormat = """Europe:"" #,##0.0, ""M""; ""Europe:"" -#,##0.0, ""M"""</p>
  
  <p>Then, I get the correct number format, "Europe: 22.8 M"</p>
  
  <p>But this doesn't help me, because now the region name is hard-coded again. Can anyone help identify what I'm doing wrong, or identify an alternative solution?</p>
  
  <p>Cheers,</p>
  
  <p>B</p>