• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code snippet to alter/change existing code in a module

M

Mr.Karr

Member
Hi

Can anyone please provide a code snippet to alter or change existing code?

To elaborate:
I have an automation in place say, Module1. Now I need to alter a particular line (Ln45, Col4). Please advise
 
While it is possible to write code that alters itself or other code, it's very bad practice. It's much better to use variables in your code where you need something to change. W/o seeing what the code looks like, I'm not sure how much more we can advise.
 
@Luke M : please see the below string

Code: 
'List of Excel Ranges to Copy from
    MyRangeArray = Array(Sheet1.Range("A1:C10"), Sheet4.Range("A1:C10"), _
      Sheet3.Range("A1:C10"), Sheet2.Range("A1"), Sheet5.Range("A1:C10"))

is what I want to change as I tried to alter this with other ways but failed.
I have the same (what follows after MyRangeArray) in cell A1. Do you think we can anyway re-write this ?
 
What part are you wanting to change? Do you need the worksheets to change, or the range address?
 
worksheets and ranges as well.

Can I have the array's pre-entered in particular cells?
Can I have the complete array captured in a single cell ?

I have the sample file attached for you along with the code module. Please see the file.
 

Attachments

  • Book2.xlsm
    18 KB · Views: 5
Uploaded file doesn't show me what changes, it just has the info we already know.

Perhaps we could take a step back...from the overall view, what are you trying to accomplish? You need to build a range...how does this range get definied, in plain speak (not code)?
 
What part are you wanting to change? Do you need the worksheets to change, or the range address?
Click to expand...

Further to Luke's question, it is often not a good idea to use range names in your code, because they don't get updated if someone inserts a new row/column in the workbook. Instead, assign a Named Range to any ranges you want to reference, and then use that Named Range in your code.
 
@Luke M : alright, let me provide more information about the activity I'm trying to achieve.

1. Export multiple ranges to PPT: This automation is utilized by various teams and we do not know the ranges they have to export. So I'm trying to make this portion as dynamic and facilitating users with select option. They go ahead & select range one by one & trigger "export to PPT" at once.

Presently what you see in the module is a static one with fixed array ranges. This approach won't help for the above explained.

2. Good news is, I've an automation developed already to perform the point#1 :). But there is an additional requirement to modify the automation ie to export ranges to an existing PPT. Sad not sure how to modify that to fit the recent.

Hope this helps. Please feel free to ask if you still require additional info.
 
@jeffreyweir : okay, I have now created named ranges to those cells with actual ranges to PPT.

Please see the attached file. Can you help with the code? Let us see if this approach works.
 

Attachments

  • Book2 (1).xlsm
    22.4 KB · Views: 3
Here's one way to do it. Let's user input the XL worksheet name, rather than code name (which can be confusing). Since the PPT array is hardcoded, I made assumption that you always have 5 ranges.
 

Attachments

  • PPT Export.xlsm
    25.3 KB · Views: 6
@Luke M :
I am getting below error when I try to implement the same code in another workbook.

Runtime error 9
"subscript out of range"

Set tb = Worksheets("ExistingExport").ListObjects("tblCopy")
 
You will want to make sure that the table name (and that you have a table!) is the same. Select a cell within the table, and then in one of the Table ribbon menu's on the left, you can see the name. Either change the code to match XL, or XL to match the code.
 
Hello Guys,
I am a beginner in VBA, refring to some of the codes available in internet I tried multiple codes including the one above which gives dynamic cell range to copy range in powerpoint but for some reason only sheet 1 gets copied in powerpoint.No other sheet is getting copied.Same thing is happening with different codes that I tried.

Can somebody help me here!
 
You must log in or register to reply here.
Back
Top