• 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.

macro code updating from another macro (if that makes sense)

R

rszebin

New Member
Hi, I may have a design problem. Also lacking advanced excel/VBA, I may approach the problem in a wrong way, and I am asking for some help in this regard.


What I want:


I want to track multiple teams that each handle several tasks groupped in several categories. This is supposed to be a live document, where team members do minimal work, namely inserting task name and description, and automate everything else. There is a sheet that consolidates all the tasks that are timestamped. I have organized seets for each team, and then arranged the groups of tasks. Due to timestamping formulas, I recorded a few macros that insert rows and format the new rows properly (the tasks are ordered vertically, as well as the groups)

I would like to avoid creating worksheets for each task group, as it can become a too large file, and easy to make a mistake. What I am having difficulties expressing is


once the VBA will insert a new row of cells ( a range, technically, pushing all cells below) the other macros will need updating, because they will reference incorrect cells.


Is it possible to have one macro increment cell number in other macros (same workbook) ? This is the best wording that I can find, and since I am asking all kinds of questions (except the right one) on the internet, I cannot get the right answer.


I have tried "Use relative references" button, it doesn't work for me.

I hope the example attached will explain my problem.


http://www6.zippyshare.com/v/38946167/file.html]testvba.zip


If the new task button is clicked, it will insert a new range, happy clouds, etc. If after clicking the button (inserting the range) the button in the second category will no longer work properly (either work "alone")


