mc vba all answer

8/10/2019 Mc Vba All Answer

http://slidepdf.com/reader/full/mc-vba-all-answer 1/24

FIBONACCI SERIES

Sub FIBSub() ' Finds Nth Fibonacci number N = 1,2,3,... ' From the series Fib(1)=0, Fib(2)=1, Fib(3)= 1,.... ' N is the input contained in the cell named ValueofN ' Function FIB is called to find the value

' The result is printed in the cell named ValueofFIBN Dim M As Range, FIBN As Range

Set M = Range("ValueofN") Set FIBN = Range("ValueofFibN") 'FIBN.Value = FIB(M.Value) FIBN.Value = FIB(M.Value)

End Sub

Function FIB(N As Integer) As Long ' To find Nth Fibonacci Number, N = 1,2,3,4,5,... ' The series is: 0,1,1,2,3,5,8,... ' For example: FIB(1) is 0 ' FIB(2) is 1 ' FIB(3) is 1 ' FIB (4) Is 2 ' The rule is FIB(N) = FIB(N-1)+ FIB(N-2) for n = 3,4,5,...

Dim F As Long, FP As Long, j As Integer

F = 1

FP = 0

If N = 1 Then FIB = FP

Else If N = 2 Then FIB = F

Else

For j = 3 To N F = F + FP FP = F - FP

Next j FIB = F

End If End If

End Function

Function FIBR(N As Integer) As Long



'Recursive computation ' To find Nth Fibonacci Number, N = 1,2,3,4,5,... ' The series is: 0,1,1,2,3,5,8,... ' For example: FIB(1) is 0 ' FIB(2) is 1 ' FIB(3) is 1 ' FIB (4) Is 2

' The rule is FIB(N) = FIB(N-1)+ FIB(N-2) for n = 3,4,5,... ' This function uses recursion

If N = 1 Then FIBR = 0

Else If N = 2 Then FIBR = 1

Else FIBR = FIBR(N - 2) + FIBR(N - 1)

End If End If

End Function Function FIBW(N As Integer) As Long ' Same as FIB, but uses DO WHILE... LOOP ' To find Nth Fibonacci Number, N = 1,2,3,4,5,... ' The series is: 0,1,1,2,3,5,8,... ' For example: FIB(1) is 0 ' FIB(2) is 1 ' FIB(3) is 1

' FIB (4) Is 2 ' The rule is FIB(N) = FIB(N-1)+ FIB(N-2) for n = 3,4,5,...


F = 1 FP = 0

If N = 1 Then FIBW = FP

Else If N = 2 Then FIBW = F

Else j = 3 Do While j <= N F = F + FP FP = F - FP j = j + 1 Loop FIBW = F

End If End If



End Function

Function FIBU(N As Integer) As Long ' Same as FIB, but uses Do Until... LOOP

' To find Nth Fibonacci Number, N = 1,2,3,4,5,... ' The series is: 0,1,1,2,3,5,8,...

' For example: FIB(1) is 0 ' FIB(2) is 1 ' FIB(3) is 1 ' FIB (4) Is 2 ' The rule is FIB(N) = FIB(N-1)+ FIB(N-2) for n = 3,4,5,...


F = 1 FP = 0

If N = 1 Then FIBU = FP

Else If N = 2 Then FIBU = F

Else j = 3 Do Until j > N F = F + FP FP = F - FP j = j + 1

Loop FIBU = F

End If End If

End Function

FAIR PRICE SHOP

Function reduced(r As Range)

'Gives the coverage matrix of the balance problem after deleting the rows and cols Dim i, j, rowCount, colCount As Integer rowCount = r.Rows.count colCount = r.Columns.count

Dim finalMatrix() As Integer Dim changedMatSize() As Integer changedMatSize = redurc(r)

Dim rowFinal, colFinal As Integer rowFinal = changedMatSize(1) colFinal = changedMatSize(2)



ReDim finalMatrix(1 To rowFinal, 1 To colFinal)

Dim toRemoveCols() As Integer toRemoveCols = mustselect(r)

'now to actually fill up the finalMatrix Dim row, col, tempSum As Integer

row = 1 For i = 1 To rowCount tempSum = 0 For j = 1 To colCount tempSum = tempSum + CInt(r.Cells(i, j).Value) Next j If tempSum <> 1 Then 'For the rows which are not to be ignored col = 1 For j = 1 To colCount If (toRemoveCols(j) <> 1) Then 'skip cols finalMatrix(row, col) = CInt(r.Cells(i, j).Value)

