Avatar of Vipin Kumar
Vipin KumarFlag for India asked on

VBA to create lines based on template

I have a MAC and I am trying to write a VBA excel code to create lines based on a template, but i am not able to get the logic how i can achieve the result.


The template is as below. All the ones starting with $ are variables which will have different values on different sheets.

$ACL $LOCLNW $LOCLNW_IPMI01 
$ACL $LOCLNW $LOCLNW_HEV_MGMT01 
$ACL $LOCLNW $LOCLNW_HEN_MGMT01 
$ACL $LOCLNW $LOCLNW_APP_MGMT01 
$ACL $LOCLNW $LOCLNW_STR_MGMT01 
$ACL $LOCLNW $LOCLNW_SRV 
$ACL $LOCLNW $NW_NET 
$ACL $LOCLNW $LOCLNW_CS01 
$ACL $LOCLNW $LOCLNW_CS01_CS_VP
$ACL $LOCLNW $LOCLNW_APP_MGMT_COMSRVRS 
$ACL $LOCLNW $LOCLNW_NAT01 
$ACL $LOCLNW_IPMI01 $LOCLNW_APP_MGMT01 


The values for the variables from one sheet is as below. Different sheets will have different values, but variable names remain same.

$ACL:2500
$LOCLNW:1.1.32.0 0.0.31.255
$LOCLNW_IPMI01:1.1.32.0 0.0.1.255,1.1.38.0 0.0.1.255
$LOCLNW_HV_MGMT01:1.1.34.0 0.0.1.255
$LOCLNW_HANA_MGMT01:1.1.40.0 0.0.1.255
$LOCLNW_APP_MGMT01:1.1.52.0 0.0.3.255,1.1.56.0 0.0.7.255
$LOCLNW_STOR_MGMT01:1.1.46.0 0.0.1.255
$LOCLNW_SERVICES:1.2.128.0 0.0.7.255
$NW_NETSEC:1.1.117.0 0.0.0.255
$LOCLNW_CIS01:1.2.132.0 0.0.1.255
$LOCLNW_CIS01_CIS_VIPS:1.2.133.0 0.0.0.255
$LOCLNW_APP_MGMT_COMSERVERS:1.1.55.128 0.0.0.127,1.1.59.128 0.0.0.127,1.1.63.128 0.0.0.127
$LOCLNW_NAT01:1.2.130.0 0.0.0.255


I want to create a logic so that it create lines as below for ex:

$ACL $LOCLNW $LOCLNW_IPMI01 --> this should come as below, it creates two lines because the variables $LOCLNW_IPMI01 has two values in it separated by comma.

2500 1.1.32.0 0.0.31.255 1.1.32.0 0.0.1.255

2500 1.1.32.0 0.0.31.255 1.1.38.0 0.0.1.255

VBAMicrosoft Excel

Avatar of undefined
Last Comment
aikimark

8/22/2022 - Mon
aikimark

Substitution is relatively straight forward, using the VBA Replace() function.

Since you're using a MAC, you'll have to use a 2D array to store the replacement name/value pairs.

If you have a lot of name/value pairs, you might have to do a performance tweak.  But hold off on that until you get it working and do some performance testing.
ASKER
Vipin Kumar

Hi aiki,

Yeah i am aware of substitution using the replace function but there are variables which have multiple values stored in them and for that template line it should generate that many lines as many values it has in the variable. If you can show up the logic with basic code how can i achieve that, i can modify it accordingly as per requirement.

For ex:
$ACL $LOCLNW_IPMI01 $LOCLNW_APP_MGMT01 --> this should be end up as below as both $LOCLNW_IPMI01 and $LOCLNW_APP_MGMT01have multiple values in them.
2500 1.1.32.0 0.0.1.255 1.1.52.0 0.0.3.255
2500 1.1.38.0 0.0.1.255 1.1.52.0 0.0.3.255
2500 1.1.32.0 0.0.1.255 1.1.56.0 0.0.7.255
2500 1.1.38.0 0.0.1.255 1.1.56.0 0.0.7.25

i hope you get the point
aikimark

Will the template lines always have three $ values?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Vipin Kumar

not necessarily they might have one, two three or none
aikimark

none
Because there are no matches in the substitution table or because there are no $ values in the template string?
ASKER
Vipin Kumar

here i have only given a sample of few lines... the template list is long... so there are lines in the template which will not have any $ variables... also there are lines which can have one two or three $ variables

for ex:
! this is sample
$ACL
# this is another $ LOCLNW sample
$ ACL $LOCLNW_IPMI01
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aikimark

Please post examples of none and examples where there are non $ values in the template
ASKER
Vipin Kumar

i have provided the same in my previous comment

the values of $ variables will never be empty it will be just in the template line there can be lines with $variables or none
aikimark

there are lines in the template which will not have any $ variables
Which of the lines that you've posted do not contain any $ variables?

Which of the lines that you've posted contain text that is not a $ variable?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Vipin Kumar

for ex:
! this is sample --> this doesnt contain any $ variable
$ACL
# this is another $ LOCLNW sample
$ ACL $LOCLNW_IPMI01
aikimark

Which of the lines that you've posted contain text that is not a $ variable?
Let me clarify...Do you have any lines that contain one, or more, $ variables and some non-substitution text?

Example:
$ACL YADA YADA BR549

Open in new window

ASKER
Vipin Kumar

yes
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aikimark

I'm encountering an ASCII 160 at the end of each line of the template.  Is that an artifact of our copy/paste operations or are there really 160 characters in your template data?
ASKER
Vipin Kumar

there is no ascii 160 character
ASKER CERTIFIED SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aikimark

@Vipin

Are you still testing my code?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck