Home > database >  What is the purpose of Declaring Variables in VBA if it can be done without declaring one?
What is the purpose of Declaring Variables in VBA if it can be done without declaring one?

Time:06-19

I have a simple code from a book on VBA that displays a MsgBox with greetings. The code is below:

Sub GreetMe5()
Dim Msg As String
If Time < 0.5 Then Msg = “Morning”
If Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon”
If Time >= 0.75 Then Msg = “Evening”
MsgBox “Good “ & Msg
End Sub

My question is that what's the purpose of declaring the variable 'Msg as String' if it could be done without declaring one? The code without the variable should be like this:

Sub Greetings1()
If Time < 0.5 Then MsgBox "Good Morning"
If Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon"
If Time >= 0.75 Then MsgBox "Good Evening"
End Sub

Thank You

CodePudding user response:

The small example of code you give is in itself quite poor quality as it is what you would expect a naive user of VBA to come up with. Letts say this is part of a programm you will distribute to overseas offices in your company. The greeting should of course be in the local language. DO you want to write 50 versions of the code you propose, 1 for each language used, or would something more advanced be better.

Sub GreetUser()
Dim Msg As Greeting  ' where greeting is the name of an enumeration
If Time < 0.5 Then Msg = Greeting.Morning
If Time >= 0.5 And Time < 0.75 Then Msg = Greeting.Afternoon
If Time >= 0.75 Then Msg = Greeting.Evening
MsgBox Messages(getsystemLocale).Greetings(msg)
End Sub

CodePudding user response:

VBA supports two ways of programming.

The first is typeless programming. Easy to use, compact, very suited to small macros. This is also the syntax that VBScript chose to implement from VBA.

Note Dim A implies Dim A as Variant.

The second is programming efficiently with typed programming, ie programming to the CPU like C does.

In typeless everything is an abstract datatype that requires processing to prepare it every time you use it. It needs to be a specific type when the CPU uses it.

When typed is requires no processing when used as that type. Remembering that Basic auto converts types in both cases if needed known as Let coercion (https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/74614d3e-7068-4c33-b149-029534522472). So you have to declare it correctly for speed as Basic fixes slow declares.

This is a Variant in memory. Note most fields are one from the union.

typedef struct tagVARIANT {
  union {
    struct {
      VARTYPE vt;
      WORD    wReserved1;
      WORD    wReserved2;
      WORD    wReserved3;
      union {
        LONGLONG     llVal;
        LONG         lVal;
        BYTE         bVal;
        SHORT        iVal;
        FLOAT        fltVal;
        DOUBLE       dblVal;
        VARIANT_BOOL boolVal;
        VARIANT_BOOL __OBSOLETE__VARIANT_BOOL;
        SCODE        scode;
        CY           cyVal;
        DATE         date;
        BSTR         bstrVal;
        IUnknown     *punkVal;
        IDispatch    *pdispVal;
        SAFEARRAY    *parray;
        BYTE         *pbVal;
        SHORT        *piVal;
        LONG         *plVal;
        LONGLONG     *pllVal;
        FLOAT        *pfltVal;
        DOUBLE       *pdblVal;
        VARIANT_BOOL *pboolVal;
        VARIANT_BOOL *__OBSOLETE__VARIANT_PBOOL;
        SCODE        *pscode;
        CY           *pcyVal;
        DATE         *pdate;
        BSTR         *pbstrVal;
        IUnknown     **ppunkVal;
        IDispatch    **ppdispVal;
        SAFEARRAY    **pparray;
        VARIANT      *pvarVal;
        PVOID        byref;
        CHAR         cVal;
        USHORT       uiVal;
        ULONG        ulVal;
        ULONGLONG    ullVal;
        INT          intVal;
        UINT         uintVal;
        DECIMAL      *pdecVal;
        CHAR         *pcVal;
        USHORT       *puiVal;
        ULONG        *pulVal;
        ULONGLONG    *pullVal;
        INT          *pintVal;
        UINT         *puintVal;
        struct {
          PVOID       pvRecord;
          IRecordInfo *pRecInfo;
        } __VARIANT_NAME_4;
      } __VARIANT_NAME_3;
    } __VARIANT_NAME_2;
    DECIMAL decVal;
  } __VARIANT_NAME_1;
} VARIANT;

https://docs.microsoft.com/en-us/windows/win32/api/oaidl/ns-oaidl-variant


This is a VBA string (known as a BStr)

A BSTR is a composite data type that consists of a length prefix, a data string, and a terminator. The following table describes these components.

Item            Description

Length prefix   A four-byte integer that contains the number of bytes in the following data string. It appears immediately before the first character of the data string. This value does not include the terminator.

Data string   A string of Unicode characters. May contain multiple embedded null characters.

Terminator          A NULL (0x0000) WCHAR.

Previously, some versions of Mac operating systems defined this data type in a different way, and some Microsoft code running on Mac computers used this data type. This documentation no longer describes these obsolete details.

A BSTR is a pointer. The pointer points to the first character of the data string, not to the length prefix.

BSTRs are allocated using COM memory allocation functions, so they can be returned from methods without concern for memory allocation.

https://docs.microsoft.com/en-us/previous-versions/windows/desktop/automat/bstr

  • Related