col = col + 1 End If Next j row = row + 1 'As this is a row not to be ignored End If Next i

reduced = finalMatrix

End Function

Function redurc(r As Range)

Dim i, j, rowCount, colCount As Integer rowCount = r.Rows.count colCount = r.Columns.count

Dim colsToRemove() As Integer colsToRemove = mustselect(r)

Dim tempSum, numRowToRemove, numColToRemove As Integer numRowToRemove = 0 For i = 1 To rowCount tempSum = 0 For j = 1 To colCount tempSum = tempSum + CInt(r.Cells(i, j).Value) Next j If tempSum = 1 Then numRowToRemove = numRowToRemove + 1 End If Next i

numColToRemove = 0 For Each i In colsToRemove If (i = 1) Then numColToRemove = numColToRemove + 1 End If Next i

Dim newRowCol(1 To 2) As Integer

newRowCol(1) = rowCount - numRowToRemove



newRowCol(2) = colCount - numColToRemove redurc = newRowCol

End Function

Function mustselect(r As Range)

Dim rowCount, colCount As Integer rowCount = r.Rows.count colCount = r.Columns.count

Dim i, j, tempSum As Integer Dim toSelect() As Integer ReDim toSelect(1 To colCount) For i = 1 To colCount toSelect(i) = 0 Next i

For i = 1 To rowCount tempSum = 0 For j = 1 To colCount tempSum = tempSum + CInt(r.Cells(i, j).Value) Next j If (tempSum = 1) Then For j = 1 To colCount If (CInt(r.Cells(i, j).Value) = 1) Then toSelect(j) = 1 End If Next j End If

Next i mustselect = toSelect

End Function

CHECK DIGIT VALIDATION

Function valid(strg As String) As String '-------------------------------------------------------- ' To validate a ten-digit string using check digit method 'Input argument is a ten digit string 'Output is "OK" or "NotOK" 'Modulo 11 method for check digit ' Multipliers are 1,2,...,9 ' for the digits from 9th position,..., 1st position resply. ' 1st psoition is the left most position. 'The follwing string related functions are used: 'Mid(s,p,n): Returns from string s the substring ' starting from position p and of length n 'Right (s,n):Returns from string s the substring ' at the right end of length n

'Val(s): Converts the string s composed only of digits ' into its corresponding number '--------------------------------------------------------



Dim sum As Integer, j As Integer, remainder As Integer

valid = "OK"

sum = 0

For j = 1 To 9 sum = sum + Val(Mid(strg, Len(strg) - j, 1)) * j '......Debug.Print sum Next j remainder = sum Mod 11

If Right(strg, 1) <> "X" Then If remainder <> Val(Right(strg, 1)) Then valid = "NotOK" End If Else If 10 <> remainder Then

valid = "NotOK" End If End If

End Function Function validate(numcode As Integer) As String

' Validation of a numeric code with a maximum ' of nine digits, the last of which is a chekdigit. ' Modulo 9 method is used for chek digit computation ' The multipliers are: 1,2,3,...,9

Dim strg As String, chek As String

Dim sum As Integer, j As Integer, remainder As Integer Dim f As Integer

' To convert numeric code to a string using Str function. ' Trim fuction removes any unncessary leading spaces ' from a string.

strg = Trim(Str(numcode)) validate = "OK" sum = 0

' f gives the number of digits in strg 'after removing the chek digit. f = 8 If Len(strg) < 9 Then f = Len(strg) - 1 End If

For j = 1 To f 'The function Val(string) 'converts string of digits into its number sum = sum + Val(Mid(strg, Len(strg) - j, 1)) * j Next j

' Mod operator gives remainder after dividing the first number with the

second

remainder = sum Mod 9



If remainder <> Val(Right(strg, 1)) Then validate = "NotOK" End If

End Function

Sub validsub() '-------------------------------------------------------- ' To validate a ten-digit string using check digit method ' sub procedure version 'Input is a ten digit string available in range named strg 'Output is "OK" or "NotOK" 'Modulo 11 method for check digit 'Multipliers are in range named mltpls 'The follwing string related functions are used: 'Mid(s,p,n): Returns from string s the substring ' starting from posaition p and of length n 'Right (s,n):Returns from string s the substring

