charts - vba excel 2007 referencing data? -
i inheriting vba code other people. the.......some other code make chart of existing data in excel. dont source datasets referenced. data x-axis? data y-axis??..........
........some other code............
'****************** '****charts******** '*******************
windows(nm_driver).activate activesheet.range("a19") = "" 'intialize cell empty '*******copies previous sheet , makes new sheet******** windows(nm_out).activate sheets(2).select sheets(2).copy after:=sheets(worksheets.count) j = j + 1 sheets(j).select debug.print "j=" & j windows(nm_driver).activate out_sht_nm = activesheet.range("a8") & " plots" debug.print "out_sht_nm" & out_sht_nm activesheet.range("a10").select selection.copy windows(nm_out).activate sheets(j).select activesheet.range("h1").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false '****paste heading in plots tab***** sheets(j).name = out_sht_nm '**** assign sheet's name****** debug.print "out_sht_nm=" & out_sht_nm '*********copies actual data seq num********* windows(nm_inp2).activate sheets(i).select activesheet.range("b3:b23").select 'i use usedrange here selection.copy windows(nm_out).activate sheets(j).select activesheet.range("b3").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false windows(nm_inp2).activate sheets(i).select activesheet.range("c3").select col_len = selection.end(xltoright).column debug.print "col_len=" & col_len p = 3 '*******this loop if checking till have columns ending *_xb x = col_len 1 step -1 debug.print "x=" & x windows(nm_inp2).activate sheets(i).select activesheet.cells(3, x).select selection.copy windows(nm_driver).activate activesheet.range("a24").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false if activesheet.range("a25") = "_xb" start = x debug.print "start=" & start end if next x '*******this loop copy remaining columns input file********* k = 3 start - 1 'these columns not end _xb debug.print "k=" & k windows(nm_inp2).activate activesheet.cells(3, k).select selection.copy windows(nm_driver).activate activesheet.range("a19").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false flag = activesheet.range("a21").text '***it checks if column not end _sl_? if flag = "_sl_" 'do nothing else windows(nm_inp2).activate sheets(i).select activesheet.cells(3, k).select activesheet.range(selection, selection.end(xldown)).select selection.copy windows(nm_out).activate activesheet.cells(3, p).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false p = p + 1 '*******variable (p) still incrementing need append remaing columns after *_xb columns******** end if debug.print "flag=" & flag next k '****this loop pasting columns ending *_xb , creating charts***** k = start col_len windows(nm_inp2).activate activesheet.cells(3, k).select selection.copy windows(nm_driver).activate activesheet.range("a19").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false flag = activesheet.range("a21").text '***it checks if column not end _sl_? if flag = "_sl_" else chart_title = activesheet.range("a14").text '**copies column ends *_xb spline consist input file*** '***variable (k) iterating columns in spline consist input file****** windows(nm_inp2).activate sheets(i).select activesheet.cells(3, k).select activesheet.range(selection, selection.end(xldown)).select selection.copy '*****pastes in output file , creates chart******* 'variable (p) iterating columns in output file****** windows(nm_out).activate activesheet.cells(3, p).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false p = p + 1 end if next k debug.print "k=" & k debug.print "p=" & p debug.print "chart_title=" & chart_title '*********** creating charts , labelling x-axis accordingly**************** start_2 = ((p - 3) / 2) + 3 z = 105 m = 0 n = 0 debug.print "start_2=" & start_2 b = 3 start_2 - 1 windows(nm_out).activate sheets(j).select activesheet.cells(3, b).select selection.copy windows(nm_driver).activate activesheet.range("a19").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false chart_title = activesheet.range("a14").text windows(nm_out).activate sheets(j).select text1 = activesheet.cells(3, b).text alp_in = 0 c = start_2 p - 1 text2 = activesheet.cells(3, c).text if ucase(left(text2, len(text2) - 3)) = ucase(text1) alp_in = c end if next c debug.print "alp_in=" & alp_in activesheet.cells(3, b).select activesheet.range(selection, selection.end(xldown)).select selection.copy activesheet.cells(3, z).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false activesheet.cells(3, alp_in).select activesheet.range(selection, selection.end(xldown)).select selection.copy activesheet.cells(3, z + 1).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false activesheet.cells(3, z) = "" activesheet.cells(3, z + 1) = chart_title activesheet.cells(3, z + 1).select row_end = selection.end(xldown).row activesheet.range(cells(3, z), cells(row_end, z + 1)).select debug.print "row_end=" & row_end call border_design activesheet.shapes.addchart.select activechart.charttype = xlline activechart.parent.name = "mychart" & z activechart.axes(xlcategory).select selection.ticklabelposition = xllow activechart.axes(xlcategory).ticklabelspacing = 2 activesheet.chartobjects("mychart" & z) .height = 260 ' resize .width = 290 ' resize .left = m * 300 .top = n * 270 + 600 end '**** variable (m) defines left alignment of chart , variable (n) defines teh top alignment of chart*** '**these variables used because requirement have 3 charts in row**** activechart.legend.select selection.delete activechart.axes(xlvalue).select activechart.setelement (msoelementprimaryvalueaxistitlevertical) activechart.axes(xlvalue, xlprimary).axistitle.text = "log odds" activechart.axes(xlvalue).ticklabels.numberformat = "#,##0.00" activechart.axes(xlvalue).crossesat = -100 'activechart.charttitle.text = chart_title activechart.charttitle.font.size = 10 activechart.charttitle.font.bold = true m = m + 1 if m = 3 m = 0 n = n + 1 end if z = z + 2 'm , n control position of chart next b '********now have create charts class variables coming raw_data 'input file**** windows(nm_driver).activate activesheet.range("aa1").select activesheet.range(selection, selection.end(xldown)) = "" activesheet.range("ab1") = "" '*****copies spline variables in output file driver****** '***basically check if variable coming raw_data input file part of spline variable or not*** windows(nm_out).activate sheets(j).select activesheet.range("c3").select activesheet.range(selection, selection.end(xltoright)).select selection.copy windows(nm_driver).activate activesheet.range("aa1").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=true 'transpose=true here converts row column windows(nm_inp1).activate sheets(i).select activesheet.range("a3").select tot_row = selection.end(xldown).row '****gives total number of rows in raw_data input file***** v = 53 '******variable (v) defines column number class variables pasted in m18 plots sheet**** '****this loop checks every variable of raw_data input file (only column a) list of spline variables , see if 'it spline variable or class variable. if comes out spline variable leaves , if comes out 'be class variable copies in m18 plots sheet , creates graph out of that********* g = 3 tot_row windows(nm_inp1).activate text1 = activesheet.cells(g, 1).text end1 = g windows(nm_driver).activate activesheet.range("ab1") = text1 sum1 = activesheet.range("ac26") if sum1 = 1 '*****checking if variable spline or class; if spline sum1 = 1 else sum1 =0 *** else windows(nm_inp1).activate '*****this loop checks number of rows single variable has in raw_data input file***** h = g + 1 tot_row text11 = activesheet.cells(h, 1).text if text11 = text1 end1 = h '****end1 variable gives row number has last occurance of variable raw_data input file***** end if next h heading1 = activesheet.cells(g, 1).text '****the variable name*** windows(nm_driver).activate activesheet.range("a19") = heading1 chart_title = activesheet.range("a14").text '****chart title combination of sheet name , variable name*** windows(nm_out).activate sheets(j).select activesheet.cells(3, v + 1) = chart_title '****pastes chart_title in m18 plots sheet activesheet.cells(3, v) = "bin" '****pastes text "bin" in m18 plots sheet windows(nm_inp1).activate activesheet.range(cells(g, 3), cells(end1, 4)).select selection.copy windows(nm_out).activate sheets(j).select activesheet.cells(4, v).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false '****selects full table , gives border**** activesheet.range(cells(3, v), cells(3, v + 1)).select activesheet.range(selection, selection.end(xldown)).select selection .wraptext = false 'call border_design end '************************************ '************************************ '**********create chart*************** '************************************ '************************************* activesheet.shapes.addchart.select activechart.charttype = xlline activechart.parent.name = "mychart" & v activechart.axes(xlcategory).select selection.ticklabelposition = xllow activechart.axes(xlcategory).ticklabelspacingisauto = true
'use parent property here activesheet.chartobjects("mychart" & v) .height = 260 ' resize .width = 290 ' resize .left = m * 300 .top = n * 270 + 600 end activechart.legend.select selection.delete activechart.axes(xlvalue).select activechart.setelement (msoelementprimaryvalueaxistitlevertical) activechart.axes(xlvalue, xlprimary).axistitle.text = "log odds" activechart.axes(xlvalue).ticklabels.numberformat = "#,##0.00" activechart.axes(xlvalue).crossesat = -100 activechart.charttitle.text = chart_title activechart.charttitle.font.size = 10 activechart.charttitle.font.bold = true m = m + 1 if m = 3 m = 0 n = n + 1 end if v = v + 2 g = end1 end if next g windows(nm_out).activate activesheet.range("b3").select activesheet.range(selection, selection.end(xldown).end(xltoright)).select call border_design activesheet.range("b3").select activesheet.range(selection, selection.end(xltoright)).select selection.font.bold = true activesheet.range("ba3").select activesheet.range(selection, selection.end(xltoright)).select selection.font.bold = true activesheet.range("a1").select next windows(nm_out).activate application.displayalerts = false sheets(1).delete sheets(1).delete application.displayalerts = true sheets(1).select activeworkbook.save activeworkbook.close windows(nm_inp1).activate activeworkbook.close windows(nm_inp2).activate activeworkbook.close windows(nm_driver).activate activesheet.range("a1").select msgbox "completed" ans2 = msgbox("do want open output file created ??", vbyesno) if ans2 = vbyes workbooks.open filename:=mv_file_sv_nm end if end sub
this part of code selection used create chart
activesheet.range(cells(3, v), cells(3, v + 1)).select activesheet.range(selection, selection.end(xldown)).select now need find activesheet. backing few lines find this:
sheets(j).select what sheet "j"? j gets defined line near top of posted code:
j = j + 1 what tells me (as best can guess based on have posted here) code looping through sheets , creating charts , doing other formatting.
as in sheet code pulling data, coming variable v. have make guesses (1. because can't see sheet structure, , 2. because there code through here). v set 53, , added in various loops in code.
that best i'm willing given how broad question is. isn't best forum general "what mean" questions.
i'll repeat bit in comment looking @ chart source data (in excel window) may decipher going on.
Comments
Post a Comment