(Solved) – How to remove dynamic digits of prefix in EXCEL VBA

  • by

I want to change the format of the values in the Array(OutArray) mentioned in the below code. The Array will return as six digit numbers, with preceding 0’s to make up the numbers. If the actual values are 5678, 5000, 8, the Array will return as 005678, 005000, 000008. I couldn’t change this datatype format since its the only way the instrument will return data.

I’m trying to remove the Prefix 0 that comes along with each value. But the class is that, I don’t know how many 0’s will be there as Prefix since the values returned are dynamic. I’m struck in this line of code,

                InArray = MID(Step, "", "")

Where I actually wanted to identify the first non-zero value. Is there any way to do this?

 Public Function copy_XY()

   Dim idn As String
   idn = instrument.ReadString()  ' Reading a value from instrument (Dynamic)    
   Dim OutArray() As String
   OutArray = Split(idn)
   Dim Step As Variant
   Dim InArray As String
   For Each Step in OutArray
      InArray = MID(Step, "", "")
   Next Step

End function

When I use CInt as below I’m getting overflow error.

   Dim Val() As Int
   Val(0) = CInt(OutArray(0))

Leave a Reply

Your email address will not be published. Required fields are marked *