' at the right end of length n 'Val(s): Converts the string s composed only digits ' into its corresponding number '--------------------------------------------------------

Dim sum As Integer, j As Integer, remainder As Integer Dim scode As String, valid As String ' Dim mcode(10) As Integer

scode = Range("strg") valid = "OK"

sum = 0

For j = 1 To 9 sum = sum + Val(Mid(scode, 10 - j, 1)) * j Next j

remainder = sum Mod 11

If remainder <> 10 Then If remainder <> Val(Right(scode, 1)) Then valid = "NotOK" End If Else If "X" <> Right(scode, 1) Then valid = "NotOK" End If End If MsgBox (valid) End Sub

SELECTION OF FAIR PRICE SHOP IN VILLAGE

Option Base 1

Function mustselect(am As Range) ' to find which centres must be selected ' am is the accessibilty matrix



'ICOV(I) = J if centre J is the only centre accessible to it ' = 0 otherwise 'MS(J) = 1 if centre J must be selected ' = 0 otherwise 'MS is transferred to the function name at the end 'NV ... No of villages 'NC... Number of centres

Dim i As Integer, j As Integer, c As Integer, NV As Integer, NC As Integ

er Dim sum As Integer

Dim ICOV() Dim MS() NV = am.Rows.Count NC = am.Columns.Count

ReDim ICOV(1 To NV) ' taken as a row vector ReDim MS(1 To NC) ' taken as a row vector

' Fill ICOV( 1 to NV) For i = 1 To NV ICOV(i) = 0 c = 0 sum = 0 For j = 1 To NC sum = sum + am.Cells(i, j).Value If am.Cells(i, j).Value > 0 Then c = j End If

Next j

If sum = 1 Then

ICOV(i) = c End If Next i ' Fill MS ( 1 to NC) For j = 1 To NC MS(j) = 0

For i = 1 To NV If ICOV(i) = j Then MS(j) = 1 Exit For End If Next i

Next j

mustselect = MS

End Function Function redurc(am1 As Range) ' given an accessibility matrix am ' this function calls mustselect and outputs

' no of rows and columns in the reduced problem 'NV1 .... No. of villages 'NC1..... No. of centres



'rc(1)... No. of rows in reduced problem 'rc(2)... No. of columns in the reduced problem 'MS1(i)... Value of mustselect of ith centre Dim i As Integer, j As Integer, nv1 As Integer, nc1 As Integer Dim rc(1 To 2) As Integer Dim MS1() nv1 = am1.Rows.Count

nc1 = am1.Columns.Count ReDim MS1(1 To nc1) MS1 = mustselect(am1) 'find number of new columns in reduced problem rc(2) = 0 For j = 1 To nc1 rc(2) = rc(2) + MS1(j) Next j rc(2) = nc1 - rc(2) 'find no. of rowsin reduced problem rc(1) = 0 For i = 1 To nv1

For j = 1 To nc1 If MS1(j) = 1 And am1.Cells(i, j).Value = 1 Then rc(1) = rc(1) + 1 Exit For End If Next j

Next i rc(1) = nv1 - rc(1) redurc = rc

End Function

Function reducedrange(am2 As Range) ' This is an array function occupying ' no of rows = no of rows of reduced mat +1 ' no of cols = no of cols of red mat + 1 ' to display the reduced accessibility matrix ' v(i) .. village number in the ith row of reduced matrix ' c(j) centre number in the jth column of the reduced matrix ' first row of output is centre numbers ' first column of output is village nos 'corner cell of output is blank ' Thus output size is 1= no of rows in red mat, 1+ No of cols in red mat ' output is namedas array lines() 'xyrc(1) No of rows in reduced matrix ''xyrc(2) No of cols in red matrix ' MS3 is the array into which mustselect indices are copied 'Nvil no of villages in mm2 'ncntr is no of centres in am2 Dim i As Integer, j As Integer, sum As Integer, k As Integer, flag As Integer Dim nvil As Integer, ncntr As Integer Dim ms3() Dim xyrc() As Integer

Dim v(), c(), lines()

nvil = am2.Rows.Count ncntr = am2.Columns.Count ReDim ms3(1 To ncntr)



ms3 = mustselect(am2) 'xyrc(1) = Selection.Rows.Count - 1 'xyrc(2) = Selection.Columns.Count - 1 ReDim xyrc(1 To 2) xyrc = redurc(am2) ReDim v(1 To xyrc(1)) ReDim c(1 To xyrc(2))

