VBA String Editor For Excel 2007

**GWilliams** · 05-01-2009, 01:40 PM

I Haven't gotten any replies in the Excel 2007 dedicated area in 3 weeks and this doesn't necessarily apply to only 2007. The original post is here.

I'm looking for something like this that will work in excel '07.

**shg** · 05-01-2009, 02:40 PM

Your explanation in that post is not very complete, and there's a single example. Perhaps if you expanded it, described where the input strings would be found (in a text file?), and what the output should be.

Where do the variables come from? How many functions are there? How many constructs -- #IFDEF is one, what are the others?

**GWilliams** · 05-01-2009, 03:26 PM

Originally Posted by GWilliams

I am converting a proprietary program that belongs to our company to excel. Most of the work is done but a portion of it requires me to output a large number of concatenated strings with many double quotation marks and variables. What i am looking for is an application or plug-in of some sort that does that same as this but for excel 2007.

example of code line from previous program:

@IFDEF EmitCSV @EMITTOFILE IO_#PlcName.csv "#Alarm_Tag\State_Addr1","#Item_Addr1",Word,1,RO,100,,,,,,,,,,"",

@ means it is a function
# means it is a variable to be expanded

VBA equivalent:

Please Login or Register  to view this content.

the VBA Function FormatSTR simply adds preceding 0
if in the above example BP_ADDR were to equal "103" then Item_Addr1 would equal "00103"

EmitToFile(File Name, String to append)

I can do the conversion to a VBA string by hand but it is very time consuming considering i have over 1000 different lines like this to convert.

Any help would be greatly appreciated.

Additions as for SHG's request:

IfDef is asking if this variable is Defined/Dimensioned.
I converted this Variable to a Boolean so i now test if if is True Or False.

EmitToFile in both cases outputs to a flat file (.txt) format.
We just change the extension. In this example we are outputing as a .csv to be imported by a program called WonderWare.

In the original Program the delimiter for functions is a space. It also does not require parentheses or any other formatting characters besides the # and @ symbols. It is a somewhat crude and simple language but it was written by one person to get a job done. Odd fact: You can't multiply in that language, you have to loop an addition formula.

The main Question is not about if the code i wrote will work, it does.
The question is, does anyone know of a plugin or program that can do what the tool described in this website does. If it exists anywhere other than the Excel 2000 Development tools. And if it does exist where someone could get such a thing.

When looking at the example I gave, you can see how much of a mess it is to do the same thing in VBA compared to the original program. it is very Time consuming to write out all the Chr's and " and & to get VBA to output the same line into a flat file as the previous program. If i had the tool described in the website i could take the line:

"#Alarm_Tag\State_Addr1","#Item_Addr1",Word,1,RO,100,,,,,,,,,,"",

put it in the string editor mark the # symboled strings as variables and remove the #'s and i would get the complex string i would have had to construct by simply clicking a button.

**shg** · 05-01-2009, 03:37 PM

Where do you get BP_ADDR in the output string? It doesn't appear in the source.

**GWilliams** · 05-01-2009, 03:52 PM

it from miles up the Module in a completely different Sub. It is a public variable. To understand the whole thing of what is happening you would kinda need the entire Template but it is huge and kinda Company property so i can't just give it out. The BP_ADDR is a long representing a position in Memory for a PLC (Programmable Logic Controller). The module this is in consists of 4,500 comments (comments are the original Code from the original program that i had copied to assist in rewriting it and for explanation of what my code does) and 2,500 lines of code. The entire project consists of 6,200 lines of code

.

EDIT: i just noticed exactly what you were talking about the examples i gave don't match...

Please Login or Register  to view this content.

This is what it looks like now i think i found the solution have been working on it a while. The function emittoCSV looks like this:

Please Login or Register  to view this content.

So it is basically solved for what the example i gave but there are a ton more todo for a different system set up and the ones that need to to be done are even more complex here is an example:

'@IFDEF EmitCSV @EMITTOFILE WW#PlcName.csv :IOInt,Group,Comment,Logged,EventLogged,EventLoggingPriority,RetentiveValue,RetentiveAlarmParameters,AlarmValueDeadband,AlarmDevDeadband,EngUnits,InitialValue,MinEU,MaxEU,Deadband,LogDeadband,LoLoAlarmState,LoLoAlarmValue,LoLoAlarmPri,LoAlarmState,LoAlarmValue,LoAlarmPri,HiAlarmState,HiAlarmValue,HiAlarmPri,HiHiAlarmState,HiHiAlarmValue,HiHiAlarmPri,MinorDevAlarmState,MinorDevAlarmValue,MinorDevAlarmPri,MajorDevAlarmState,MajorDevAlarmValue,MajorDevAlarmPri,DevTarget,ROCAlarmState,ROCAlarmValue,ROCAlarmPri,ROCTimeBase,MinRaw,MaxRaw,Conversion,AccessName,ItemUseTagname,ItemName,ReadOnly,AlarmComment,AlarmAckModel,LoLoAlarmDisable,LoAlarmDisable,HiAlarmDisable,HiHiAlarmDisable,MinDevAlarmDisable,MajDevAlarmDisable,RocAlarmDisable,LoLoAlarmInhibitor,LoAlarmInhibitor,HiAlarmInhibitor,HiHiAlarmInhibitor,MinDevAlarmInhibitor,MajDevAlarmInhibitor,RocAlarmInhibitor,SymbolicName

that is all one line. and i am not sure what is what in there i gotta talk to the guy that wrote it to figure out what is going on in that one.