Category Archives: Electronics

Excel VBA functions for formatting numbers using SI prefixes (and back)

Excel does not have any built-in way of formatting numbers such that the numbers are displayed in a neatly readable way using SI prefixes, like m for milli (10^-3), k for kilo (10^3), µ for micro (10^-6) etc. Here I provide a VBA function I wrote to fill this need. In addition, I added support for using the prefix notation of Spice circuit simulators where both m and M are interpreted as milli and Meg has to be used to signify mega. Also, u has to be used instead of µ to indicate micro in Spice.

As icing on the cake, I also wrote a function to convert the resulting strings back to numbers.

The functions are called FormatPrefix and ValPrefix. Source code with detailed descriptions of the input arguments are shown further down in this post.

Below is a link to an Excel document with the functions built into a VBA module and a worksheet with an example table of values fed to the function to display its output for a large variation of input arguments:

[Excel] ExcelPrefixFormattingFunction.xls

Here is a screenshot demonstrating the formatting:

Excel table showing the output of FormatPrefix for a large set of input arguments.
Excel table showing the output of FormatPrefix for a large set of input arguments.

The source code is shown below:

Public Function FormatPrefix(value As Double, _
                             Optional precision As Integer = 3, _
                             Optional blankZero As Boolean = False, _
                             Optional space As Boolean = False, _
                             Optional spice As Boolean = False) As String
    ' Convert a number to a string, usually with an SI prefix at the end.
    '
    ' Arguments:
    ' value is the value to be converted.
    ' Optional precision argument determines number of significant digits to be printed (default 3).
    ' Optional blankZero argument determines if zero should return the empty string (default false).
    ' Optional space argument determines if a space should be inserted after the number but before
    '  a possible prefix (default false).
    ' Optional spice argument determines if Spice prefixes should be used instead of metric ones
    '  (default false). The only differences are Meg vs M and u vs µ.
    ' Values above or equal to 1e15 or below 1e-18 are printed using scientific notation without
    ' prefix.
    ' The decimal indicator is always a point, even if the locale says it should be a comma.
    '
    ' Written by Per Magnusson, Axotron, 2016-02-26
    ' This code is public domain and comes without any warranty. Enjoy!
    
    ' Handle the value 0
    If value = 0 Then
        If blankZero Then
            FormatPrefix = ""
        Else
            If space Then
                FormatPrefix = "0 "
            Else
                FormatPrefix = "0"
            End If
        End If
        Exit Function
    End If
    
    ' Handle negative numbers
    sign = 1
    If value < 0 Then         sign = -1 ' store the sign         value = -value ' make value positive until later     End If          ' Calculate exponent and mantissa     exponent = Int(Log(value) / Log(10#))     mantissa = Round(value / (10 ^ exponent), precision - 1)     If mantissa >= 10 Then
        ' Handle the case where the mantissa is rounded up to 10
        exponent = exponent + 1
        mantissa = mantissa / 10
    End If
    exponent3 = 3 * Int(exponent / 3) 'exponent rounded down to nearest multiple of 3
    
    ' Find the right prefix
    If exponent >= 15 Then
        ' Too big to represent with the supported prefixes, use scientific notation
        prefix = "sci"
    ElseIf exponent >= 12 Then
        prefix = "T"
    ElseIf exponent >= 9 Then
        prefix = "G"
    ElseIf exponent >= 6 Then
        If spice Then
            prefix = "Meg"
        Else
            prefix = "M"
        End If
    ElseIf exponent >= 3 Then
        prefix = "k"
    ElseIf exponent >= 0 Then
        prefix = ""
    ElseIf exponent >= -3 Then
        prefix = "m"
    ElseIf exponent >= -6 Then
        If spice Then
            prefix = "u"
        Else
            prefix = "µ"
        End If
    ElseIf exponent >= -9 Then
        prefix = "n"
    ElseIf exponent >= -12 Then
        prefix = "p"
    ElseIf exponent >= -15 Then
        prefix = "f"
    ElseIf exponent >= -18 Then
        prefix = "a"
    Else
        ' Too small to represent with the supported prefixes, use scientific notation
        prefix = "sci"
    End If
        
    If prefix = "sci" Then
        ' Use scientific notation
        formatStr = "0"
        If precision > 1 Then
            formatStr = formatStr & "."
            For ii = 1 To precision - 1
                formatStr = formatStr & "0"
            Next
        End If
        formatStr = formatStr & "e-0"
        prefix = ""
        value = sign * mantissa * 10 ^ exponent
    Else
        decimals = precision - (exponent - exponent3) - 1
        If decimals > 0 Then
            formatStr = "0."
            For ii = 1 To decimals
                formatStr = formatStr & "0"
            Next
        Else
            formatStr = "0"
        End If
        value = sign * mantissa * 10 ^ (exponent - exponent3)
    End If
    optSpace = ""
    If space Then
        optSpace = " "
    End If
    FormatPrefix = Replace(Format(value, formatStr) & optSpace & prefix, ",", ".")
    'FormatPrefix = formatStr & optSpace & prefix