ReDim lines(1 To xyrc(1) + 1, 1 To xyrc(2) + 1)

' fill the vector c k = 0 For j = 1 To ncntr If ms3(j) = 0 Then k = k + 1 c(k) = j lines(1, k + 1) = j End If

Next j

' fill vector v k = 0 For i = 1 To nvil flag = 0 For j = 1 To ncntr If ms3(j) = 1 And am2.Cells(i, j).Value = 1 Then flag = flag + 1 End If Next j If flag = 0 Then k = k + 1 v(k) = i lines(k + 1, 1) = i

End If Next i lines(1, 1) = ""

For i = 1 To xyrc(1) For j = 1 To xyrc(2) lines(i + 1, j + 1) = am2.Cells(v(i), c(j)).Value Next j Next i

reducedrange = lines

End Function

SELECTION OF VILLAGE FOR FAIR PRICE SHOP

Option Explicit

Sub ExtSolv() ' ' ExtSolv Macro



' Macro recorded 11/08/2007 by Prof. V V Rao ' ' Keyboard Shortcut: Ctrl+Shift+E ' Dim i As Integer, n As Integer Dim st, ft, durn n = Range("slvrange").Rows.Count

SolverReset

SolverOptions Precision:=0.01

SolverOptions IntTolerance:=8 SolverOptions AssumeNonNeg:=True SolverOptions AssumeLinear:=True

SolverOk SetCell:="$U$18", MaxMinVal:=2, ValueOf:="0", ByChange:="$E$18:$S$18" SolverAdd cellref:=Range("T21:t70"), relation:=3, Formulatext:=Range("U21:U70")

SolverAdd cellref:=Range("Varse"), relation:=5 For i = 1 To n

st = Timer Range("varse") = 1 Range("slevel").Value = Range("slvrange").Cells(i, 1).Value Range("slvrange").Cells(i, 2) = "*****" If Range("w71").Value = "F" Then

SolverSolve UserFinish:=True SolverFinish KeepFinal:=1 Range("slvrange").Cells(i, 2).Value = Range("objfne").Value

End If ft = Timer durn = ft - st Range("slvrange").Cells(i, 3) = Range("w71").Value Range("slvrange").Cells(i, 4) = durn Next i End Sub Sub enumerate() '------------------------------ 'exhaustive enumeration 'nc... No, of centres 'AM(i,j) value of the i,j th element of accessibility matrix 'LHS(j) Jth value of village covered or not 'S(i) Is ith centre selected (1) or not(0) ' F tells whether the selection vector provides feasible soln (1) or not (0) ' SMin(i) value of ith element of min cost selection vector 'Cost (i) cost of ith cost centre, CMin min cost, C is current cost 'n the value of the decimal value of the solution in question ' k is the row number in data table (output line number) 'n1 intermediate variable '-------------------------------- Dim stime, ftime, durn Dim st1, sf1, sd1 Dim n As Long, n1 As Long, m As Long, nc As Integer

Dim AM(1 To 50, 1 To 15) As Integer Dim F As Integer Dim S(1 To 15) As Integer, SMin(1 To 15) As Integer



Dim Cost(1 To 15) As Integer Dim CMin As Integer, C As Integer, sum As Integer Dim i As Integer, j As Integer, k As Integer 'Initialization of Accessibilty array Dim AMrng As Range, cstrng As Range, Orng As Range Set AMrng = Range("e21:s70") Set cstrng = Range("e17:s17")

Set Orng = Range("AE21") 'Iterate over each output line For k = 1 To 10 Range("slevel").Value = Orng(k, 1)

'------- Initialization

For i = 1 To 50

For j = 1 To 15 AM(i, j) = AMrng(i, j) 'initialize AM array Next j

Next i For j = 1 To 15 Cost(j) = cstrng(1, j) ' initialize cost vector Next j nc = 15 CMin = 29999 For i = 1 To 15 S(i) = 1 SMin(i) = 1 Next i F = 0 sum = 0

'Iterate over each possible selection vector stime = Timer st1 = Now For m = 1 To (2 ^ nc) - 1 n = m '--- fill s vector through bit conversion of n ' For i = 1 To nc ' first make all bits zero ' S(i) = 0 ' Next i i = nc Do While n <> 0 ' fill from right to left S(i) = n Mod 2 n = Int(n / 2) i = i - 1 Loop