I would as much as possible stick to the layout of the sheets and all. Sheet2 (containing task details and so on can, as a solution be moved to horizontal, but I would rather not, after inserting 5 teams with some interesting tasks, one would have to scroll forever, or use hyperlinks ... Arranging on vertical is the most space efficient arrangement I have insofar.


Please advise.


My tanks in advance!
 
Hi, rszebin!

Could you please upload again the file? It appears to have an error while downloading the compressed archive.

Regards!
 
Hi Pablo ,


You can download it from here :


https://www.dropbox.com/s/slwbzjhtp292c28/testvba.xlsm


The zipped file contained only this one workbook.


Narayan
 
Hi, rzsebin!


I should admit that I didn't understand what do you want to achieve, I looked at both worksheets with data and I didn't get it.


Despite of this I checked that in the VBA code you have this line:

-----

Code: 
ActiveCell.FormulaR1C1 = "updated text goes here "

-----


If that's all the problem, why not replacing it with something like:

-----

ActiveCell.FormulaR1C1 = Worksheets("Sheet3").Cells(1, 1).Value

-----

and place in cell A1 of worksheet Sheet3 whatever you need?


Hope it helps.


Regards!
 
Hi, rzsebin!


After remembering things done in Word years ago to eliminate viruses from .doc and .dot files (which involved reading, writing and removing VBA code, i.e., using the same methods that they use to spread), and after digging into old folders, I retrieved and adapted a little piece of code to do the job actually modifying the VBA code. Here's the link:

https://dl.dropboxusercontent.com/u/60558749/macro%20code%20updating%20from%20another%20macro%20%28if%20that%20makes%20sense%29%20-%20testvba%20%28for%20rzsebin%20at%20chandoo.org%29.xlsm


This is the code:

-----

[pre]
Code: 
Option Explicit

Sub UpdateVBACode()
' constants
Const ksModule = "Module1"
Const ksProcedure = "test1"
Const ksSource = "updated text goes here "
Const ksTarget = "This is the updated text... just in case :P"
' declarations
Dim lCOL As Long, lCODL As Long, lPBL As Long, lPCOL As Long, lPSL As Long
Dim bF As Boolean, lFrom As Long, lTo As Long, sLine As String
Dim I As Long, J As Long, K As Long
' start
' process
With Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule
lCOL = .CountOfLines
lCODL = .CountOfDeclarationLines
lPBL = .ProcBodyLine(ksProcedure, vbext_pk_Proc)
lPCOL = .ProcCountLines(ksProcedure, vbext_pk_Proc)
lPSL = .ProcStartLine(ksProcedure, vbext_pk_Proc)
lFrom = lPBL
lTo = lPBL + lPCOL - 1
bF = .Find(ksSource, lFrom, 1, lTo, -1, True, True)
Debug.Print lCOL; lCODL, lPBL; lPCOL; lPSL, bF, lFrom; lTo
If bF Then
K = 0
For I = lFrom To lTo
Debug.Print I, "Before", "|"; .Lines(I, 1); "|"
K = K + 1
J = I
sLine = Replace(.Lines(J, 1), ksSource, ksTarget)
.ReplaceLine J, sLine
Debug.Print J, "After", "|"; .Lines(J, 1); "|"
Next I
MsgBox K & " changes made in procedure " & ksProcedure & _
" of module " & ksModule, _
vbApplicationModal + vbOKOnly + vbInformation, "Summary"
End If
End With
' end
Beep
End Sub
[/pre]
-----


Regards!


PS: As a matter of fact I'm not the creator of that sort of antivirus code for Word files (from which this code derives), I just happened to step into a virus code because of a programming error if file was in a network, and then borrowed the idea and develop an enhanced version for the antivirus code using the same replication mechanism copied from the virus code. That's the learning process, isn't it? I'd have like to quote the author but he seemed to prefer remaining in the anonymity.

With that home made AV I began a collection of script Office malware code, and when I get in touch with the damned famous Melissa code I tried changing the string of the virus signature which was coded in a constant string plain-text (I replaced one character), and then tested both files with Norton NAV and McAfee 2001 or 2002 versions, and they didn't detect the altered file which was just as dangerous as the original. And at least for almost a year!

Oh!... those were the days...
 
Thank you for all the replies, I find it really nice when random humans help other random humans, just because.


Now, further deeper into my proposal.


@ SirJB7, i tried your updating code, just to see what it does, and i get "undefined variable: vbext_pk_Proc " just FYI.


@ everyone: I have the feeling you did not actually insert tasks via the buttons (on Sheet2); Sheet1 is supposed to have (almost) no input actions - manually, just referencing information from other cells, an "Overview Sheet" if you like.


I forgot to mention in my previous post that for the timestamps to work, you need Iterations enabled ( http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/ )


If you push the top button "insert new task" everything should go just fine. if you push the Insert new task button in the Geek team, the more tasks you inserted at the above team, the more messed up it will be.


To me, the reason is quite simple: in my VBA I am using absolute references to where each macro should insert a range. But if a different macro will insert a range, the macros dealing with ranges in rows below will fail to do their job, as the row numbers are no longer correct.


I would like upon inserting a range to update all other macros to insert their defined ranges at the correct row numbers. I hope it will make more sense now.


The example workbook I uploaded is a small example. In the situation I am in, I have more teams, categories, and a lot of tasks.
 
Hi ,


Both Sheet2 and Sheet1 have some data ; I need to now memorise everything on both sheets before I press the New Task button so that I can see what pressing the button does , and where it has gone wrong ; it would be nice if you could start with a blank form so that pressing the New Task introduces the first task ; pressing it a second time introduces a second task. With these two presses , it will be clear what the macro does , and pressing it more times will either give a consistent result each time , or will start giving inconsistent results at some stage , or will give totally erratic results. What ever be the result , I can figure out for myself what my actions did , and whether they did so correctly. Is this possible ? Can you clean up both sheets as far as possible , and then upload the file ?


If the above is not possible , please explain what it is that you are looking to do ; uploading a file with input data , and possible output data , and a clear linkage between the input and the output will make it easy for others to develop the macro you want , instead of debugging a pre-written macro.


Narayan
 
You could use loops instead of selecting specific ranges.


For example, loop down until a cell contains a specific text (i.e. "Start Date") then insert a new row below.


That way you don't need to worry about changing the vba code.


You can loop until more than one criteria is met, i.e, find the category, then the person, then start date
 
@ DaveTurton: thanks for the suggestion, I have little experience with loops but I understand what a loop basically is and I think it's the right approach, as I am starting with certain cell positions, but after several inserts, there is no real way of knowing where the ranges should be inserted. I will look up the loops and give that a shot.


@ Narayank: I tried to reproduce as accurately my situation. I would say you dont need to remember anything on the first sheet, as everything there should be inserted and updated automatically; to better see the results, change the values of cells D9, D21, and it will insert a "marker"

After that click any of the insert buttons (F7, F20) and you will notice the function of the macros. The point would be, after you have say, 5 task in a list,


task1

task2

task3

task4

task5

insert your text here


will make it easy for the user to figure out what cell to enter his text in.

that is why the macro will fill up the D* cells with some marker text.


I am working on a improvement to a task tracking document. And since my recent romantic involvement with Excel, I am trying to do a better job. The document is intended to be used by team leaders to input tasks in Sheets 2,3,4,n and supervisors to observe updates in Sheet1, hence all the references, etc. I tried to make an example file, where I would show just the topic I have difficulties with. As Dave suggested, it looks to me in this moment that loops are a way to do this, it's just that I lack a good understanding of loops in VBA. I am reading stuff about them as we speak.
 
Hi Rszebin


as a very basic example you could use the below. It will basically start in C1, then loop down until it finds "dishwasher" then loop down until it finds "start date". Then select the cell below and insert a row above.


It is not the best method, but I thought it might be the easiest for you to read, understand and adapt

[pre]
Code: 
Cells(1, 3).Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = "dishwashing"
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = "start date"
ActiveCell.Offset(1, 0).Select
Rows(ActiveCell.Row).Insert shift:=xlDown[/pre]

Please note however that you have a space after your "start date" text, so either remove the space or change the code to look for "start date "


You may also wish to put Option Compare Text
at the top of your module as this will remove case sensitivity from the searches
 
@ Dave - I want to keep the initial timestamp, and the last timestamp. Example:

Team A is working on a task to migrate 12K users from mailsystem1 to mailsystem2. There will be some temporary db set up, servers, the works. This task will take 2 weeks. There are weekly updates to the task. Initially the task is entered in the D column. it will have a timestamp. After 1 week there will be some updates to the task (the servers are installed successfully, the databases are ready for migration) I want to keep the initial timestamp, and want to record when the task was updated. Until now, the only way I found is in the post I mentioned before (http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/#comment-117514)

I will later use the timestamps to make conditional formatting to indicate which task exceeds its time frame, among other manipulation.


Quick question (skipping ahead on 'loops'):

I have several macros that are supposed to insert ranges at relative cells (if macro1 will insert 2 rows, the pre-recorded macro2 will have to somehow update its range insertion point by 2 rows)
 
Assign a public variable at the top of your module


i.e.


Code: 
Public RowCount As Integer


You would then use an IF statment, which would basically say if you are inserting 2 rows then RowCount = 2. you can then refer to RowCount in your second macro
 
You can record any date and time with vba so you could for example set 01/05/13 09:00:00 as the initial time stamp and then Now as the updated


e.g

[pre]
Code: 
ActiveCell.Value = "01/05/13 09:00:00"
ActiveCell.Offset(0, 4).Value = Now

you could even declare a constant at the top of your module 


[code]Const StartStamp = "01/05/13 09:00:00"


then the above would read

ActiveCell.Value = StartStamp
ActiveCell.Offset(0, 4).Value = Now[/code][/pre]
 
Thank you Dave, but I don't understand technically what you are saying, as I am just at the tip of the iceberg called VBA. It sounds to me like what I want to achieve, but I need to study a bit more to know what I need to do. I can't say, therefore, that it has been solved, but I have the feeling it is :)


Thanks again for all the help.


Raymond
 
Hi Raymond


Here is a VBA tutorial I obtained from the internet some time ago (can't remember the source I'm afraid).


https://docs.google.com/file/d/0ByiZqUlQMjxhOGZPYVZKRDZDb0k/edit?usp=sharing


I have provided this to my staff members and it has helped their knowledge grow and they can now read my codes and debug errors etc.


Have a good read through, I think it will help you a lot
 
Thanks for the document. With the weekend coming up, I will have time to open a beer and go through it. I am joining the class here on chandoo, but that is a project for next month, as I want to have the time it requires.

I don't like taking shortcuts, like I just did with this post, but I figured I can do something in between other tasks at hand.


I honestly (and greatly) appreciate all help. Have a good week-end y'all!
 
Hi, rzsebin!


Did you get that error (of the constant vbext_pk_Proc) with my uploaded file or after copying the code to another one?


Checked the references for each workbook? Alt-F11, Tools, References. For Excel 2010 versions I have by default:

- Visual Basic For Applications

- Microsoft Excel 14.0 Object Library

- OLE Automation

- Microsoft Office 14.0 Object Library

For this workbook in particular these too:

- Microsoft Visual Basic for Applications Extensibility 5.3

- Microsoft Forms 2.0 Object Library


You need the 1st of those 2 last, Extensibility 5.3. Without running the code you can check it with Alt-D + L (Compile VBA project from the Debug menu entry).


Regards!
 
@ SirJB7 I was able to find "Microsoft Visual Basic for Applications Extensibility 5.3" - was unchecked; but cannot find "Microsoft Forms 2.0 Object Library". Im running Office Professional Plus 2010.


- Visual Basic For Applications

- Microsoft Excel 14.0 Object Library

- OLE Automation

- Microsoft Office 14.0 Object Library

were checked.


The file you uploaded for me works just fine. Thank you anyway :)
 