End Function



Public Function ValPrefix(str As String) As Double
    ' Convert a number formatted as a string using FormatPrefix() back to a number.
    '
    ' Arguments:
    ' str is the string to be converted
    ' Error checking is very limited.
    '
    ' Written by Per Magnusson, Axotron, 2016-02-26
    ' This code is public domain and comes without any warranty. Enjoy!
    
    exponent = 0
    prefix = ""
    strlen = Len(str)
    
    If StrComp(Right(str, 1), "A") >= 0 Then
        ' There is a letter at the end of the value
        ii = 1
        While StrComp(Mid(str, strlen - ii, 1), "A") >= 0
            ii = ii + 1
        Wend
        prefix = Mid(str, strlen - ii + 1)
        Select Case prefix
            Case "T"
                exponent = 12
            Case "G"
                exponent = 9
            Case "M"
                exponent = 6
            Case "Meg"
                exponent = 6
            Case "k"
                exponent = 3
            Case "m"
                exponent = -3
            Case "u"
                exponent = -6
            Case "µ"
                exponent = -6
            Case "n"
                exponent = -9
            Case "p"
                exponent = -12
            Case "f"
                exponent = -15
            Case "a"
                exponent = -18
            Case Else
                ' Error, handle it somehow?
                ValPrefix = "Prefix error"
                Exit Function
        End Select
    End If
    ValPrefix = Val(str) * 10 ^ exponent
    
End Function

Crosstalk Problems when Running I2C Signals in a Cable

An I2C bus (or SMBus) can fail to work for various reasons. A well known reason is that there may be too much capacitance on the bus causing too slow low-to-high transitions. 400 pF is the maximum bus capacitance according to the I2C specification, but this is not necessarily a hard limit in practice. The problem of excessive capacitance can often be remedied by lowering the data rate, decreasing the value of the pull-up resistors or somehow reducing the bus capacitance by changed routing, shortened cables or using a different kind of cable.

This article however describes a different and perhaps less obvious problem that can occur while running an I2C bus in a cable, namely crosstalk, and what to do about it, if it becomes a problem.

Here is a photo of the cross section of the simple telephone cable used in the experiments in this article. The labels show the initial signal assignment that were used to provide both power and an I2C bus to a temperature sensor.

Cross section of the cable with initial signal assignment.
Cross section of the cable with initial signal assignment.

4.7 kohm were used as pull-up resistors. Below is a scope plot of the resulting signals when using 70 cm of cable and a data rate of approximately 200 kHz.

Crosstalk between I2C signals. 70 cm of cable, signals on adjacent wires, 4.7 kohm pull-up. Yellow signal is SCL and blue is SDA. Red arrows show crosstalk events.

The red arrows show where one signal is disturbed by a fast transition from high to low on the other signal. The green arrow shows that no disturbance can be seen on SCL when SCL is low, despite the fast transition from high to low on SDA. The reason that the signals are only disturbed while they are high is that in the high state, it is only a relatively weak pull-up resistor that keeps the signal at its proper level, so a small amount of current injected into the signal can change the voltage quite a bit. When an I2C signal is low, it is held low by a strong transistor which has an on-resistance of only a few tens of ohms so it would require a current that is two orders of magnitude stronger to cause a disturbance of the same amplitude on a low I2C signal than on a signal in the high state.

If the negative going glitches caused by the crosstalk become large enough, they may cause the bus to fail in various ways as the signals are misinterpreted, so it is important to keep these under control. The I2C specification defines valid high logic levels as being at least 70% of VDD. 70% of 3.3 V is 2.31 V and the glitches in the plot above dip down to this level, which is bad.

Coupling mechanism

The mechanism for coupling from the disturbing signal (the aggressor) to the disturbed signal (the victim) is the capacitance between the wires in the cable. A schematic model of the I2C-bus and cable (drawn using LTspice) is shown below.

Model of cable with I2C signals on adjacent wires.
Model of the cable with I2C signals on adjacent wires.

R1 and R2 are the I2C pull-up resistors. C1-C4 are the capacitances between adjacent wires in the cable while C5 and C6 are the capacitances between the diagonal wires. C7 and C8 are stray capacitances on the PCBs as well as capacitances at the pins of the ICs. S1 is a switch that models the open-drain output of the driving pin while V1 controls when the switch is closed and when it is open. The values of several of the components are set as parameters (within curly braces) to make it easier to modify them and adapt them to the actual values of a specific instance of the circuit. The capacitances shown in the schematic are reasonably representative of the real circuit discussed above.