Do While i <> 0 'rest of the bits are zero S(i) = 0 i = i - 1 Loop '------------------------- Check feasibility sum = 0 For i = 1 To 50 For j = 1 To 15 If (AM(i, j) * S(j) = 1) Then

sum = sum + 1 Exit For End If



Next j If sum < i Then ' the ith village is not serviced Exit For End If Next i

If sum = 50 Then 'feasible

'---- Find C, the cost of S vector C = 0 For i = 1 To 15 C = C + Cost(i) * S(i) Next i '---------------- update cmin and smin() if necessary If C < CMin Then CMin = C For i = 1 To nc SMin(i) = S(i) Next i End If

End If Next m ftime = Timer sf1 = Now durn = ftime - stime sd1 = sf1 - st1 '---------------- write output If CMin < 29999 Then ' write output if solution is feasible Orng(k, 2) = CMin For i = 1 To 15 Orng(k, i + 2) = SMin(i) Next i Orng(k, 18) = durn

Orng(k, 19) = sd1 * 24 * 60 * 60 End If Next k End Sub

MULTIPLEX MOVIES

Option Explicit Option Base 1

Sub findmovies() ' Multiplex Movies ' To list the feasible shows ' starting from the Outrng range

'inputrng ... starting timings of shows in 24 hr format, ' include title row 'outrng.. ...range in which output needs to be written 'ws......... starting time of desirable time window 'we......... ending time of desirable time window 'solnno..... index of the solution,

' same as the row of the output range

Dim irng As Range, orng As Range



Set irng = Range("inputrng") Set orng = Range("outrng") Dim solnno As Integer, j As Integer, i As Integer Dim sttime As Single Dim movie As String Dim multplx As String Dim Language As String

Dim tkts As Single Dim tktg As Single Dim mintkts As Single Dim mintktg As Single Dim minmultplx As String, minsttime As Single, minlang As String mintktg = 999999 mintkts = 999999

'clear the output range j = 1

Do While (orng(j, 1) <> "") 'all six values of j th output row are made blank

For i = 1 To 6 orng(j, i) = "" Next i j = j + 1 Loop

' To scan the input range ' for feasible solutions

solnno = 0

For i = 2 To irng.Rows.Count

multplx = irng(i, 1) movie = irng(i, 2) Language = irng(i, 3) If Language <> "E" Then Language = "H" End If tkts = WorksheetFunction.VLookup(multplx, Range("halltab"), 3, False)* Range("F1").Value

tktg = WorksheetFunction.VLookup(multplx, Range("halltab"), 4, False)* Range("F1").Value If Language = "E" Then tkts = tkts * (1 - WorksheetFunction.VLookup(multplx, Range("halltab"), 5, False) / 100) tktg = tktg * (1 - WorksheetFunction.VLookup(multplx, Range("halltab"), 5, False) / 100) End If For j = 4 To 7 sttime = irng(i, j)

If (sttime >= Range("ws").Value And sttime <= Range("we").Value) Then solnno = solnno + 1 'new output line orng(solnno, 1) = irng(i, 1)

orng(solnno, 2) = irng(i, 2) orng(solnno, 3) = sttime orng(solnno, 4) = Language



orng(solnno, 5) = tkts orng(solnno, 6) = tktg

If tkts < mintkts Then mintkts = tkts End If If tktg < mintktg Then

mintktg = tktg End If End If

Next j Next i ' Summary output

If solnno > 0 Then solnno = solnno + 1

orng(solnno, 1) = "No. of Feasible Solutions"

orng(solnno, 2) = solnno - 1 orng(solnno + 1, 1) = "MinSilverChrg" orng(solnno + 1, 2) = mintkts orng(solnno + 2, 1) = "MinGoldChrg" orng(solnno + 2, 2) = mintktg

End If

End Sub Function listshows(irng As Range, ptab As Range, prmtr As Range) '----------------------------------------------------

'An array function to be given in an array of size: 'rows in data list (w/o headings)*4, six columns ' Each output row gives the following details on a feasible show: ' Multiplex code, movie name, starting time, Language ' t1 , t2... begin and end times of desired time window ' inputrng... range name of data base including field titles ' otpt() is output array '---------------------------------------------------

Dim r As Integer, c As Integer, i As Integer, j As Integer Dim tkts As Integer, tktg As Integer Dim solnno As Integer Dim otpt()

