Dim as Byte versus Dim as String

[SMB Cooper]

What is the effect if I Dim as Byte a String data type value? I know that it will generate a Type Mismatch error, which can be countered by an Error Handling line.
<o> </o>
I am asking, because the “as Byte” variable executed my macro really fast as compared to when I use the “as String” variable.
<o> </o>
I am currently using the “as String” variable because my limited knowledge with VBA tells me that it is the right procedure… even though it is slow.
<o> </o>
I tried to Google for an answer in vain. Hope that somebody could enlighten me.

 

[mikerickson]

Bytes are single memory locations with a value of 0 to 255
Strings are concatenations of characters each character takes up as much memory as a Byte.

Bytes are faster. But they are in no way substitutable for strings.

What are you doing? If a Byte can be made to do the work of a String,.... that's an unusual routine.

 

[Fencliff]

Theoretically you can use a byte array in place of strings, but apart from some obscure COM shenanigans, it's not a particularly useful technique, because the implicit conversion to string is quite an expensive operation.

However to demonstrate:

    Dim myString        As String
    Dim myByteArray()   As Byte

    myString = "Hello World!"
    myByteArray() = "Hello World!"
    
    Debug.Print myString
    Debug Print myByteArray

Will print "Hello World!" twice.

I think your performance gains are due to the fact that your code is actually failing, but you don't notice it because you're ignoring your errors.

If this isn't the case, I'd love to see what you're doing.

 

[SMB Cooper]

Thanks.. appreciate your prompt replies...

My macro is just a simple Select Case which will run two more macros when the ActiveCell.Value is equal to "numbers formatted as TEXT" example 1, 2, 3 etc. and some look like this -2-2, -2-3, -5-10, etc. I have used the TYPE(Value) FUNCTION and it returns 2, meaning they are realy TEXT. The Macros are both running OK except that the "as Byte" macro is really fast.


============= As Byte ==================

Sub MacroByte()

Application.ScreenUpdating = False

On Error Resume Next
Dim VarByte As Byte
VarByte = ActiveCell.Value
Select Case ActiveCell.Value
Case "1"
ActiveCell.Offset(0, 0).Range("A1").Select
Application.Run "'Files01.xls'Macro1"
Range("B2").Select
Application.Run "Files02.xls!Macro2"
Case "-2-2"
ActiveCell.Offset(0, 0).Range("A1").Select
Application.Run "'Files01.xls'Macro1"
Range("B3").Select
Application.Run "Files02.xls!Macro2"
Case Else
MsgBox "This SelectCase does not exist yet!", vbOKOnly + vbInformation, "MacroByte/Files01/Module1:"
Exit Sub
End Select

If Err Then MsgBox "Error Handling Message!", vbOKOnly + vbInformation, "MacroByte/Files01/Module1:"
Err = 0
On Error GoTo 0

Application.ScreenUpdating = True

End Sub


============ As String ===============


Sub MacroString()

Application.ScreenUpdating = False
Dim VarByte As String
VarByte = ActiveCell.Value
Select Case ActiveCell.Value
Case "1"
ActiveCell.Offset(0, 0).Range("A1").Select
Application.Run "'Files01.xls'Macro1"
Range("B2").Select
Application.Run "Files02.xls!Macro2"
Case "-2-2"
ActiveCell.Offset(0, 0).Range("A1").Select
Application.Run "'Files01.xls'Macro1"
Range("B3").Select
Application.Run "Files02.xls!Macro2"
Case Else
MsgBox "This Select Case does not exist yet!", vbOKOnly + vbInformation, "MacroByte/Files01/Module1:"
Exit Sub
End Select
Application.ScreenUpdating = True
End Sub

 

[Fencliff]

Hi,

You are storing ActiveCell.Value into a variable, but never using it again, but instead referring to ActiveCell.Value again in the Select Case statement. The data type of the variable has nothing to do with the performance - and in fact, in the MacroByte procedure, if the cell value is a string (any text) or an integer larger than 255, the value would never get stored in the variable. With numbers <= 255 this would work.

The reason your code is running faster, is because the disabled error handling is being cascaded up the call stack to the macros you are running. I can't say what's going on there, but either the code is producing incorrect results without you noticing, or the code in the called procedure is structured so that ignored error conditions are somehow favourable for performance, but still produce correct results (no idea how this could occur).

In any case, you can remove the whole VarByte variable and the assignment, since you're not using it anyway.

 

[SMB Cooper]

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CBOYETG%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Thank you very much for your help, Fencliff. I remove the variable and the assignment, and the macro still works… therefore the data type of the variable has nothing to do with the performance.
<o> </o>
It is clear to me now, that it is because of the disabled error handling being cascaded to the two other macros… however, it is still not clear to me why the code produce correct results. I have checked the results cell by cell.
<o> </o>
Anyway, I was really tempted to use the code because it is faster but refrain from doing because I am afraid it will give incorrect results later (without me noticing it).
<o> </o>
Again, thank you… I can sleep soundly now.