Hi, rzsebin!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/macro%20code%20updating%20from%20another%20macro%20%28if%20that%20makes%20sense%29%20%28for%20rszebin%20at%20chandoo.org%29.png


There you'll find that the reference for "Microsoft Forms 2.0 Object Library" points to FM20.DLL, ant this file is older that tax collectors! I'm using too Office 2010 Pro Plus, but under Win 7 x64, so if you're running on 32 bits x86 surely you'll found that file in a different folder than WindowsSysWOW64, more than probable in WindowsSystem32. If you happen to find it, you can add it from the Browse button of that screen.

Not thinking loudly, if you don't find it, try reinstalling (not need to uninstall) Office with all the options enable, who knows, maybe then it appears.


Regards!
 
I really appreciate all your help, Bond, but I must say, this is an overkill for me :) even if I enable Forms 2.0, I'm afraid it won do me any good as I can't understand it. yet.

If anyone reads this and has the same issue, yes, I am running Win7 32bit, not by choice.


I shall return when I am a bit more skilled in VBA, which, I can't allocate time right now.


Cheers!
 
Hi, rzsebin!

If I were you I'd try installing all Office features, could you? Otherwise if you're willing to post an email address here I'll get back to you (which I could then remove it for privacy concerns).

Regards!
 
You must log in or register to reply here.
Back
Top