r = 4 * (irng.Rows.Count - 1) c = irng.Columns.Count ReDim otpt(1 To 4 * (r - 1), 1 To 6)

'Initialize otpt to null values For i = 1 To r For j = 1 To 6 otpt(i, j) = "" Next j Next i

' Check each of the four shows in each row to verify their feasibilty

' and if feasible write six output values in the corresponding row of OT



PT

solnno = 0 For i = 2 To irng.Rows.Count For j = 4 To 7 If irng(i, j) >= prmtr(1, 1) And irng(i, j) <= prmtr(1, 2) Then

solnno = solnno + 1 otpt(solnno, 1) = irng(i, 1) otpt(solnno, 2) = irng(i, 2) otpt(solnno, 3) = irng(i, j) otpt(solnno, 4) = irng(i, 3) tkts = WorksheetFunction.VLookup(irng(i, 1), ptab, 3, False) * prmtr(1, 3) tktg = WorksheetFunction.VLookup(irng(i, 1), ptab, 4, False) * prmtr(1, 3) If irng(i, 3) = "E" Then tkts = tkts * (1 - WorksheetFunction.VLookup(irng(i, 1), ptab, 5, False) / 100)

tktg = tktg * (1 - WorksheetFunction.VLookup(irng(i, 1), ptab, 5, False) / 100) End If otpt(solnno, 5) = tkts otpt(solnno, 6) = tktg End If

Next j Next i listshows = otpt

End Function Sub findmovies1()

' Multiplex Movies ' To list the feasible shows starting from the active cell

'inputng ... starting timings of shows in 24 hr format, ' include title row 'activecell.. the location of the highlight when the button is pressed 'ws......... starting time of desirable time window 'we......... ending time of desirable time window 'solnno..... index of the solution, ' same as the row of the output range Dim solnno As Integer, j As Integer, i As Integer Dim sttime As Single 'clear the output range j = 1

Do While (ActiveCell(j, 1) <> "")

For i = 1 To 4 ActiveCell(j, i) = "" Next i j = j + 1

Loop

'Put the four column headings for output

ActiveCell(1, 1) = "Mltplex"

ActiveCell(1, 2) = "Movie"



ActiveCell(1, 3) = "Sttime" ActiveCell(1, 4) = "ShowNo."

' To scan the input range ' for feasible solutions

solnno = 1

For i = 2 To Range("inputrng").Rows.Count

For j = 4 To 7 sttime = Range("inputrng").Cells(i, j).Value If (sttime >= Range("ws").Value And sttime <= Range("we").Value) Then

ActiveCell(solnno + 1, 1) = Range("inputrng").Cells(i, 1).Value ActiveCell(solnno + 1, 2) = Range("inputrng").Cells(i, 2).Value ActiveCell(solnno + 1, 3) = sttime ActiveCell(solnno + 1, 4) = j - 3

solnno = solnno + 1 End If

Next j Next i End Sub

MULTIPLEX MOVIES ( SHORT CUT)

Sub ExtractMovies()

' ' ExtractMovies Macro ' Macro recorded 7/13/2005 by Prof. V V Rao ' ' Keyboard Shortcut: Ctrl+Shift+M ' ActiveWindow.SmallScroll ToRight:=-5 Range("B3:H34").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "M9:M10"), CopyToRange:=Range("P4:R4"), Unique:=False ActiveWindow.SmallScroll ToRight:=0 End Sub Sub ActCell() ActiveCell(1, 1) = 1 ActiveCell(2, 2) = 2 ActiveCell(3, 3) = 3 End Sub Function GVlookup(LV As Variant, LR As Range, LCNO As Integer, RCNo As Integer, Intrvl As Boolean) '.... Like VLOOKUP with the difference that the lookup column can be any column in the range '.... LV Lookup Value '.... LR Lookup Range '.... LCNO Lookup Column Number within LR '.... RCNO Result Column Number within LR

'.... ITRVL Is interval lookup needed (TRUE), otherwise FALSE

Dim MR As Integer, mtype As Integer



mtype = -1 * Intrvl MR = WorksheetFunction.Match(LV, WorksheetFunction.Index(LR, 0, LCNO), mtype) GVlookup = WorksheetFunction.Index(LR, MR, RCNo) End Function

MINIMUM Search

Function Msearch(X As Integer, M As Range)

Dim result(1 To 1, 1 To 2) As Integer 'Dim result(1 To 2) As Integer Dim i As Integer, j As Integer, NR As Integer, NC As Integer