Below are the simulated waveforms of the SCL and SDA signals when the I2C pull-up resistors are stepped from 1k via 2k2 and 4k7 to 10kohms.

Simulated waveforms for the circuit above. The pull-up resistor value is stepped through the values 1k, 2k2, 4k7 and 10k. The smaller the resistance, the steeper the edges.

One can note that the amplitude of the glitches and the steepness of the edges match the measurement pretty well for the 4k7 pull-up resistor (the next slowest traces). This supports that the model is fairly accurate.

We can see that the initial glitch in the SDA waveform has (almost) the same amplitude regardless of the the value of the pull-up resistors. This might not be expected, but is a consequence of the fact that the initial part of the step is dominated by the capacitive voltage division in the circuit. The capacitive divider consists of C1 which injects charge into the parallel combination of C4, C5 and C7. Right after the step, the voltages on the capacitors start to recover and how quickly this happens is determined by the value of the pull-up resistor, so the 1k resistor gives a ten times quicker recovery than the 10k resistor. But note that it does not significantly reduce the magnitude of the glitch.

There is also a little crosstalk happening during the slower positive edge of the aggressor. This crosstalk is much less of a problem since it has a positive amplitude and thus either increases the already positive amplitude of the victim signal or does not affect a low victim signal since that is actively being pulled low and is thus almost immune to the disturbance.

Reduced pull-up resistances

A lower value of the pull-up resistors did not seem to do much good according to the simulation, but let’s anyway verify this in reality. Below is the same measurement as before, but with 2.2 kohm pull-up resistors.

Crosstalk between I2C signals. 70 cm of cable. Signals on adjacent wires. 2.2 kohm pull-up. Yellow signal is SCL and blue is SDA.
Crosstalk between I2C signals. 70 cm of cable, signals on adjacent wires, 2.2 kohm pull-up. Yellow signal is SCL and blue is SDA.

As predicted by the simulation, the amplitude of the glitches remain the same, while the positive edges have become steeper.

Modified cable configuration

So, we could not reduce the amplitude of the crosstalk by the old standard trick of reducing the pull-up resistance. So is there something else that we can try?

Since the coupling mechanism is capacitive, we would like to reduce the capacitance between the aggressor and victim. This can actually be done without changing cables if we change which wire is used for what signal. To do this, the I2C signals are put on diagonal instead of adjacent wires as follows:

Cross section of the cable with improved signal assignment.
Cross section of the cable with improved signal assignment.

(If it had instead been a four-wire ribbon cable, putting the I2C signals on the outer wires and power and GND on the two inner had been the way to go. In a twisted-pair cable, the I2C signals should be twisted with power or GND and absolutely not with each other.)

The resulting crosstalk is shown in the plots below for 4.7k and 2.2k pull-ups.

I2C 4k7 diagonal cable
Crosstalk between I2C signals. 70 cm of cable, signals on diagonal wires, 4.7 kohm pull-up. Yellow signal is SCL and blue is SDA.
Crosstalk between I2C signals. 70 cm of cable. Signals on adjacent wires. 2.2 kohm pull-up. Yellow signal is SCL and blue is SDA.
Crosstalk between I2C signals. 70 cm of cable, signals on diagonal wires, 2.2 kohm pull-up. Yellow signal is SCL and blue is SDA.

As can be seen, the glitches have been substantially reduced and are now harmless. This can also be simulated by just changing the simulation schematic slightly to move the aggressor diagonally from the victim:

sch_diagonal_cable
Model of cable with I2C signals on diagonal wires.

The resulting waveforms are shown below.

wave_diagonal_cable
Simulated waveforms for the circuit with the I2C signals diagonally in the cable. The pull-up resistor value is stepped through the values 1k, 2k2, 4k7 and 10k. The smaller the resistance, the steeper the edges.

The simulation matches the measurement well also in this case.

So the method of placing the I2C signals far away from each other in the cable is apparently effective, just as expected.

A less elegant possibility is to add more capacitance between the I2C signals and GND, provided the bus capacitance is not already too large. This will affect the capacitive voltage division in a positive manner and reduce the crosstalk by increasing the capacitance from victim to GND instead of reducing the capacitance between aggressor and victim. The cable reassignment above actually did both reduce the aggressor-victim capacitance as well as increase the victim-to-GND (and power) capacitance, so it made use of both mechanisms.

Cable length influence

Another question is how the length of the cable affects the crosstalk. By examining the schematic models of the cables, one can realize that all the cable capacitances (C1-C6) are proportional to the length of the cable. As mentioned above, the amplitude of the crosstalk is controlled by capacitive voltage division. All of the capacitances involved in the division, except for the small external capacitances to GND (C7 and C8), grow linearly with the length of the cable, so as long as the cable capacitances dominate over the stray capacitances to GND, the length of the cable should have only a very minor impact on the amplitude of the crosstalk.

