The Blue Square Bet Premier side were trailing before Barnes-Homer fired home at the far post in the 85th minute, and he powered a penalty past Joe Lewis three minutes later to spark scenes of wild celebration at Moss Rose.
Cardiff full debutant Nathaniel Jarvis looked to have given the much-changed Bluebirds victory when he scrambled the ball home in the 57th minute.
And the visitors, who finished the match with seven teenagers on the pitch, had chances to kill off their hosts before Barnes-Homer took his goals haul to 20 for the season and send the underdogs through at the expense of the npower Championship leaders.
Cardiff manager Malky Mackay made a full 11 changes to the side that beat Millwall, and Jarvis, having already played against Macclesfield this season for Forest Green, had the ball in the net early on. But play was called back for a foul by strike partner Etien Velikonja, who was given a rare chance to impress.
In an even opening to the match Macclesfield had sight of goal themselves with Charlie Henry warming Joe Lewis' palms from 20 yards before Keiran Murtagh shot wide.
As the game reached the half-hour mark Cardiff defender Ben Nugent, who celebrated signing a new deal with the club on Friday, had a header saved by Lance Cronin after Adedeji Oshilaja's cross found the centre-back.
Three minutes later Henry missed a glorious chance to open the scoring when he found himself unmarked but failed to test Lewis, making his second start of the season, from six yards.
Macclesfield were getting to grips with their opponents in the first meeting between the sides since 2005 and felt they should have had a penalty just before half-time. Midfielder Sam Wedgbury believed he was fouled when he went shoulder to shoulder with Oshilaja.
Macclesfield, who had already knocked out League One side Swindon in this season's competition, started brightly in the second half, Amari Morgan-Smith having a shot blocked by Nugent two minutes after the break.
Against the run of play the Bluebirds took the lead with a fine move finished off by Jarvis from close range after great work from Declan John. The Welshman, making his full debut, pulled a pass back into the path of Jarvis at the end of a fine run down the left.
Stephen McPhail, captaining Cardiff in only his second appearance of the season, went close to making it 2-0 a minute later but Cronin pulled off a fine stop to deny the former Leeds midfielder's 20-yard effort.
Just when it looked like Cardiff were easing through, Jack Mackreth crossed from the right wing and Barnes-Homer met it with a left-footed finish from six yards out with five minutes left.
The match was decided in the 88th minute in controversial fashion when referee Andrew Madley pointed to the spot for a foul by Jarvis on Barnes-Homer.
The club's top scorer composed himself before knocking Cardiff out of the competition.