result(1, 1) = 0 result(1, 2) = 0 'result(1) = 0 'result(2) = 0

NR = M.Rows.Count NC = M.Columns.Count

For i = 1 To NR For j = 1 To NC If M(i, j) = X Then result(1, 1) = i result(1, 2) = j

'result(1) = i 'result(2) = j Exit For End If Next j

If result(1, 1) > 0 Then 'If result(1) > 0 Then Exit For End If Next i Msearch = result 'Msearch = WorksheetFunction.Transpose(result)

End Function

Function minnmin(given As Range)

Dim r As Integer, c As Integer, i As Integer, j As Integer Dim result(1 To 2) As Integer

r = given.Rows.Count c = given.Columns.Count

result(1) = given(1, 1)

'results(1) = given.cells(1,1).value

For i = 1 To r



For j = 1 To c If given(i, j) < result(1) Then result(1) = given(i, j) End If Next j Next i

result(2) = 0

For i = 1 To r For j = 1 To c If given(i, j) = result(1) Then result(2) = result(2) + 1 End If Next j Next i

minnmin = result

End Function Function minnmin3(given As Range)

'Single pass solution



result(1) = given(1, 1) result(2) = 0

For i = 1 To r

For j = 1 To c If given(i, j) < result(1) Then result(1) = given(i, j) result(2) = 1 Else If given(i, j) = result(1) Then result(2) = result(2) + 1 End If End If Next j Next i

minnmin3 = result

End Function

Function minnmin2(given As Range) 'Solution using "For each" Dim r As Integer, c As Integer, i As Integer, j As Integer Dim result(1 To 2) As Integer




result(1) = given.Cells(1, 1).Value

For Each Cell In given If Cell.Value < result(1) Then result(1) = Cell.Value End If Next Cell

result(2) = 0

For Each Cell In given

If Cell.Value = result(1) Then result(2) = result(2) + 1 End If Next Cell

minnmin2 = result

End Function

Sub minnmin_sub()


r = Selection.Rows.Count c = Selection.Columns.Count

result(1) = Selection.Cells(1, 1).Value

For Each Cell In Selection If Cell.Value < result(1) Then

result(1) = Cell.Value End If Next Cell

result(2) = 0

For Each Cell In Selection If Cell.Value = result(1) Then result(2) = result(2) + 1 End If Next Cell

Selection(r + 1, 1).Value = result(1) Selection(r + 1, 2).Value = result(2)

End Sub

TRAVELLLER HEURISTIC PROBLEM

Sub tsp() '--------------------------------------------------------------------- '... To solve travelling salesperson Problem



'... using the cheapest neighbor heuristic ' '... The distance matrix is given in the range named tsprange ' '... n... ...........Number of cities '... costmat(i,j)... cost of going to city-j from city-i '... mincol(j)...... index of minimum cost column in row j

'... minval(j)...... minimum value of cost in row j '... tcost.......... total cost of a solution '... row ........... index of the row currently being examined '... i, j ,k ....... control variables in FOR NEXT Loops '... 9999 .......... used for infinity '-------------------------------------------------------------------- Dim mincol() As Integer, minval() As Single, n As Integer Dim costmat() As Single Dim tspmat As Range

Set tspmat = Range("tsprange")

n = tspmat.Rows.Count

ReDim mincol(1 To n), minval(1 To n) ReDim costmat(1 To n, 1 To n) Dim tcost As Single

Dim i As Integer, j As Integer, row As Integer

'initialize tspmat.Font.Color = RGB(0, 0, 0)

For i = 1 To n

mincol(i) = 0 minval(i) = 9999 costmat(i, 1) = 9999 For j = 2 To n costmat(i, j) = tspmat.Cells(i, j).Value Next j Next i

tcost = 0 '... Start with city-1 as the current city '... Find in the row of the current city, min val, and mincol '....to decide the next city to be visited '... update cost matrix to ensure that the next city is not visited from anywhere else '... update tcost '... next city becomes current city

row = 1 For i = 1 To n - 1

For j = 2 To n If costmat(row, j) < minval(row) Then minval(row) = costmat(row, j) mincol(row) = j End If

Next j

tcost = tcost + minval(row) 'update total cost



'update mincol(row) column

For k = 1 To n costmat(k, mincol(row)) = 9999 Next k tspmat.Cells(n + 1, i).Value = mincol(row) 'display the next city to be visted