Below is an oscilloscope plot of an I2C bus that is run through 2.7 m of cable.

I2C 2k2 diagonal long cable
Crosstalk between I2C signals. 2.7 m of cable, signals on diagonal wires, 2.2 kohm pull-up. Yellow signal is SCL and blue is SDA.

As expected, the amplitude of the crosstalk has remained essentially the same while the edges are less steep due to the added capacitance.

Summary and recommendations

  • I2C is particularly sensitive to capacitive crosstalk since the signals are often not driven by strong drivers, but instead by weak pull-up resistors.
  • Glitches caused by crosstalk can cause an I2C bus to malfunction.
  • Cables often have a large capacitance between at least some of the wires.
  • Reducing the value of the I2C pull-up resistors does not have any significant effect on the crosstalk and is therefore not an effective way of controlling it.
  • I2C signals should be placed in a cable such that they have as little capacitance as possible to aggressor signals, i.e. to signals with fast high-to-low transitions (like other I2C signals).
  • Capacitance between I2C wires and power and GND wires help reduce crosstalk (but at the expense of  higher bus capacitance, and thus slower transitions and reduced maximum speed for a given value of pull-up resistor).
  • In twisted pair cables, the I2C signals should be paired with power or GND wires, not with other I2C signals.
  • In ribbon cables, the I2C signals should only be adjacent to power or GND wires.
  • The length of the cable does normally not affect the amplitude of the crosstalk very much. (As long as the cable is long enough so that the cable capacitance dominates over other bus capacitances.)
  • Longer cables do of course add more capacitance and thus reduce the steepness of the positive edges, which can reduce the maximum speed and/or require reduced pull-up resistances.
  • It is a very good idea to use an oscilloscope to take a look at the I2C signals in any new design to see if they look good or need to be improved.

I hope this article has provided some intuition about – and insight into – I2C crosstalk in cables and what to do to control it.

Problems with Python XBee 2.2.3 package

This blog post describes an issue I am having with the XBee 2.2.3 Python package where the callbacks that are supposed to happen when new packets are received suddenly stop. I managed to find the reason for this as well as an at least temporary solution.

A bit of background

I just tried to add a second node to my small ZigBee network (now consisting of a coordinator and two routers) and ran into a bit of trouble. The new node has a new version of the XBee radio, namely XB24CZ7PIT-004. This has replaced the previous product XB24-ZB from Digi and it has a new firmware which simultaneously supports coordinator/router/end point. So one does not have to upload a firmware specific to the role the radio is playing in the network. Maybe it has other subtle differences as well. I do not know if the new radio version is relevant to the problems I am having, but I thought I’d better mention it.

The problem

I use a callback function to receive the data from the XBee, but the problem is that when I added the new XBee (as a router), I just got one or a few packets back from the coordinator (which is the one I talk to using Python) before callbacks stopped. I discovered that a new error callback feature had been added in 2.2.2, so I made use of this to try to troubleshoot:

 # Callback function that runs when XBee errors are detected
 def xbee_error(e):
     print "XBee error!"
     print e
 
 xbee = ZigBee(ser, callback = receive_data, error_callback = xbee_error)

 

This gave me the following printout before receive_data callbacks ceased:

XBee error!
'Unrecognized response packet with id byte \xa1'

So apparently a “new” kind of packet with frame type A1 has started to show up for some reason unknown to me. This turns out to be a “Route Record Indicator”. I have not looked into why this happens, but since the A1 frame type is not defined in the api_responses dictionary in zigbee.py and there is a new except-clause with a break in the definition of run() in base.py, I think the thread that receives data from the XBee and calls the callback function thereby terminates when such an unrecognized frame id is received.

I do not think this is appropriate behavior in this case. The thread should not die just because an A1 frame ID appears. I solved it quickly and in an ugly manner by commenting out the troublesome break in base.py:

    def run(self):
         """
         run: None -> None
 
         This method overrides threading.Thread.run() and is automatically
         called when an instance is created with threading enabled.
         """
         while True:
             try:
                 self._callback(self.wait_read_frame())
             except ThreadQuitException:
                 # Expected termintation of thread due to self.halt()
                 break
             except Exception as e:
                 # Unexpected thread quit.
                 if self._error_callback:
                     self._error_callback(e)
                 <b>#break # Commented out by Per M 2016-01-06</b>

There are probably better ways to solve this. Maybe the A1 and other possible frame IDs (see the API frame specifications section in the XBee User Guide) should be added to api_responses in zigbee.py.