Fix text-formatted numbers

Beside the suggestion from the following Microsoft page, re-assigning the value with Macro will also resolve.

Selection.Value = Selection.Value

Importing data from Excel is not easy. If the formatting of a column changed (the first 100 cells is number and the second 100 cells is text), the SSIS may convert the second 100 cells to NULL values. Cleaning the formats must be done before the import.


Determine Monday

In VB, the (Saturday Mod 7) = 0.

' VBA Example: Determine Monday
Public Function Monday(a_dt As Date) As Date
    Dim t As Long
    t = Int(a_dt)
    Monday = t - ((t + 6) Mod 7) + 1
End Function


' VBA Example: Determine Monday
' version 2
Public Function Monday(a_dt As Date) As Date
    Dim t As Long
    t = Int(a_dt) - 1
    Monday = t - (t Mod 7) + 2
End Function


' VBScript: determine the first monday of the month of a given date
Public Function First_Monday(a_dt) 
    Dim t

    t = Int(a_dt) - Day(a_dt) + 7
    First_Monday =  t - ((t + 6) Mod 7) + 1
End Function

Excel VBA: Value, Value2, and NumberFormat

The default property of an Excel Cell is the Value property.  It returns the formatted value of the Excel Cell.  The underlying value is stored in the Value2property.  The format is stored in NumberFormat property.  If the underlying value and the format do not get along, the Value property will return an overflow error.


For example, the Excel Cell A1 is formatted as date m/d/yyyy, and the users enter a string “20070101”.  Since the date values are stored as Double in Excel, the system will display the string “##########” at Cell A1 to mean invalid date.  If the VBA code accesses the Value property, the code will return the overflow error.  But, the value “20070101” can still be retrieved from the Value2 property.


The Excel formatting algorithm will try to distinguish the date value from other values.  If the users enter “6 months”, the system will understand it is a string. Both Value property and Value2 property will return the string “6 months”.

Excel VBA: Default Property Of A Class

Bullen’s book has better example.

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

By: Stephen Bullen; Rob Bovey; John Green

Publisher: Addison-Wesley Professional


Add the following line of code on a blank line immediately following the Property Get statement.

Attribute Value.VB_UserMemId = 0

Now, your Property Get  procedure will look something like the following.  The line in bold is what you added.

Property Get Value() As Long
Attribute Value.VB_UserMemId = 0
    Value = pValue
End Property


  MultiUse = -1  'True
Attribute VB_Name = "MyCollection"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

Private m_collection As Collection

Property Get Item(ByVal id As Variant) As Variant
Attribute Item.VB_UserMemId = 0
    Set Item = m_collection(id)
End Property

Public Function NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = m_collection.[_NewEnum]
End Function

Public Sub Add(Item As Variant, Optional key As Variant, Optional Before As Variant, Optional After As Variant)
    Call m_collection.Add(Item, key, Before, After)
End Sub

Public Property Get Count() As Long
    Count = m_collection.Count
End Property

Public Sub Remove(Index As Variant)
    Call m_collection.Remove(Index)
End Sub

Private Sub Class_Initialize()
    Set m_collection = New Collection
End Sub

Private Sub Class_Terminate()
    Set m_collection = Nothing
End Sub

Excel QueryTable Mystery

I have a QueryTable Object on the Excel spreadsheet. It always works when I use the External Data menu to refresh it. It will only work at the first refresh when I use the VBA to refresh it. The code is the following.


The good news is I figure out a workaround solution. By specifying the parameter, the same QueryTable works.

ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False