tspmat.Cells(row, mincol(row)).Font.Bold = True 'bold face the cell in input range tspmat.Cells(row, mincol(row)).Font.Color = RGB(255, 0, 0) row = mincol(row)

Next i

tcost = tcost + tspmat.Cells(row, 1).Value

tspmat.Cells(n + 1, 0).Value = 1 tspmat.Cells(n + 1, n).Value = 1 tspmat.Cells(row, 1).Font.Bold = True

tspmat.Cells(row, 1).Font.Color = RGB(255, 0, 0) tspmat.Cells(n + 2, 1) = tcost

End Sub Function TSPF(tspmat As Range) 'An array function occupying an N+2 cells row range '--------------------------------------------------------------------- '... To solve travelling salesperson Problem '... using the cheapest neighbor heuristic ' '... The distance matrix is given in the range named tsprange '

'... n... ...........Number of cities '... costmat(i,j)... cost of going to city-j from city-i--input range '... mincol(j)...... index of minimum cost column in row j '... minval(j)...... minimum value of cost in row j '... tcost.......... total cost of a solution '... row ........... index of the row currently being examined '... i, j ,k ....... control variables in FOR NEXT Loops '... 9999 .......... used for infinity '-------------------------------------------------------------------- Dim mincol() As Integer, minval() As Single, n As Integer Dim costmat() As Single Dim Result() As Integer If tspmat.Rows.Count <> tspmat.Columns.Count Then ' error message TSPF = CVErr(xlErrNA) Exit Function End If n = tspmat.Rows.Count

ReDim Result(1 To n + 2) ' Result has first N=1 elements to contain route; last to contain tcost ReDim mincol(1 To n), minval(1 To n) ReDim costmat(1 To n, 1 To n) Dim tcost As Single

Dim i As Integer, j As Integer, row As Integer

'initialize



For i = 1 To n

mincol(i) = 0 minval(i) = 9999 costmat(i, 1) = 9999 For j = 2 To n costmat(i, j) = tspmat(i, j)

Next j Next i

tcost = 0 '... Start with city-1 as the current city '... Find in the row of the current city, min val, and mincol '....to decide the next city to be visited '... update cost matrix to ensure that the next city is not visited from anywhere else '... update tcost '... next city becomes current city

row = 1 For i = 1 To n - 1

For j = 2 To n If costmat(row, j) < minval(row) Then minval(row) = costmat(row, j) mincol(row) = j End If Next j

tcost = tcost + minval(row) 'update total cost

'update mincol(row) column

For k = 1 To n costmat(k, mincol(row)) = 9999 Next k Result(i + 1) = mincol(row) 'Fill the i+1 th element of Result with the next city to be visted 'tspmat.Cells(row, mincol(row)).Font.Bold = True bold face the cell in input range row = mincol(row)

Next i

tcost = tcost + tspmat(row, 1)

Result(1) = 1 Result(n + 1) = 1 'tspmat.Cells(row, 1).Font.Bold = True Result(n + 2) = tcost

TSPF = Result

End Function

VENDER Selection



Option Explicit Option Base 1 Function Composite(Wts As Range, VScore As Range, PScore As Range) Dim r As Integer, c As Integer, i As Integer, j As Integer Dim w1 As Single, w2 As Single, w3 As Single

' This is an array function

' To compute the composite scores in vendor selection problem Dim Comp() As Single r = PScore.Rows.Count c = PScore.Columns.Count ReDim Comp(1 To r, 1 To c) w1 = Wts(1) w2 = Wts(2) w3 = 1 - w1 - w2 For i = 1 To r For j = 1 To c Comp(i, j) = w1 * VScore(i, 1) + w2 * VScore(i, 2) + w3 * PScore(i, j)

Next j Next i Composite = Comp

End Function Function ColMax(Comp As Range) ' array function to compute ' the index of max rated vendor in each column Dim c As Integer, r As Integer, i As Integer, j As Integer Dim max As Single, maxrow As Integer Dim OTRow() As Integer c = Comp.Columns.Count r = Comp.Rows.Count

ReDim OTRow(1 To c) For j = 1 To c max = -9999 maxrow = 0 For i = 1 To r If Comp(i, j) > max Then max = Comp(i, j) maxrow = i End If Next i OTRow(j) = maxrow Next j ColMax = OTRow End Function

mc vba all answer

Documents