Skifte Noen formler med formelresultatene

February 18  by Eliza

Brian har et behov for å behandle et regneark før det kan bli delt ut til andre mennesker. Det han trenger er å eliminere de fleste, men ikke alle, av formlene i regnearket. Han ønsker å gå gjennom alle cellene i et valgt område av celler, og hvis cellen inneholder en formel, sjekk at formelen. Hvis formelen inneholder en referanse (noen referanse) til et annet regneark i den gjeldende arbeidsboken, så formelen blir ignorert. Hvis formelen ikke inneholder en slik referanse, og deretter makro behov for å erstatte formelen med resultatet av formelen.

Dette er en relativt grei oppgave; alt du trenger å gjøre er å ha makroen skritt grundig cellene og (1) finne ut om cellen inneholder en formel. Hvis den gjør det, så sjekk for å se om formelen inneholder et utropstegn. Utropstegn er benyttet i formel referanser, for eksempel følgende:

= A1 Ark2!

Så, hvis formelen inneholder et utropstegn, kan du ignorere det. Hvis den ikke inneholder et utropstegn så kan du erstatte den med sin verdi.

Sub ConvertFormulas1 ()
Dim c Som Variant
Dim frm As String

On Error Resume Next

For hver c I Selection
Hvis c.HasFormula Deretter
frm = c.Formula
Hvis InStr (1, frm, "!") = 0 Then
c.Value = c.Value
End If
End If
Neste c
End Sub

Det er ett problem med dette: utropstegnet vil vises i alle formler eksterne til gjeldende regneark, inkludert de som er i andre arbeidsbøker. Hvis du virkelig ønsker å bare erstatte formler til andre regneark i den gjeldende arbeidsboken, men ignorere formler som refererer ark på andre arbeidsbøker, så må du legge til noen ekstra logikk. Logikken gjør seg tydelig når du ser på hvordan Excel refererer de andre arbeidsbøker:

= [OtherWorksheet.xls] Ark1 '! $ C $ 9

Legg merke til at navnet på den andre arbeidsboken inneholdt i parentes. Dermed etter testing for utropstegn (som informerer deg om at referansen er til et annet regneark, må du sjekke om det finnes en venstre brakett. Hvis det er det, så referansen er ikke til en celle i gjeldende arbeidsbok.

Sub ConvertFormulas2 ()
Dim c Som Variant
Dim OtherSheet Som boolsk
Dim frm As String

On Error Resume Next

For hver c I Selection
Hvis c.HasFormula Deretter
frm = c.Formula
OtherSheet = False
Hvis InStr (1, frm, "!") Deretter
OtherSheet = True
Hvis InStr (1, frm, "[") Deretter
OtherSheet = False
End If
End If
Hvis ikke OtherSheet Deretter
c.Value = c.Value
End If
End If
Neste c
End Sub

Det skal påpekes at det ville være forholdsvis lett å modifisere fremgangsmåten anvendt i denne makro slik at det ble kvitt alle eksterne referanser samtidig la referanser til den aktuelle arket intakt. Faktisk, alt du trenger å gjøre er å kvitte seg med sjekking for braketten og deretter kvitte seg med "Ikke" søkeord i strukturen som sjekker OtherSheet variabel.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (6959) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: Skifte Noen formler med formelresultatene.