VBA: Convert Constant Name to Constant Value…?

[zero269]

Hello,

Is it possible to convert a Constant Name (string value) to its Constant Value (Long value)?

Constants enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

For example, in my following test code, I want to use the Horizontal Alignment value of xlCenter. The values are in an array, along with other settings for a given column. VBA will not accept "xlCenter" as a String value from my array.

I didn't find anything promising online, so in the meantime, I'm using a Select Case as a workaround… although I'm concerned how this will be a viable solution for a variety of other use cases where a Constant Name can't be used as a String Value. The only other thing I can think of is to store both Name and its Value in the Table that I'm using for my Settings Array.

Any advice or tips would be greatly appreciated...

    Dim CValue as Long

    For i = LBound(arrSettings) To UBound(arrSettings)
      
      'Convert Constant Name to Value
      Select Case arrSettings(i, 5)
          Case "xlCenter"
              CValue = -4108
          Case "xlGeneral"
              CValue = 1
          Case Else
      End Select
      
      'Column Width
      wsLogs.Columns(i).ColumnWidth = arrSettings(i, 4)
      wsLogs.Columns(i).EntireColumn.HorizontalAlignment = CValue

    Next i

Thank you...

 

[myall_blues]

You can create a 'type' for the array record like so:

Private Type arrRecord
    cValue As XlHAlign
End Type

and then in your routine you can use the value directly.

Sub test()
    Dim myarr As arrRecord
    myarr.cValue = xlHAlignCenter
    Sheet1.Columns(1).EntireColumn.HorizontalAlignment = myarr.cValue
End Sub

 

[zero269]

You can create a 'type' for the array record like so:

Private Type arrRecord
    cValue As XlHAlign
End Type

and then in your routine you can use the value directly.

Sub test()
    Dim myarr As arrRecord
    myarr.cValue = xlHAlignCenter
    Sheet1.Columns(1).EntireColumn.HorizontalAlignment = myarr.cValue
End Sub

Hi myall_blues,

I was looking into your recommendation with using the Type Statement. I have to admit this is way over my head.

Type statement (VBA)

Office VBA reference topic

learn.microsoft.com

XlHAlign enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

After a ton of online research, the Select Case seemed like a prime candidate, although I can't say that's really what I should use consdiering how I'm filling my array for this purpose.

My workbook in this case is a neverending WIP... I recently decided the best way to ensure consistency was to create a Settings sheet with a Table that stores all the settings per Table/Column and Log Report. In this example, I was storing xlCenter and xlGeneral for the column alignments, only later to realize I couldn't use the Name in the macro. So, I added another column that stores it's Value and I'm able to use that instead.

This is definitley the amateur approach... but I I'm not sure I really have much of a choice considering my lack of skill with this topic.

Switching to arrays recently enabled me to essentially go from this... confusing and difficult to maintain with random changes.

'Column Width
Range("B:E").ColumnWidth = 5         'Book No.(B), Book Level(E)
Range("D:D").ColumnWidth = 6        'AR Points(D)
Range("F:H").ColumnWidth = 7         'Word Count(F), Test Date(G), Read Date(H)
Range("A:A,I:I").ColumnWidth = 8     'Quiz(A), Book Status(I)
Range("J:J").ColumnWidth = 20         'Series(J)
Range("C:C").ColumnWidth = 40       'Title(C)

... to this... using the value from the Align Constant column above.

'Format Columns
For i = LBound(arrSettings) To UBound(arrSettings)
    With wsLogs.Columns(i)
        .ColumnWidth = arrSettings(i, 4)
        .EntireColumn.HorizontalAlignment = arrSettings(i, 5) '-4108 Constant value
        .Font.Name = "Segoe UI Light"
        .Font.Size = 11
        .NumberFormat = arrSettings(i, 3)
    End With
    Next i

My issue was as I need to add or move Table Columns, I had to keep updating the Column Letters in the first example. The 2nd one lets me loop through each column and apply individual settings instead of focusing on grouping them together.

Thanks again myall_blues, and best regards...